# Nested IF....Maybe

#### bschulze

##### Active Member
I am looking for a formula to calculate a cell. I cannot come up with the proper formula. It has multiple parts:

Cell Range: A1:B1 (Sum)

-5% on first \$100
-Over \$100....2% up to \$95

This cell is to never be over \$100.00. I have tried this with some nested if statements but have had little success. Any assistance would be great.

### Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

#### lozzablake

##### Well-known Member
Can you clarify with an example? eg:

f A1+B1 is 400 you would have:

5% x 100 + 2% x 95 = 6.9.

#### njimack

##### Well-known Member
Code:
``=IF(A1<=100,A1*0.05,MIN(5+((A1-100)*0.02),95))``

#### bschulze

##### Active Member
Thanks for the help. This is the formula I ended up using:

=IF(SUM(A1:B1)<=100,SUM(A1:B1)*0.05,MIN(5+((SUM(A1:B1)-100)*0.02),100))

#### SteveO59L

##### Well-known Member
=IF((100*0.05)+((H12-100)*0.02)>100,100,IF(H12<=100,H12*0.05,IF(H12>100,((100*0.05)+((H12-100)*0.02)),"Something Else")))

#### pgc01

##### MrExcel MVP
Hi bschulze

If the value is in A1 you can use:

Code:
``=MIN(A1,100)*5%+MIN(95,MAX(0,(A1-100)*2%))``

However, I don't understand in your post: "Cell Range: A1:B1 (Sum)"
What do you mean, this formula is to be applied to each of the 2 cells or to their sum? Please explain.

Hope this helps
PGC

Replies
7
Views
32
Replies
4
Views
34
Replies
5
Views
64
Replies
3
Views
126
Replies
6
Views
281