Need Nested If formula help please

[dbwiz]

Here is my formula - I am getting VALUE and am pretty sure I am missing a bracket or two...

=IF(I253>=5<10,(L253*20%)),IF(I253>=10<15,(L253*30%)),IF(I253>=15<20,(L253*40%)), IF(I253>=20,(L253*50%))

Bottom line: If column I has at least 5 years of service but less than 10, then Column L is multiplied by 20%, if they have at least 10 yos but less than 15, then col L is multiplied by 30%, and so on.

Can someone help me fix this?

 

[Jonmo1]

Try a lookup instead....

=L253*LOOKUP(I253,{0,5,10,15,20},{0,0.2,0.3,0.4,0.5})


However you didn't specify what it would return for values Less than 5...
I made it 0 for less than 5.


Hope that helps.

 

[dbwiz]

Thank you! It works great. I am not quite sure how Lookups work, but it would appear that it starts from zero and each one of the numbers in the array are equivalent to the "greater than or equal to" in my scenario. I'll have to read up on it. At any rate, thank you very much.

 

[Jonmo1]

Glad to help...

here's how it works..

=L253*LOOKUP(I253,{0,5,10,15,20},{0,0.2,0.3,0.4,0.5})

It takes the value in I253 and compares it to the first array {0,5,10,20}
It looks for the largest number in that array that is less than or equal to I253.
It records the position # in the first array of the largest value <= I253.
It then returns the corresponding value from the same position # in the 2nd array {0,0.2,0.3,0.4,0.5}

It is important for the values in the first array to be sorted Ascending (smallest to largest, left to right)
And the 2nd array sorted according to their corresponding values in the first array.


Hope that helps.