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
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
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,
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
RE: Excel Formula Help
Skip,
Just traded in my OLD subtlety...
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
RE: Excel Formula Help
Yes, my bad with the table criteria.
Woohooo...Thanks for the formula.
Impressive as ever
Thanks,
Arv
RE: Excel Formula Help
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
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,
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
RE: Excel Formula Help
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
Sure, i will post in new thread.
Thanks,
arv
RE: Excel Formula Help
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
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
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
Noted, let me give it a shot.
Thanks for your help
arv
RE: Excel Formula Help
combo
RE: Excel Formula Help