Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with an Excel formula
I need help creating an excel formula. I have a spreadsheet that calculates
employee paid time off based on years of employment, but there is a cap. So, I need to create a formula that indicates that time off is accrued at a specific rate, unless the time cap has been reached, in which case the cap amount is reflected. I know it is an "IF" formula but can't get it to work. Any help is appreciated! Thanks, -- Christina |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with an Excel formula
You need a Max() formula something like:
=MAX(maximum allowed , years * accrual rate) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Christina" wrote in message ... I need help creating an excel formula. I have a spreadsheet that calculates employee paid time off based on years of employment, but there is a cap. So, I need to create a formula that indicates that time off is accrued at a specific rate, unless the time cap has been reached, in which case the cap amount is reflected. I know it is an "IF" formula but can't get it to work. Any help is appreciated! Thanks, -- Christina |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with an Excel formula
Typo?
I think you meant: =MIN(maximum allowed , years * accrual rate) Indicating the lesser of Max Allowed or the calculated amount. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Sandy Mann" wrote in message ... You need a Max() formula something like: =MAX(maximum allowed , years * accrual rate) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Christina" wrote in message ... I need help creating an excel formula. I have a spreadsheet that calculates employee paid time off based on years of employment, but there is a cap. So, I need to create a formula that indicates that time off is accrued at a specific rate, unless the time cap has been reached, in which case the cap amount is reflected. I know it is an "IF" formula but can't get it to work. Any help is appreciated! Thanks, -- Christina |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with an Excel formula
Sandy,
Thank you for the suggestion. Unfortunately, the formula needs to be a little more complex. Here is a sample of my worksheet... Balance Taken Accrued Balance Hire Date 9/30/2008 1-Oct 10/15/08 10/15/08 1/1/2006 160.00 6.67 160.00 3/7/2005 160.00 6.67 160.00 8/15/2005 156.67 6.67 160.00 The dates on the left are dates of hire, the other dates are the current payperiod dates. You can see that there is a column for time taken (this is manually entered) and a rate of accrual. I have a formula for the rate of accrual that reflects accrual based on start date. My confusion is getting the "balance" column to reflect the possible cap of time in relation to start date and current accrual. Does that make sense? Can you or anyone else help? Thank you! -- Christina "Sandy Mann" wrote: You need a Max() formula something like: =MAX(maximum allowed , years * accrual rate) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Christina" wrote in message ... I need help creating an excel formula. I have a spreadsheet that calculates employee paid time off based on years of employment, but there is a cap. So, I need to create a formula that indicates that time off is accrued at a specific rate, unless the time cap has been reached, in which case the cap amount is reflected. I know it is an "IF" formula but can't get it to work. Any help is appreciated! Thanks, -- Christina |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with an Excel formula
I think that you may require someone else to help you, it is 12:15am and I
am just off to bed. I also do not understand you cloumns. I assume that: Hire Date 1/1/2006 3/7/2005 8/15/200 are the hire dates and: 160.00 160.00 156.67 are the times taken with: 6.67 6.67 6.67 the Accrual rates I then assume that the final: 160.00 160.00 160.00 must be the "Balance" that you are lookong for. If that is not correct can you explain further and then no doubt someone will jump in and help you. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Christina" wrote in message ... Sandy, Thank you for the suggestion. Unfortunately, the formula needs to be a little more complex. Here is a sample of my worksheet... Balance Taken Accrued Balance Hire Date 9/30/2008 1-Oct 10/15/08 10/15/08 1/1/2006 160.00 6.67 160.00 3/7/2005 160.00 6.67 160.00 8/15/2005 156.67 6.67 160.00 The dates on the left are dates of hire, the other dates are the current payperiod dates. You can see that there is a column for time taken (this is manually entered) and a rate of accrual. I have a formula for the rate of accrual that reflects accrual based on start date. My confusion is getting the "balance" column to reflect the possible cap of time in relation to start date and current accrual. Does that make sense? Can you or anyone else help? Thank you! -- Christina "Sandy Mann" wrote: You need a Max() formula something like: =MAX(maximum allowed , years * accrual rate) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Christina" wrote in message ... I need help creating an excel formula. I have a spreadsheet that calculates employee paid time off based on years of employment, but there is a cap. So, I need to create a formula that indicates that time off is accrued at a specific rate, unless the time cap has been reached, in which case the cap amount is reflected. I know it is an "IF" formula but can't get it to work. Any help is appreciated! Thanks, -- Christina |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
When I enter a formula, Excel shows the formula not the results | Excel Worksheet Functions | |||
Excel 2002 Formula: Urgent Conditional Formula Required Right Away - if possible | Excel Discussion (Misc queries) | |||
Build excel formula using field values as text in the formula | Excel Worksheet Functions | |||
Excel 2002 formula displayed not value formula option not checked | Excel Worksheet Functions | |||
How do I view formula results intead of formula in excel? | Excel Worksheet Functions |