Calculate a formula to a maximum value

[RPJ]

I need to calculate a formula up to a maximum amount. This is the current formula without having the ceiling I need.

=IF(I4=105,105,+I4-I51+I55)

The maximum total that this can ever show is 105. This works fine unless the "else" part of the formula(+I4-I51+I55) adds to a number greater than 105. This represents the maximum sick leave an employee can carry forward. If some sick time is used then they will earn more sick leave over time until it reaches 105 hours again but it needs to be capped at that amount.

Can someone tell me what I need to add to the formula above. Thanks.

 

[NARAYANK991]

Hi ,


Try this :


=IF(I4=105,105,MIN(105,I4-I51+I55))


Narayan

 

[RPJ]

Brilliant! That seems to work now although using Min seems counter-intuitive. I'm not sure how it works but it seems to. Thanks Excel Ninja.