VBA or Formula for 35 nested IF AND statements

[SierraDelta]

Hi All,

I've been struggling with this problem all day. I've searched through existing posts for nesting statements, but my scenario doesn't match with anything I've found so far.

I have 3x different drop down lists. The combination of their values form the qualifiers for the numeric result at the end of my current IF AND statement. I have the "Total Hours" values for each combination - I don't mind entering them manually.

The following statement works, but obviously only for 1 combination. I need this logic for 36 x3 combinations. If the script or formula can allow me to keep entering the Total Hours values manually that would be perfect.

=IF(AND(D6="Care Report";G6="Chaos";K6="1");"26";"26")

	A	B	C	D
1	Assessment Type (Drop Down)	Maturity Level (Drop Down)	Data Quantity (Drop Down)	Total Hours (Fixed formula Answer Cell)
2	Report 1	0	1	(see formula above)
3		1	3
4		2	6
5		3	9
6		4	12
7		5	24
8
9	Report 2	0	1
10		1	3
11		2	6
12		3	9
13		4	12
14		5	24
15
16	Report 3	0	1
17		1	3
18		2	6
19		3	9
20		4	12
21		5	24

<tbody>
</tbody>

I would be very grateful for any guidance/help.

Thanks,
SD

 

[Dr. Demento]

Welcome the the forum, SD.

I can think of two approaches:

Robert outlines a pretty slick response here (How to Create an excel dropdown list that displays text with a numeric hidden value)

A more simplistic way without forms is to use Data Validation (same as Robert's suggestion) but append to the beginning the value of the text (ie, "1 Report Type A") and then capture the left most character in each cell and add/multiply them as needed.

hth

 

[SierraDelta]

Thanks for the suggestion. Unfortunately that solution does not speak to my scenario. I will try to explain it in a different way. I think that because I don't know how to resolve it, I'm not communicating the problem very well. Let's try again.

The different values contained in each drop down list are not combined in a mathematical calculation to get to the Total Hours result. The numbers are actually just index numbers which correspond with text, so in essence I have three text drop down fields which, in different combinations, equals a certain amount of hours.

Here is a simple expression:

Where Assessment Type = Report 1; and Maturity Level = 0; and Data Quantity = 1; then Total Hours = 20.
Where Assessment Type = Report 1; and Maturity Level = 1; and Data Quantity = 1; then Total Hours = 15.
Where Assessment Type = Report 1; and Maturity Level = 2; and Data Quantity = 1; then Total Hours = 15.

Where Assessment Type = Report 1; and Maturity Level = 0; and Data Quantity = 2; then Total Hours = 21.
Where Assessment Type = Report 2; and Maturity Level = 1; and Data Quantity = 3; then Total Hours = 22.
Where Assessment Type = Report 3; and Maturity Level = 2; and Data Quantity = 4; then Total Hours = 23.

and so on.

The IF AND statement I have works. But I need all of the possible combinations' calculations (105 in total; 35 for each report number), to be active in 1 cell. The answer cell must be in-line with each drop down field. So A1 is Assessment Type, A2 is the drop down field and D2 is always the cell where the answer is returned for all combinations.

Does it make more sense now?

 

[Dr. Demento]

It does.Thanks.

I think the answer is a CASE statement. You'll probably need to do a Worksheet_Change (which I'll need to work on; not my forte, but I'll put something together tomorrow). Take a look here for some understanding how CASE works. But before that, I'd concatenate the three columns and then use the CASE. For example,

cellConcat = "Report 1|MatLevel 0|DataQty 1"  ' <-- hard-coded for this example
Select Case True
  Case "Report 1|MatLevel 0|DataQty 1"
    TotHrs = 20
  Case "Report 1|MatLevel 1|DataQty 1"
    TotHrs = 15
  Case "Report 1|MatLevel 2|DataQty 1"
    TotHrs = 15

My apologies that I don't have a ready-made solution at this moment. I can have something by tomorrow.

You could, however, code all the permutations based on your data along with the corresponding hours.

While this would get you where you want to go, it will be an awful lot of coding; I swear there's something I'm missing that would allow much less than 105 CASE statements. There isn't an algorithm that dictates how each Total Hour qty is computed based on the three variable?? If so, capitalizing on that would be the way to go.

 

[Eric W]

If there are patterns to be found in your list of permutations, then maybe we could come up with something shorter. However, if you just have to use a hardcoded list, you can try something like this:

	A	B	C	D	E	F	G	H	I	J
1	Assessment Type	Maturity Level	Data Quantity	Total Hours			Assessment Type	Maturity Level	Data Quantity	Total hours
2	Report 1	0	2	21			Report 1	0	1	20
3							Report 1	1	1	15
4							Report 1	2	1	15
5							Report 1	0	2	21
6							Report 2	1	3	22
7							Report 3	2	4	23
8

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet4​

Array Formulas Cell Formula D2 {=IFERROR(INDEX($J$2:$J$7,MATCH(A2&"|"&B2&"|"&C2,$G$2:$G$7&"|"&$H$2:$H$7&"|"&$I$2:$I$7,0)),"No Match")} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Create a table somewhere in your workbook with the option, you can put it on another sheet and hide it if you want. Then you can use the formula in D2 to get the matching value. It's an array formula, confirm it with Control+Shift+Enter. Also, I notice you use the ; for a separator in your formula, yours should look like this:

=IFERROR(INDEX($J$2:$J$7;MATCH(A2&"|"&B2&"|"&C2;$G$2:$G$7&"|"&$H$2:$H$7&"|"&$I$2:$I$7;0));"No Match")

Of course you'll need to change the ranges to match your sheet. Hope this helps.

 

[Eric W]

This formula also works in D2:

=SUMIFS($J$2:$J$7;$G$2:$G$7;A2;$H$2:$H$7;B2;$I$2:$I$7;C2)

 

[SierraDelta]

Thank you very much for this example. Logically speaking, this is the most practical solution for me. I've included a snippet of my source table below. Don't worry about the total cost cells, it's hours x rate. This is so that you can see that there is no way that the Maturity vs Quantity vs Report Type can be calculated. The total has to come from a hard coded list because the "hours" cells are dictated and not calculated.

So,

and that is why =IF(AND(D6="Care Report";G6="Chaos";K6="1");"26";"26") will work for 1 combination.

Your solution will enable me to hard code the results for each of the combination possibilities, so that when Assessment, Maturity and Quantity drop downs have been selected, the Total Hours cell will be auto-populated. Yes?

 

[Eric W]

Correct.

 

[SierraDelta]

It does.Thanks.

I think the answer is a CASE statement. You'll probably need to do a Worksheet_Change (which I'll need to work on; not my forte, but I'll put something together tomorrow). Take a look here for some understanding how CASE works. But before that, I'd concatenate the three columns and then use the CASE. For example,

cellConcat = "Report 1|MatLevel 0|DataQty 1"  ' <-- hard-coded for this example
Select Case True
  Case "Report 1|MatLevel 0|DataQty 1"
    TotHrs = 20
  Case "Report 1|MatLevel 1|DataQty 1"
    TotHrs = 15
  Case "Report 1|MatLevel 2|DataQty 1"
    TotHrs = 15

My apologies that I don't have a ready-made solution at this moment. I can have something by tomorrow.

You could, however, code all the permutations based on your data along with the corresponding hours.

While this would get you where you want to go, it will be an awful lot of coding; I swear there's something I'm missing that would allow much less than 105 CASE statements. There isn't an algorithm that dictates how each Total Hour qty is computed based on the three variable?? If so, capitalizing on that would be the way to go.

Dr. Demento, would you still be able to put an example worksheet together for me?

@Eric, I forgot to reply with Quote so that Dr. D could see I was replying to him. Unfortunately I don't understand your solution step-wise. That is why Dr. D's appears more straight forward for me to use.

 

[stumac]

Rather than have this onchange I would consider a User defined function:

Public Function TotalHours(AssesType As String, Maturity As Integer, Quantity As Integer) As Variant
Dim MatchString As String
MatchString = AssesType & "-" & Maturity & "-" & Quantity
TotalHours = CVErr(xlErrNA)
Select Case MatchString
Case "Report 1-0-1"
TotalHours = 20
Case "Report 1-1-1"
TotalHours = 15
Case "Report 1-2-1"
TotalHours = 15
Case "report 1-0-2"
TotalHours = 21
Case "Report 2-1-3"
TotalHours = 22
Case "Report 3-2-4"
TotalHours = 23
End Select
End Function

As this code to a new module within the VBA project of your workbook and then in your worksheet:

=TotalHours(A1,B1,C1)