Any Excel formula wizards in here?? Need some help...

[grannylib]

thanks!

[lizziegrace]

1. I can give it a shot.

What do you need?

[grannylib]

3. I'll explain as best I can...

I have what is for me a knotty problem that I think is going to require either a really long nested formula or maybe a lookup table...but I'm too stupid about Excel to know where to start!

I have an employee form that includes an area for calculating the payout of benefits if a person quits, and the amount paid out depends partly on the date on which the person quits. I have a table all set up with the dates (1/1-1/14; 1/15-31; 2/1-2/14; 2/15-2/29; etc.) in one column and the fraction for that part of the year in the other column of my table. On the form itself is a cell for the quit/term date. Currently, the quit/term date is entered m/dd/yyyy; the dates on the table are entered m/dd. There is also a group of cells where one manually enters the amount of vacation/personal leave allotted at the beginning of the year and the amount of each used to date.Is there a way that I can link the cells that do the calculating of payout amount to the correct cells in the lookup table, based on the quit date???

I am probably being clear as mud here...I apologize! Not sure if there is a better way to explain it or not.
But anyhoo....help would surely be appreciated!

[ThomCat]

2. What are you trying to do.

I used to teach software classes, including Excel classes. That was many versions ago, but I still use Excel every day so I can probably help. :)

[grannylib]

4. I'll explain as best I can...

I have what is for me a knotty problem that I think is going to require either a really long nested formula or maybe a lookup table...but I'm too stupid about Excel to know where to start!

I have an employee form that includes an area for calculating the payout of benefits if a person quits, and the amount paid out depends partly on the date on which the person quits. I have a table all set up with the dates (1/1-1/14; 1/15-31; 2/1-2/14; 2/15-2/29; etc.) in one column and the fraction for that part of the year in the other column of my table. On the form itself is a cell for the quit/term date. Currently, the quit/term date is entered m/dd/yyyy; the dates on the table are entered m/dd. There is also a group of cells where one manually enters the amount of vacation/personal leave allotted at the beginning of the year and the amount of each used to date.Is there a way that I can link the cells that do the calculating of payout amount to the correct cells in the lookup table, based on the quit date???

I am probably being clear as mud here...I apologize! Not sure if there is a better way to explain it or not.
But anyhoo....help would surely be appreciated!

[ThomCat]

5. First,

The collumn with range of dates needs to have just single dates in it. Use the first or last day of the period, but be consistent. That will allow you to use the dates in a formula.

I'd have to play with this to find the most elegant way to do it. But a nested program is probably the easiest. (I like writing formulas so I tend to go for that option.)

Using the last date of each period, you could simply do a nested formula that asks, is this date greater then that one, if not then is it greater then that one, if not then is it greater then that one? The formula would basically say, "nope, he left after that date, and after that date, and after that date, but before that date!" You can have a value tied to each date. He left before May 1st (meaning he left during the period immediately prior to May 1st) so use this value.

A lookup table would work too. But I find look-up tables are sometimes buggy when you are matching ranges rather than lists.

[grannylib]

6. Thanks for the suggestions; I will give it a try!