 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / February 2008Return value based on range of cells
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 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?  SignatureRegards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk > Hi, > Can anyone suggest how to return a value based upon how old clients were [quoted text clipped - 11 lines] > Thanks for any advise > 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 > Care to give a bit more detail? Are you working with Dates of Birth? > [quoted text clipped - 20 lines] > > Thanks for any advise > > Greg 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. SignatureHTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk > Thanks for the quick reply Sandy, > It is working with dates of birth but these are converted to integers. [quoted text clipped - 31 lines] >> > Thanks for any advise >> > Greg 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. SignatureRegards Roger Govier > I may be still not understanding your needs but would: > [quoted text clipped - 47 lines] >>> > Thanks for any advise >>> > Greg > 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. SignatureRegards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk > Hi Sandy > [quoted text clipped - 72 lines] >>>> > Thanks for any advise >>>> > 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 > Hi Sandy > [quoted text clipped - 68 lines] > >>> > Thanks for any advise > >>> > Greg 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)))) SignatureHTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk > 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. [quoted text clipped - 216 lines] >> >>> > Thanks for any advise >> >>> > 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 :) > Witht he DOB in Column A, the Academic year in Column B, try: > [quoted text clipped - 220 lines] > >> >>> > Thanks for any advise > >> >>> > Greg 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) SignatureHTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk > Sandy & Roger, > Thanks very much for your help, I'm almost there by concantenating the [quoted text clipped - 246 lines] >> >> >>> > Thanks for any advise >> >> >>> > Greg |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.