Home |
Search |
Today's Posts |
#1
|
|||
|
|||
formula to calculate a benefit amount
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 -- pgruening ------------------------------------------------------------------------ pgruening's Profile: http://www.excelforum.com/member.php...o&userid=26011 View this thread: http://www.excelforum.com/showthread...hreadid=394278 |
#2
|
|||
|
|||
Try this, Peter: =ROUNDUP(MIN((0.4*A1)+0.15*MIN(3500,A1),3500),0) Does that help? Regards, Ron -- Ron Coderre ------------------------------------------------------------------------ Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419 View this thread: http://www.excelforum.com/showthread...hreadid=394278 |
#3
|
|||
|
|||
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 :) -- Nigel_hough ------------------------------------------------------------------------ Nigel_hough's Profile: http://www.excelforum.com/member.php...o&userid=20623 View this thread: http://www.excelforum.com/showthread...hreadid=394278 |
#4
|
|||
|
|||
THANKS!! to both of you, works great! Peter -- pgruening ------------------------------------------------------------------------ pgruening's Profile: http://www.excelforum.com/member.php...o&userid=26011 View this thread: http://www.excelforum.com/showthread...hreadid=394278 |
#5
|
|||
|
|||
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 -- pgruening ------------------------------------------------------------------------ pgruening's Profile: http://www.excelforum.com/member.php...o&userid=26011 View this thread: http://www.excelforum.com/showthread...hreadid=394278 |
#6
|
|||
|
|||
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 -- Ron Coderre ------------------------------------------------------------------------ Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419 View this thread: http://www.excelforum.com/showthread...hreadid=394278 |
#7
|
|||
|
|||
sorry Ron works great I put a wrong cell reference in the formula Thanks again Peter -- pgruening ------------------------------------------------------------------------ pgruening's Profile: http://www.excelforum.com/member.php...o&userid=26011 View this thread: http://www.excelforum.com/showthread...hreadid=394278 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need "reverse" amortization; calculate loan amount | Excel Worksheet Functions | |||
would like to know the formula amount and display word | Excel Discussion (Misc queries) | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
how do I create a formula to calculate sales tax using Yes & No | Excel Worksheet Functions | |||
Formula to calculate only the negative numbers | Excel Worksheet Functions |