excel calculation

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Shiblie Bshara - 16 Jan 2008 08:36 GMT

hi,
I need to do a formula that covers the next conditions:

Max= 5000
Total= 5700
Min = ???

if total < max, then min = total*0.06
if total = max, then min = total*0.06
if total > max, then min = (max*0.06)+(total-max)

thanx

Reply to this Message

Mike H - 16 Jan 2008 08:55 GMT

Hi,

Try this

=IF(B2>B1,(B1*0.06)+(B2-B1),B2*0.06)

Where
B1= max
B2= total

Mike

> hi,
> I need to do a formula that covers the next conditions:
[quoted text clipped - 8 lines]
>
> thanx

Reply to this Message

Bernard Liengme - 16 Jan 2008 14:21 GMT

The IF formula is good but you can also use an IF-less formula
With Max in A1 and Total in B1
=(B1*0.06)*(B1<=A1)+((A1*0.06)+(B1-A1))*(B1>A1)
best wishes

Signature

Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

> hi,
> I need to do a formula that covers the next conditions:
[quoted text clipped - 8 lines]
>
> thanx

Reply to this Message