Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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, |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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, |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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, |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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, |
#5
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Maximum amount allowed | Excel Discussion (Misc queries) | |||
Maximum amount of macros supported | Excel Discussion (Misc queries) | |||
What is the Maximum amount of sheets in a workbook? | Excel Discussion (Misc queries) | |||
Maximum amount of characters in a footer | Excel Discussion (Misc queries) | |||
formula that includes a maximum amount | Excel Worksheet Functions |