Help with an Excel formula

[Christina]

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,

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Ron Coderre]

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)

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

I think I did. Thanks for the catch.

--
Reagrds,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

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)

 

[Christina]

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

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

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!