Quizzes & Puzzles1 min ago
Excel IF rules and Dependants
7 Answers
I want a cell to display a 'Y' or an 'N' in a column, dependent on what another two cells contain (one a birth date, another a term type).
I have the first part thanks to some help on here last Friday afternoon, and have the following formula:
=IF(B2<37865,"N",IF(B2>38595,"N","Y"))
...which basically says 'if a child is born between 1st September 2003 and the 31st August 2008, they are ['Y] eligible. If not, they are ['N'] not.
These dates are relevant to the AUTUMN TERM 2008.
NOW, I want these dates to change, dependent on what another cell says later in the spreadsheet. For example, if I am then talking about the SPRING TERM 2009, the dates need to be shifted. The sam principal applies but the dates need to change.
I have started it, hopefully...something like this seems on the right track?!:
IF((X2 "Autumn 2008")B2<37865,"N",IF(B2>38595,"N","Y")) - X2 is the cell with 'Autumn Term 2008' in. Alternatively it will be Spring Term 2009 or Summer Term 2009, so will need multiple coding putting in...
I have the first part thanks to some help on here last Friday afternoon, and have the following formula:
=IF(B2<37865,"N",IF(B2>38595,"N","Y"))
...which basically says 'if a child is born between 1st September 2003 and the 31st August 2008, they are ['Y] eligible. If not, they are ['N'] not.
These dates are relevant to the AUTUMN TERM 2008.
NOW, I want these dates to change, dependent on what another cell says later in the spreadsheet. For example, if I am then talking about the SPRING TERM 2009, the dates need to be shifted. The sam principal applies but the dates need to change.
I have started it, hopefully...something like this seems on the right track?!:
IF((X2 "Autumn 2008")B2<37865,"N",IF(B2>38595,"N","Y")) - X2 is the cell with 'Autumn Term 2008' in. Alternatively it will be Spring Term 2009 or Summer Term 2009, so will need multiple coding putting in...
Answers
1 to 7 of 7
Best Answer
No best answer has yet been selected by funkylad20. Once a best answer has been selected, it will be shown here.
For more on marking an answer as the "Best Answer", please visit our FAQ.
Taking into account your previous post...
http://www.theanswerbank.co.uk/Technology/Comp uters/Question607276.html
... to keep thing simple I would suggest that when you change cell X2 you set up two cells to indicate the Term start and end dates which can then be referenced by your age eligibility formula.
e.g.
Term start date cell (X3 or whatever)
=IF(R12="Autumn 2008",DATE(2003,9,1),IF(R12="Spring 2009",DATE(2004,1,1),IF(R12="Summer 2009",DATE(2004,5,1),"")))
(NB no spaces in formula except for Term descriptions in quotes)
Term end date cell (X4 or whatever)
=IF(R12="Autumn 2008",DATE(2008,8,31),IF(R12="Spring 2009",DATE(2008,12,31),IF(R12="Summer 2009",DATE(2009,4,31),"")))
then your age eligibilty formula can be simplified to
=IF(OR(B2<X3,B2>X4),"N","Y")
The use of DATE(y,m,d) removes your datecode complication.
http://www.theanswerbank.co.uk/Technology/Comp uters/Question607276.html
... to keep thing simple I would suggest that when you change cell X2 you set up two cells to indicate the Term start and end dates which can then be referenced by your age eligibility formula.
e.g.
Term start date cell (X3 or whatever)
=IF(R12="Autumn 2008",DATE(2003,9,1),IF(R12="Spring 2009",DATE(2004,1,1),IF(R12="Summer 2009",DATE(2004,5,1),"")))
(NB no spaces in formula except for Term descriptions in quotes)
Term end date cell (X4 or whatever)
=IF(R12="Autumn 2008",DATE(2008,8,31),IF(R12="Spring 2009",DATE(2008,12,31),IF(R12="Summer 2009",DATE(2009,4,31),"")))
then your age eligibilty formula can be simplified to
=IF(OR(B2<X3,B2>X4),"N","Y")
The use of DATE(y,m,d) removes your datecode complication.
Kempie, thank you! Works a treat.
One other thing - not a necessity, but if the cell with the DOB (B2 in your formula) is left blank, how do I make the other cell blank (with the 'age eligibility formula' in it) rather than a 'Y' or a 'N'?
Problem is, multiple following cells now contain 'Y' even though no record/DOB is entered...
One other thing - not a necessity, but if the cell with the DOB (B2 in your formula) is left blank, how do I make the other cell blank (with the 'age eligibility formula' in it) rather than a 'Y' or a 'N'?
Problem is, multiple following cells now contain 'Y' even though no record/DOB is entered...
Perfect, many thanks!
Does that work for any cell then? I often wonder if there is a way, when entering ongoing data over a period of time, rather than just dragging the formulas downwards through the spreadsheet creating #VALUE! results that it simply displays as a blank cell...when mailmerging, it always treats the #VALUE! etc as entries and merges for thousands of pages!!
Does that work for any cell then? I often wonder if there is a way, when entering ongoing data over a period of time, rather than just dragging the formulas downwards through the spreadsheet creating #VALUE! results that it simply displays as a blank cell...when mailmerging, it always treats the #VALUE! etc as entries and merges for thousands of pages!!
1 to 7 of 7
Related Questions
Sorry, we can't find any related questions. Try using the search bar at the top of the page to search for some keywords, or choose a topic and submit your own question.