11-11-2009, 06:44 PM
You can also use excel to do this...
=IF(A1<=2.5,A1*1.2,IF(A1<=10,A1*1.18,IF(A1<=20,A1*1.15,IF(A1>20,A1*1.11))))
Setting up cells with your test values is a good idea in case you ever want to change them on the fly. It's always good to avoid hard-coding your values:
C1=2.5, C2=10, C3=20
D1=1.20, D2=1.18, D3=1.15, D4=1.11
=IF(A1<=$C1,A1*$D1,IF(A1<=$C2,A3*$D2,IF(A1<=$C3,A3*$D3,IF(A1>$C3,A3*$D4))))
The "$" sign prevents the value from being updated when you copy and paste or expand the formula over multiple cells.
Basically locks that cell into position, where A1 will change relative to the location of the formula.
Then Copy the new data...paste special only values over the old data....Otherwise you'll be pasting the formula which will result in unwanted results.
=IF(A1<=2.5,A1*1.2,IF(A1<=10,A1*1.18,IF(A1<=20,A1*1.15,IF(A1>20,A1*1.11))))
Setting up cells with your test values is a good idea in case you ever want to change them on the fly. It's always good to avoid hard-coding your values:
C1=2.5, C2=10, C3=20
D1=1.20, D2=1.18, D3=1.15, D4=1.11
=IF(A1<=$C1,A1*$D1,IF(A1<=$C2,A3*$D2,IF(A1<=$C3,A3*$D3,IF(A1>$C3,A3*$D4))))
The "$" sign prevents the value from being updated when you copy and paste or expand the formula over multiple cells.
Basically locks that cell into position, where A1 will change relative to the location of the formula.
Then Copy the new data...paste special only values over the old data....Otherwise you'll be pasting the formula which will result in unwanted results.