Nested IF....Maybe

bschulze

Active Member
Joined
Jun 2, 2005
Messages
289
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.
 

Some videos you may like

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
Joined
Dec 15, 2005
Messages
818
Can you clarify with an example? eg:

f A1+B1 is 400 you would have:

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

bschulze

Active Member
Joined
Jun 2, 2005
Messages
289
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
Joined
Apr 21, 2004
Messages
7,896
=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
Joined
Apr 25, 2006
Messages
19,838
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,999
Messages
5,526,167
Members
409,685
Latest member
Bellybb

This Week's Hot Topics

Top