×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Excel Formula Help
2

Excel Formula Help

Excel Formula Help

(OP)
Seeking help around Excel formula.
I have 3 combinations - A,B & C that are number ranges.
I have broken them down into Lower and Upper.
Numbers that fall within Range A, Range B and Range C will give the expected Outcome.
Attached is an example workbook with subset of criteria.
Thanks,
Arv

RE: Excel Formula Help

Hi,

I've given you an approach that could be part or all of a solution: SUMPRODUCT.

But you may have some issues with your criteria table like...
1) 2 Outcome 16 rows
2) the criteria in Outcome 16 & the-would-be-Outcome 17 overlap!

Also your 4-line example has no valid Outcome 2

So, at this juncture, TILT!!!

Back to the drawing board.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Excel Formula Help

(OP)
Hi Skip
Yes, my bad with the table criteria.
Woohooo...Thanks for the formula.
Impressive as ever2thumbsup
Thanks,
Arv

RE: Excel Formula Help

It's ok, 2+3-1=4. Skip's formula works with either single or no match row entry.

IMHO such excel formulas, at least applied to the whole process, are hard to manage, esp. when you change assumptions. Moreover, you can't get more that single output easily. It is beter, from the point of robustness, to have some helper columns to process the data. Personally I would consider (if using excel) switching to power query environment (get & transform excel data section, built in excel since 2016 version). It is more natural and can give multiple match output, having both tables as input. In practice it is one of new BI applications implemented in excel, a powerful tool for data transforming, definitely worth to learn it.

combo

RE: Excel Formula Help

In the example you uploaded, you have ambiguous criteria. Which is the reason for your question.

Since this is a different question than the original, you ought to
1) delete the last 2 posts and
2) start a new thread.

If you want to allow multiple results, then a solution like combo suggested is possible, but then a simple "example" table with one slot is inadequate for reporting results. I would suggest a slightly different solution using MS Query. But that all should be in a new thread. Different question.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Excel Formula Help

(OP)
Hi combo
Are you able to provide example of the solution in power query so that i can refer to the data query setup?
Thanks,
arv

RE: Excel Formula Help

(OP)
HI Skip
Sure, i will post in new thread.
Thanks,
arv

RE: Excel Formula Help

Continuing with the file you moved to new thread, I added a new sheet with Power Query solution. You need excel 2016 or higher.
To work with current data (Test_Book2), you need to enable connections if you are asked to.

The file contains:
1. criteria table with query to get the data in PQ environment (not necessary to work this way, but it profits in some situations, no need to name the query as source table),
2. input table with query to get the data in PQ,
3. query that processes input (queries) and criteria, with output to output table.

All you have to do is to fill input and criteria in tables and refresh output table (select any cell inside, right-click and refresh).
Don't change table names or headers unless you modify queries.

The single output query, line by its line:
1. appends criteria table to each input line,
2. expands criteria (so you have a product of tables),
3. tests A, B and C, combines tests,
4. filters final tests,
5. removes unnecessary input and helper columns.

combo

RE: Excel Formula Help

(OP)
Hi combo
PQ > great feature to learn.
It looks like if input data does not fall into the table criteria, it omits the data in the output table.
Is there anyway to include and highlight those records?

Let me play around with the file and may come back with questions.

Thanks,
arv

RE: Excel Formula Help

As I wrote, the query creates table1 x table2 product and filters matching data. This is what I planned, to handle duplicated criteria matched.

To get all inputs, you could start new query from input data, add the query I created with external join (+expanding rows), remove unnecessary columns and replace Nulls (in output when no criteria match). For duplicated output you get multiple rows, you have to decide, how to handle this: you may leave the query as connection only (no worksheet output) and use it as input for pivot table (from external data source, you can find the query in workbook connections).

combo

RE: Excel Formula Help

(OP)
Hi combo
Noted, let me give it a shot.
Thanks for your help 2thumbsup,
arv

RE: Excel Formula Help

(OP)
Thanks Combo. I shall give it a shot 2thumbsup

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close