Issue with too many nested IF statements

Hi,

I was wondering if it would be possible for somebody to help me in either recreating a shorter (or working) equation that satisfies 6 conditions, let me explain.

There are 3 important cells:
1) an input cell – the user will enter either “a”, “b”, “c”, “d”, “e” or “f” (we will call x)
2) a variable cell – which will be a number previously generated (we will call z)
3) the output cell (to be calculated – we will call y)

Conditions:
1) If x = “a” then y = z
2) If (x = “b” AND z >= 2) then y = z else y = 2
3) If (x = “c” AND z >= 3) then y = 1.5z else y = 3
4) If (x = “d” AND z >= 4) then y = 2z else y = 4
5) If (x = “e” AND z >= 6) then y = 2z+4 else y = 6
6) If (x = “f” AND z >= 12) then y = 3z else y = 12

Here is one of the variations of equations to which I have used. It obviously does not work as there are more than 7 IF statements but it is the closest to what I think should work.

=IF(R6="a",I6,
IF(AND(R6="b",I6>=2),I6,
IF(AND(R6="b",I6<2),2,
IF(AND(R6="c",I6>=3),I6*1.5,
IF(AND(R6="c",I6<3),3,
IF(AND(R6="d",I6>=4),I6*2,
IF(AND(R6="d",I6<4),4,
IF(AND(R6="e",I6>=6),I6*2+4,
IF(AND(R6="e",I6<6),6,
IF(AND(R6="f",I6>=12),I6*3,
IF(AND(R6="f",I6<12),12,"ENTER LETTER")))))))))))

Those around me have suggested using "VLOOKUP" or "CHOOSE" funcitons but I cannot create a working formula.

It has been a while and I have still not been able to create a working function. I hope my explanation is clear, any help would be appreciated.

Thanks

(I have attached a sample worksheet)

daddylonglegs...

I may be wrong, but I think you got your Value If True/Value if False arguments backwards...

I think, according to the OPs pseudocode, it should be...

=IF(I6>=LOOKUP(J6,{"a","b","c","d","e","f";0,2,3,4,6,12}),CHOOSE(MATCH(J6,{"b","c","d","e","f"},0),I6,1.5*I6,2*I6,2*I6+4,3*I6),LOOKUP(J6,{"a","b","c","d","e","f";0,2,3,4,6,12}))

Another way which might be easier to read/maintain as it pretty well follows the logic that you wrote is to write a short user defined function as follows and place it in a module for that workbook

Please Login or Register  to view this content.

Martin

Originally Posted by sjak

Thanks for the quick reply daddylonglegs.

The formula didn't seem to work - I may have been a little misleading with the attachment since my reference to cells in the post was different than that in the attachment (the attachment above has been fixed).

As for the formula, I worked out the numbers by hand with the first variable in A6 which is 6.56. Here is how it should look after entering a letter between "a" and "f"

a = 6.56
b= 6.56
c= 9.84
d=13.12
e=17.12
f= 19.68

"f" worked for the first two examples on the worksheet but not for the third.

Any ideas?

Did you see my comment to daddylonglegs' formula?... I think he got the results arguments backwards...

try instead:

=IF(A6>=LOOKUP(J6,{"a","b","c","d","e","f";0,2,3,4,6,12}),CHOOSE(MATCH(J6,{"a","b","c","d","e","f"},0),A6,A6,1.5*A6,2*A6,2*A6+4,3*A6),LOOKUP(J6,{"a","b","c","d","e","f";0,2,3,4,6,12}))

It is a little confusing now. I am not sure what your conditions are now.

Your initial post said If (x = “f” AND z >= 12) then y = 3z else y = 12 where I assume z is A6... In your sheet, as you say, A6 is less than 12, so according to your condition, it should yield 12, not 19.68

Can you rewrite the actual conditions you have in your initial post. Use the actual cell references instead of x,y,z.