Thread: Many IF statements???

Hello,

I really tried to solve this problem using IF statements but it just didn't work as I need about 120 nested if statements! So I am hoping someone here is more knowledgeable in Excel than me and can help me out.

Here is the problem (also see the attached sample Excel file):

I have a worksheet that needs to be filled by the user. The data the user chooses are taken from two tables. One for motors ABC, and the other for motors XYZ.

First problem, the user has to enter the motor size, speed, and type and the efficiency. The size, speed and type are standard so creating their drop down list is easy. But the efficiency depends on the type, size, and speed, so how do I do that without having to create 120 nested if statements! (Well, I can't do that anyway because of the 7 max nested if statements in Excel.) So what I want, for example, if the user chooses motor ABC, size 20 horsepower, and speed 1200 rpm, then cell C23 should display the following drop down list:

89.9
90.0
90.1
90.2

The second problem which is to display the cost in cell C24 is similar to the first problem but probably a bit easier, but still requires many if statements. Can you please help?
|
Thanks.

Put this formula in C23:

=INDEX(INDIRECT(IF(C20="ABC","B5:D11","H5:J11")),MATCH(C21,A5:A11),MATCH(C22,B4:D4))

and this one in C24:

=INDEX(INDIRECT(IF(C20="ABC","E5:E11","K5:K11")),MATCH(C21,A5:A11))

Hope this helps,

Pete

It's not working for me. Could you please post a working sample file? Thanks a lot

SORRY! It's working! But how do I make the range appear as a list rather than a range? So for a range that says "83.7-83.9%" I want the user to be able to select just one value. So I want a drop down list that says:

83.7
83.8
83.9

?

SORRY! It's working! But how do I make the range appear as a list rather than a range? So for a range that says "83.7-83.9%" I want the user to be able to select just one value. So I want a drop down list that says:

83.7
83.8
83.9

?

The attached file seems to do what you want. I've amended some of the entries in your tables to remove the %age symbols and defined the single values as text values. I've also moved the formula that was in C23 to E23, and put this formula in F23:

=IF($E$23="N/A","",IF(ISNA(SEARCH("-",$E$23)),--$E$23,--MID($E$23,1,4)))

I put this slightly different formula in F24:

=IF(F23="","",IF(ISNA(SEARCH("-",$E$23)),"",IF(F23+0.1>--RIGHT($E$23,4),"",F23+0.1)))

then copied this down to F40 (i.e. beyond where you would need it).

Then I defined a dynamic named range for this group of cells, calling it "efficiency". I set up data validation in C23 using "efficiency" as the source, so you can try this out and let me know if it does what you wanted. Note that the returned values are actual numbers, not percentages, so if you wanted to use them in some other formula you will need to divide them by 100.

Hope this helps.

Pete

It's working, doing what I want, and it's wonderful! But I am having hard time applying those formulas to my real worksheet because I can't locate the 'efficiency' drop down list. Can you help, please?

Ok, the hard time I am having is with the dynamic named range you defined...I don't know how to do that. Can you show me how, please?

Ok, the hard time I am having is with the dynamic named range you defined...I don't know how to do that. Can you show me how, please?

Thank you, thank you, and thank you! You really saved the day

Pete, can I bother you with one more question?

Is there a way to clear the entry in cell C23 based on any changes in cells C20:C22? This way the user is forced to select a new efficiency value that corresponds to the changes in cells C20:C22.

Thanks a lot in advance

I can't think of a way to do that (other than using a macro), as the cell is for data entry. One thing you might consider is to use a cell somewhere with a formula like this:

=IF(isnumber(MATCH(C23,efficiency,0)),"","INVALID EFFICIENCY")

and format that cell as red and bold (and large fontsize) in order to alert the user that they have to change the value in C23.

Hope this helps.

Pete

Thanks a lot Pete, I really appreciate all your help. Keep up the good work