Excel Formula Question

[tjacobs34]

I am an HR manager and would like to figure out everyone's time off on a spreadsheet...here's what I have
Hire Date =today() =SUM(E4-D4)/365 Total Days
2/1/04 5/30/07 3.33

All that works...now I have this table
Length of Service Number of days
<1 and >3 15
<3 and >5 17
<5 and >7 20
<7 and >10 22
<10 25

Any suggestions would be great. I tried =sumif and vlookup but couldn't get anything to work...HELP

 

[SkipVought]

Hi,

A lookup table like this...
[tt]
Length of Service Number of days
0 15
3 17
5 20
7 22
10 25
[/tt]
The the lookup...
[tt]
=INDEX($B$6:$B$10,MATCH(E6,$A$6:$A$10,1),1)
[/tt]
where B6 is your lookup length of service.

Skip,

_[red][/red]

 

[AnotherHiggins]

A few notes:

*The "SUM" in "=SUM(E4-D4)/365" doesn't do anything. Just use "=(E4-D4)/365"

*I think you have your greater than and less than signs mixed up. Either that, or I am really confused by what your table means.

*What is in E4 and D4? I assume those cells contain the Hire Date and Today, respectively? But it isn't clear from your post.

Assuming that all my, um, assumptions are correct, then do the following:

Change you table to look like this:[tt]
Length of Service Number of days
0 15
3 17
5 20
7 22
10 25[/tt]

If that table is in A1:B6 and the formula =(E4-D4)/365 is in F4, then your formula to calculate number of vacation days should be:

=INDEX($B$2:$B$6,MATCH(F4,$A$2:$A$6,1),1)


[Edit: Of course, Skip beat me by a mile. But I'm posting anyway. So there. ]

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[SkipVought]

Hey, John, you've beat me quite a few times lately! ;-)

Skip,

_[red][/red]

 

[tjacobs34]

Thanks guys! I guess I was trying to make it harder than it really was...long day of looking at employee census data.

 

[yogia]

Hi tjacobs34:

In addition to the fine contribution from Skip and John, following is another way ...

=LOOKUP(F4,{0,1,3,5,7,10},{0,15,17,20,22,25})

where F4 is the number of years of service.



Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com