How do I set a maximum amount in a cell?
 

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,

How do I set a maximum amount in a cell?
 

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

On Jan 26, 11:19*pm, Griffindor107
wrote:
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,

How do I set a maximum amount in a cell?
 

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
"Griffindor107" wrote in message
...
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,

How do I set a maximum amount in a cell?
 

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

"John" wrote in message
...
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
"Griffindor107" wrote in message
...
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,

Hello Everybody;

I'm new to this forum (and forums in general) and I have a similar situation with the vacations cap but I have a further question...

With all the posted scenarios the vacation cap is relative.

Let's say that I use =MIN(200,B2-C2)
whe
200 is the cap in hours
B2 is the accrued vacations
C2 is the used vacations

As long as B2-C2 is 200, my cap is going to effective.

But if I have a case whe
B2 = 280
C2 = 0
In this case the employee has reach the limit and the formula will indicate 200 hours available, but if the employee uses 80 hours (C2 = 80) in that period the formula will calculate 200 available hours and not 120.

Is there any way to fix the hours to 200 once the limit has been reach and deduct from that?

thank you!