Return value based on range of cells

[Greg]

Hi,
Can anyone suggest how to return a value based upon how old clients were in
different years.
eg
if 5 in 2004 return 1
if 6 in 2004 return 2
and so on
Then repeat this for different years
if 5 in 2005 return 2, etc

Can't use vlookup as too many columns

Thanks for any advise
Greg

 

[Sandy Mann]

Care to give a bit more detail? Are you working with Dates of Birth?

Why should:

if 5 in 2004 return 1

but:

if 5 in 2005 return 2?

--
Regards,

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

 

[Greg]

Thanks for the quick reply Sandy,
It is working with dates of birth but these are converted to integers.
I basically need to convert the age and date of joining a school into the
Year Group that they entered on (Reception, Nursery, Y1 - Y13).
I have the Academic year of entry and the age they entered, now I need to
convert this to the Year Group on entry, eg if 5 in 2004 they entered in
Year1, if 5 in 2005 they entered in Year2
Thanks,
Greg

 

[Sandy Mann]

I may be still not understanding your needs but would:

A10: Academic year at time of birth. (ie if the academic year starts on
April 1 1999 and the DOB was Feb 1 1999 then the Academic year at time of
birth would be 1998. You cold write a formula to calculate this but doesn't
the start date chaage from year to year?)

B10: Age at entry to school

C10 Year in question

D10: =IF(COUNT(A10:C10)<0.3,"",IF(C10-A10<B10,"Entry Age
Error!",(C10-A10)-B10+1))

Post back with more information is I am misunderstanding 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

 

[Roger Govier]

Hi Sandy

I think you meant to type <3 and not <0.3 in your formula
Like yourself, I am very confused over what Greg is asking.

Greg
In your last post, it sounds rather illogical that a child aged 5 in 2004
would enter school in Year 1, and one aged 5 in 2005 would enter in Year 2
I would have thought it would have been the other way around, unless we are
talking about the number of years the school has been operating since 2004.

Here in Wales, the academic year begins on 01 Sep each year. Leaving aside
Nursery etc., a child would start school on the 1st Sept following (or
equaling ) their 5th birthday, so in the current academic year, children in
year 1 would have birthdates falling between 01 Sep 2007 and 31 Aug 2008.

I don't see why you are not using actual dates, or why you believe that
there are too many columns for a lookup.
Could you post some more detail of what data you actually have in what
columns, and exactly what it is that you wish to achieve.

 

[Sandy Mann]

I think you meant to type <3 and not <0.3 in your formula

My goodness where did that come from!

Thanks for the catch Roger.

--
Regards,

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

 

[Greg]

Thanks for the replies. Let me try and explain what I need.
I am trying to get details on which year group pupils entered a school. This
is not when they started their education, but the year group that they joined
when they first arrived at a school (They could have transferred schools).
This will depend on their age at entry and the acad year.
The data I have to work with is DOB and Date of Entry

This varies from Year to Year and would need to return the figure in the 3rd
column based on the data in columns 1 and 2
eg
Acad Year 2003/04
Age on Entry Acad Year of Entry Year Group for Age and Year
18 2003 9
17 2003 8
16 2003 7
15 2003 6
14 2003 5
13 2003 4
12 2003 3
11 2003 2
10 2003 1
9 2003 R
8 2003 N2
7 2003 N1
6 2003 -
5 2003 -
4 2003 -
3 2003 -

Then
Acad Year 2004/05
Age on Entry Acad Year of Entry Year Group for Age and Year
18 2004 10
17 2004 9
16 2004 8
15 2004 7
14 2004 6
13 2004 5
12 2004 4
11 2004 3
10 2004 2
9 2004 1
8 2004 R
7 2004 N2
6 2004 N1
5 2004 -
4 2004 -
3 2004 -

Acad Year 2005/06
Age on Entry Acad Year of Entry Year Group for Age and Year
18 2006 11
17 2006 10
16 2006 9
15 2006 8
14 2006 7
13 2006 6
12 2006 5
11 2006 4
10 2006 3
9 2006 2
8 2006 1
7 2006 R
6 2006 N2
5 2006 N1
4 -
3 -

Acad Year 2006/07
Age on Entry Acad Year of Entry Year Group for Age and Year
18 2007 12
17 2007 11
16 2007 10
15 2007 9
14 2007 8
13 2007 7
12 2007 6
11 2007 5
10 2007 4
9 2007 3
8 2007 2
7 2007 1
6 2007 R
5 2007 N2
4 N1
3 -

Acad Year 2007/08
Age on Entry Acad Year of Entry Year Group for Age and Year
18 2008 13
17 2008 12
16 2008 11
15 2008 10
14 2008 9
13 2008 8
12 2008 7
11 2008 6
10 2008 5
9 2008 4
8 2008 3
7 2008 2
6 2008 1
5 2008 R
4 2008 N2
3 2008 N1

And so on for Years N1 -Y13 from Acad Years 1990/91 to 2007/08 and age range
from Reception to 6th Form

The Acad Year of Entry and Age On Entry are derrived from the admission date
and dob using this table
01/09/2007 31/08/2008 2007
01/09/2006 31/08/2007 2006
01/09/2005 31/08/2006 2005
01/09/2004 31/08/2005 2004
01/09/2003 31/08/2004 2003
01/09/2002 31/08/2003 2002
01/09/2001 31/08/2002 2001
01/09/2000 31/08/2001 2000
01/09/1999 31/08/2000 1999
01/09/1998 31/08/1999 1998
01/09/1997 31/08/1998 1997
01/09/1996 31/08/1997 1996
01/09/1995 31/08/1996 1995
01/09/1994 31/08/1995 1994
01/09/1993 31/08/1994 1993
01/09/1992 31/08/1993 1992
01/09/1991 31/08/1992 1991
01/09/1990 31/08/1991 1990

Hope this makes sense and thanks
Greg

 

[Sandy Mann]

Witht he DOB in Column A, the Academic year in Column B, try:

=IF(B15-YEAR((A15-244))-9=0,"R",IF(B15-YEAR((A15-244))-9=-1,"N2",IF(B15-YEAR((A15-244))-9=-2,"N1",IF(B15-YEAR((A15-244))-9<-2,"-",B15-YEAR((A15-244))-9))))

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

 

[Greg]

Sandy & Roger,
Thanks very much for your help, I'm almost there by concantenating the hell
out of columns so I can use vlookup, I will also try it with your suggestion
as it looks a lot more elegent.
Thanks again, much appreciated.
Greg

 

[Sandy Mann]

I should have said that by Academic year I meant like 2003 not 2003/04 if
you want to use ethe latter replace B15 with LEFT(B15,4).

If you want to use the accademic year 2003/04 as a header - say in in B14
then replace the B15 in the formula with: LEFT($B$14,4)

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