formula to calculate a benefit amount

**pgruening** · 08-09-2005, 01:10 PM

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** · 08-09-2005, 01:28 PM

Try this, Peter:

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

Does that help?

Regards,
Ron

**Nigel_hough** · 08-09-2005, 01:29 PM

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** · 08-09-2005, 01:39 PM

THANKS!! to both of you, works great!

Peter

**pgruening** · 08-09-2005, 01:44 PM

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** · 08-09-2005, 01:50 PM

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