OT Excel formula help

[eccentricdyslexic]

Hi peeps, I couldn?t find an active ng to ask this question in so t hought I?d ask here:-)

Need a formula to calculate which age range an employee is currently in and display the result in cell c10 ie I have date of birth for an employee in a1 The current date is in b2 If the current age of the employee is under 30, the figure in c4 applies If the current age of the employee is between 30 and 70, the figure in c5 a pplies If the current age of the employee is over 70, the figure in c6 applies

Does anyone know how I can get a formula to do this?

Ta chaps,

Steve

[Bob Eager]

Work out the age first (simple subtraction of dates, etc). Make a small table with ages in first column and the value you want in second column. Just use 30,40, etc. in first column.

Then use @LOOKUP - see the help.

If there's an exact match, that's obviously it. If it can't find the age in the first column, it'll select the age that is the nearest (below) the one being looked up. So you get a range effect.

[Paul Herber]

Look at the Vlookup function.

[ss]

If you need further help go here and someone will do the formula for you....

[Paul Herber]

Cough. Excuse me, no I didn't.

[The Natural Philosopher]

Nested IF statements

I cant rememberthe format but I think if (istruue, A, B) such that if isteue is true then the answre is A otherwise its B.

B can be another IF statement.

[GB]

=IF(EDATE(A1,30*12)>B2,C4,0)+IF(EDATE(A1,70*12)

[stuart noble]

so thought I?d ask here:-)

and display the result in cell c10

Maybe a combination of IF and Vlookup. This is one I did earlier, but so mu ch earlier I can't remember why or how. Still, it may give you an idea =IF(A2"",VLOOKUP(P2,name,2,FALSE),"") If a2 isn't empty, look up p2 in the group of cells I've named "name", then move to column 2, and enter that value in a2. If a2 is empty, do nothing.. ...I think! :-)

[Richard]

Adifferent approach, stick this into C10:

=INDIRECT(CHOOSE(IF(ROUND((B2-A1)/365.25,1)=30,ROUND((B2-A1)/365.25,1)70,3,""))),"C4","C5","C6"))

[Nick]

When was lookup() introduced. I know vlookup(),hlookup() and index()/match() but I didn't know lookup(). It seems strange to me that I don't know it, so I wondered if it was a new function i.e. later than

2003?

[Bob Eager]

No idea. I only really got into Excel in late 2002, and I think it was there then.

(I had to run classes in it - not actually teach it - for a few years); my colleague, who actually taught it, was bloody good and I have found it all very useful)

[eccentricdyslexic]

Hi Richard,

I tried it in excel on the iPad but it?s erroring over the 365.25 ( highlighting it in blue?) and not wanting to play ball.

Cheers

Steve

[eccentricdyslexic]

GB I tried your formula and it?s triping up on A1 for some reason. Will have a play tomorrow with a pc excel app before concluding neither Wor k;-)

[Robin]

The DATEDIF function is still in Excel (albeit hidden) for anyone who fancies a bit of nostalgia for Lotus 1-2-3: for the age in years

=DATEDIF(A1,B2,"Y")

[Richard]

That's odd. It works fine in Excel 2003 in windows. I'll have to try it on the wife's iPad later.

[eccentricdyslexic]

You?re right richard, works fine on the pc. Thanks! PS Could it be modified to show in cell d4 the age calculated?

:-)

Steve

[GB]

"triping up on A1" what do you mean exactly?

[Richard]

Martin Brown has answered that. You'd have to enter the formula in cell d4.

[eccentricdyslexic]

Thanks everyone for your help, all sorted:-)