formula problem

[Tony]

Hi everyone, let me state from the outset that I am hopeless at maths, that
is why I am needing help
The problem is, if I have a starting amount of say
$1057 and wish to work out some deductions from this.
if amount is less than $60 take away nothing
if amount is between $61 and $142 take away 50 cents in the dollar
if the amount is greater than $142 take away 70 cents in the dollar upto and
including the full amount.
As you can see I know what I want but for the life of me I can't get my head
around the formula
Cheers and many thanks
Tony..

 

[J.E. McGimpsey]

One way:

Assume starting amount is in A1, deduction is in B1 and formula for
the total in C1:

C1: =MAX(0,A1-B1*(0.5+0.2*(B1>142))*(B1>60))

 

[Tony]

Hi everyone, let me state from the outset that I am hopeless at maths, that
is why I am needing help
The problem is, if I have a starting amount of say
$1057 and wish to work out some deductions from this.
if amount is less than $60 take away nothing
if amount is between $61 and $142 take away 50 cents in the dollar
if the amount is greater than $142 take away 70 cents in the dollar upto and
including the full amount.
As you can see I know what I want but for the life of me I can't get my head
around the formula
Cheers and many thanks
Tony..

Thanks for all the replys fellas, but I think I may have put this to you
round the wrong way
I forgot another amount.
Here is the correct scenario..
A person gets a government benefit of $334.80 per fortnight
if they earn any income in that fortnight the benefit reduces thus
Income
$0 to $62 no reduction
$62 to $142 benefit reduced by 50 cents in the dollar for the amount between
these numbers
$142 and greater, benefit is reduced by 70 cents in the dollar for anything
over $142.
I hope this makes sense
Cheers again and many thanks
Tony..

 

[Norman Harker]

Hi Tony!

Please don't cross post.

I think that this calculates correctly:

=(IF(A1<62,334.8,IF(A1<142,334.8-((A1-62)*0.5),MAX(0,334.8-40-((A1-142
)*0.7)))))

A1 contains the income.

The formula calculates the benefit.

If you want to calculate the Income inclusive of benefit use:

=A1+
(IF(A1<62,334.8,IF(A1<142,334.8-((A1-62)*0.5),MAX(0,334.8-40-((A1-142)
*0.7)))))


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Public Holidays Saturday: Bosnia-Herzegovina (Vivovdan (Orthodox));
Chile (St. Peter & St. Paul Day); Haiti (M'Guine Sauveur table servie
pour maitresse Erzulie, Tenaisse, Mambo); Taiwan (Birthday of Kuan
Kung (God of War)); USA (WW1 Day).
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.