Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel conditional formula
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel conditional formula
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 "victor" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional formula in Excel | Excel Discussion (Misc queries) | |||
Excel 2002 Formula: Urgent Conditional Formula Required Right Away - if possible | Excel Discussion (Misc queries) | |||
conditional formula in Excel | Excel Worksheet Functions | |||
Conditional Excel formula | Excel Worksheet Functions | |||
Excel conditional formula | Excel Programming |