
Wayne Contello Aug 27, 2013 12:23 PM
in response to EquiusYou can use an extra table and a vlookup() function like this:
B6=VLOOKUP(C3, Mapping :: A:B, 2)
I named the table on the right "Mapping"
main caveat is...
vloopup does a strictly less than test so your bins are actually:
X <5 > 30
5 >= X < 10 > 40
10 >= X < 15 > 50
15 >= X < 1000 > 60

Equius Aug 27, 2013 1:12 PM
in response to Wayne Contello SolvedThanks, Wayne. I also tried it this way (with help on the "AND" part):
=IF(C3<=5%,30%,IF(AND(C3>5%,C3<=10%),40%,IF(AND(C3>10%,C3<=15%),50%,60%)))

Wayne Contello Aug 27, 2013 1:17 PM
in response to EquiusOK. now add the following to your formula:
X <5 > 30
5 >= X < 8 > 35
8 >= X < 10 > 40
10 >= X < 12.5 > 45
12.5 >= X < 15 > 50
15 >= X < 1000 > 60


Equius Aug 29, 2013 8:17 AM
in response to Wayne ContelloThanks, Wayne. Much better solution for this application.

Barry Aug 29, 2013 11:12 AM
in response to EquiusHi Equius,
Agreed that the VLOOKUP soution suggested by Wayne is the better one in this situation.
As an aside, though, your equation using nested IFs could be simplified as well, as there's no need for the use of AND.
The second IF, where the conditions are C3 > 5% AND C3 <=10% will be reached only if C3 > 5%. Since that will always be TRUE at this point, there's no need to retest it.
Original: =IF(C3<=5%,30%,IF(AND(C3>5%,C3<=10%),40%,IF(AND(C3>10%,C3<=15%),50%,60%)))
Revised: =IF(C3<=5%,30%,IF(C3<=10%,40%,IF(AND(C3<=15%),50%,60%)))
To exactly match the results for the VLOOKUP solution, this further revision is needed:
Revised2: =IF(C3<5%,30%,IF(C3<10%,40%,IF(AND(C3<15%),50%,60%)))
Wayne's demonstration of the increasing complexity of the nested IF formula (and consequent increasing probability of making an error in editing the formula) still applies, though.
Regards,
Barry