if then function

[Brad]

I would like to create a formula to multiply the data in A1 by a percentage
based on the percentage in B1. So, if B1<80% then A1x10%, if B1 is 80% to
89% then A1x15%, if B1is 90% to 99% then A1x18% and if B1>100% then A1x20%.

Does anybody have a lot of experience with the if then function?

 

[Ron Rosenfeld]

I would like to create a formula to multiply the data in A1 by a percentage
based on the percentage in B1. So, if B1<80% then A1x10%, if B1 is 80% to
89% then A1x15%, if B1is 90% to 99% then A1x18% and if B1>100% then A1x20%.

Does anybody have a lot of experience with the if then function?

I would use VLOOKUP for more flexibility than doing nested IF's.

For example, set up a table named 'tbl':

0 10%
80% 15%
90% 18%
100% 20%


Then use the formula:

=VLOOKUP(B1,tbl,2)

to compute the percentage.

So in your example,

=A1*VLOOKUP(B1,tbl,2)

Note that the table output makes some assumptions about the undefined values in
your example. You do not define what you want if B1 is between 89% and 90%;
nor do you define what you want if B1 is greater than 99% up to and including
100%. Ensure that the assumptions made are what you want.


--ron