formula to calculate a benefit amount

[pgruening]

I’d appreciate any help for the formula to calculate the following
benefit amount

To calculate the benefit amount take 55% of the first $3,500 of your
monthly earnings, add 40% of the balance of your month earnings up to a
maximum benefit amount of $3,500. Round benefit amount to the next
highest dollar.

For example

Monthly earnings $8,333.33
55% of first $3,500 = $1,925.00
40% of balance $4,833.33 = $1,933.33

Total $3,858.33 which should be rounded to $3858 and is also above the
maximum benefit amount so answer should show as $3,500

Thanks, Peter

 

[Ron Coderre]

Try this, Peter:

=ROUNDUP(MIN((0.4*A1)+0.15*MIN(3500,A1),3500),0)

Does that help?

Regards,
Ro

 

[Nigel_hough]

This function should give you what you want.

Just paste it into a module in vb on your workbook and then you should
be able to call it like any excel function

Just type in a cell =benifits(Number Of Cell with Salary )


Function benifits(Earnings)

If (Earnings > 3500) Then

If (Round(((Earnings - 3500) * 0.4) + 1925, 0) > 3500) Then
benifits = 3500
Else
benifits = Round(((Earnings - 3500) * 0.4) + 1925, 0)
End If

Else

benifits = Round(Earnings * 0.55, 0)

End If

End Function


Hope this helps

 

[pgruening]

THANKS!! to both of you, works great!

Peter

 

[pgruening]

Hi Ron

At first I thought it was working ok but it appears to be giving a
answer of$3,500 no matter what I put in as earnings

Peter

 

[Ron Coderre]

Hmmm...I can't duplicate that error. I copied the posted formula from
the screen to test it and I keep getting correct values.

Using =ROUNDUP(MIN((0.4*A1)+0.15*MIN(3500,A1),3500),0)

For A1 = 2500: I get 1375
For A1 = 3500: I get 1925
For A1 = 8333.33: I get 3500

Did you copy the formula from the screen or re-type it?

Ron

 

[pgruening]

sorry Ron works great I put a wrong cell reference in the formula

Thanks again

Peter