Min Formula for pension calculation

[Rhonda]

Hi - am trying to do a formula for pension calculations for all employees.

Maximum pensionable earnings $41,100
Basic exemption amount $3,500 (if you earn less you don't have pension deductions)
Employer contribution rate is 4.95%
Maximum contribution is $1,861,20 or (41,100-3,500 X .0495)
Cell L11 is where the earnings are located

I tried this formula
MIN((41,100-3500),(L11-3500)*0.495

It works fine except when someone earns under 3500, then it puts in a negative number...I need it to say zero if it goes under 1 cent....

Thanks

 

[Aladin Akyurek]

Hi - am trying to do a formula for pension calculations for all employees.

Maximum pensionable earnings $41,100
Basic exemption amount $3,500 (if you earn less you don't have pension deductions)
Employer contribution rate is 4.95%
Maximum contribution is $1,861,20 or (41,100-3,500 X .0495)
Cell L11 is where the earnings are located

I tried this formula
MIN((41,100-3500),(L11-3500)*0.495

It works fine except when someone earns under 3500, then it puts in a negative number...I need it to say zero if it goes under 1 cent....

Thanks

=MAX(0,Calculation)

would avoid a negative result.

 

[barry houdini]

Try

=MEDIAN(0,37600,L11-3500)*0.0495

 

[Rhonda]

Thanks Mr. Houdini it works!