Nested IF Statements to display value

[raihaan9]

I'm trying to display a value in an excel cell based on what the user selects from 3 drop-down boxes.

The determination of what should be displayed is based on "BIA Decision Matrix" scale located at the bottom of the worksheet.

So for example if the user selects "1,000,000 per day" from cell I5, "<=1 hour" or "2-24 hours" from cell I6 AND "0% of care", "25% of care, "50% of care" or "75% of care" from cell I7, the value to display in cell I27 should be "Critical functions - mission critical, direct patient care. BIA rating: 0" - (blue) and so on as per the matrix attached.

What would be even better is based on the 3 inputs from the dropdown if the Relative Critically Rating table could be highlighted to show the cell where the input falls within.

I'm pretty sure this is a Nested IF statement with AND conditions but I'm struggling to determine how to write this.

Any ideas would be much appreciated.

Thanks, Rai

[dilipandey]

Hi Rai,

If the user selects "1,000,000 per day" from cell I5, "<=1 hour" from cell I6 AND "25% of care" from cell I7, the value to display in cell I27 should be 2.. right?

And do you always want the output in cell I27 ?

Regards,
DILIPandey

<click on below 'star' if this helps>

[raihaan9]

Hi Dili,

The value should be "2" or "Box 1 is for Critical functions - mission critical, direct patient care. BIA Rating: 0" (as per the blue key).

Either output to cell I27 or even better would be to highlight the cell in the BIA Decision Matrix table in RED, so in this case would highlight cell H17.

Thanks, Rai

[raihaan9]

Hi chaps,

Any ideas out there?

[dilipandey]

Hi Rai,

I would suggest you to create a summary table (may be refine Reference data) which has the same data as compared to filter drop down values and then we can think on to use Index Match type of function to pick up the correct value for example:-
1,000,000 per day ---- <=1 hour.........25% of care = 2
500 per day ---------- >25<48 hours .. 50% of care = 7

Regards,
DILIPandey

<click on below 'star' if this helps>

If the user selects "1,000,000 per day" from cell I5, "<=1 hour" from cell I6 AND "25% of care" from cell I7, the value to display in cell I27 should be 2.

[raihaan9]

Thanks Dilipandey,

Any chance you could demonstrate to me by showing me by creating an example in the attachment?
I've some research into the function but could do with looking at a working example as a starting point.

Thanks, Rai

[dilipandey]

Ya sure...

Happy to demonstrate but for that I need the following data with all the possibilities. Suggest you to create following 4 columns and attach back. Thanks.

1,000,000 per day ---- <=1 hour.........25% of care = 2
500 per day ---------- >25<48 hours .. 50% of care = 7

Regards,
DILIPandey

<click on below star if this helps>

[raihaan9]

Hi Dillipandey,

Thanks for your continued help.
What I have done is I have inserted columns showing the different permutations/conditions and the expected outcomes.
So for example if "$1,000,000 per day" is chosen,

AND EITHER

"<= 1 hour" or "2-24 Hours" is chosen,

AND EITHER

"75% of care" or "50% of care" or "25% of care" or "0% of care" is chosen,

The result should be "Mission critical, direct patient care. BIA Rating: 0".

Let me know if you need anymore information.

Thanks, Rai

[dilipandey]

Hi Raihaan,

I believe you are not getting me clearly, see the attachment and fill out the ? in it and provide the remaining criteria below that in similar manner . Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[raihaan9]

Hi Dilipandey,

I have reattached but actually these are a unique set of conditions. So I can't populate the "?"s as the 3 conditions (column A, B and C) need to be chosen in order for one of the colours to be selected. So for Dark Blue, if $1,000,000 is chosen and either <=1hour or 2-24 hours is chosen and either 75%, 50%, 25% or 0% is chosen then Dark Blue is correct.

Let me know if you need anymore information.

Thanks, Rai

[dilipandey]

Hi Raihaan,

Please look into the blue area in the attached file to understand what I meant to say. I believe this have covered all of the combinations for blue area and in the similar fashion I need criteria for others as well. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>