OT Excel formula help

Nov 09, 2017 18 Replies

Rate this thread:

Saved reading

eccentricdyslexic 9 years ago

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

Vote

Information here may be outdated?

Bob Eager 9 years ago

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.

Vote

Paul Herber 9 years ago

Look at the Vlookup function.

Vote

ss 9 years ago

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

formatting link

Vote

Paul Herber 9 years ago

Cough. Excuse me, no I didn't.

Vote

The Natural Philosopher 9 years ago

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.

Vote

GB 9 years ago

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

Vote

stuart noble 9 years ago

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! :-)

Vote

Richard 9 years ago

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"))

Vote

Nick 9 years ago

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?

Vote

Bob Eager 9 years ago

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)

Vote