How do I set a maximum amount in a cell?

[Griffindor107]

I am trying to create a sequence of equations that will calculate the amount
of paid vacations my employees have. In our policies, you can accrue paid
vacation every month, but there is a cap on how much you an you only can have
a certain amount of paid vacation. So, I would like to to have a cell add
up, but have a maximum amount that it can add to and once it hits that
maximum, it won't go any higher.
Any help would be greatly appreciated.
Thanks,

 

[Pete_UK]

Well, although you use the word maximum in your description, it is a
CAP that you want to apply and so the function is MIN. Try this:

=MIN(20,A1)

and put different values in A1. If A1 is, say, 10 then the function
will return 10, but if you change A1 to 30, then the function returns
20 - this is the capped value.

Perhaps you can see how to use this in your sheet.

Hope this helps.

Pete

 

[John]

Hi Griffindor
This formula will multiply A1by B1 up to 500, anything more and you get 500
=IF(A1*B1<=500,A40*B40,500)
Its just an example maybe more details to get more elaborate.
HTH
John

 

[David Biddulph]

Instead of
=IF(A1*B1<=500,A40*B40,500)
you could use
=MIN(A40*B40,500)