excel conditional formula

[Guest]

Hello everyone,
We have a table with few thousands records. Each record has different amount
entered in same column. See example of this table below.
We need to create a formula that adds a different percentage in column B to
amounts that are entered in column A. For example to the amounts between 100
and 900 range it will add 30%. To the amounts between 1000 and 2000 it will
add 25%. To the amounts between 2100 and 5000 range it will add 20% and so
on.

A B
100 130
300 390
800 1040
1500 1875
1900 2375
3000 3600
5000 6000
5500 6600
We would like to be able to do this not by manually sorting by ranges and
applying formula to each range, but applying one formula to whole column if
it is possible.

 

[William Benson]

You are going to be limited tin the number of nested if's that you can use.
No single formula is going to be able to handle something like this if "and
so on" is too many. Someone else here can comment on how many is too many.

I assume that all your numbers increment by multiples of a hundred, which is
why you have no test for < 100, nor between other end points?


B2 = if(A2 <=900, 1.3*A2,if(A2<=2000,A2*1.25,if(S2<=5000,A2*1.2 ... ))))

and make sure you have enough ending parentheses.

I think Chip Pearson has a solution for this, like most things

http://www.cpearson.com/excel/nested.htm