Formula not working

[vijeshlalv]

Hi, Can anyone point out the error i had made in my formula.

=IF(G15=K3,(((0.15*(G10*G10))/G5)*(460/1.8)),IF(AND(G15=K4,(((0.15*(G10*G10)/G5)*(460/2)))),IF(AND(G15=K5),(((0.15*(G10*G10)/G5)*(460/2.2)))),IF(G15=K6,(((0.15*(G10*G10)/G5)*(460/2.5))))*0))


G15 is the cell for selecting the grade for which the list is in K3 to K6. Based on this selection the formula should change.

 

[NARAYANK991]

Hi ,


Try this :


=IF(G15=K3,0.15*(G10*G10)/G5*460/1.8,IF(G15=K4,0.15*(G10*G10)/G5*460/2,IF(G15=K5,0.15*(G10*G10)/G5*460/2.2,IF(G15=K6,0.15*(G10*G10)/G5*460/2.5,0))))


There are too many parentheses , which makes finding out the problem tedious.


Narayan

 

[vijeshlalv]

Awesome, Thanks a lot, this works.

 

[Luke M]

To make it easier for future edits, might consider doing this:

=0.15*G10^2/G5*460/CHOOSE(MATCH(G15,K3:K6,0),1.8,2,2.2,2.5)


Reduced the math portion so that the common part of equation is outside of any function. Then we just use the MATCH and CHOOSE function to figure out what number we want to use in the denominator. Other benefits are that I didn't have to repeat any cell references, and you can easily add more possible numbers into the CHOOSE function.


Might also want to consider building a lookup table. If the values you wanted were in col L next to the search values, formula can become:

=0.15*G10^2/G5*460/LOOKUP(G15,K3:L6)