Struggling with conditions of IF statements

[heathermchle]

Hi, I'm working on an interactive spread sheet to use with the teachers that I support and I'm trying to find a way to make the cells in one column display certain text based on the conditions in another. I've literally spent the equivalent of two work days, it feels like, playing with formulas, trying different things. I KNOW there is a way but I just can't figure it out! Spreadsheet is below:

The user will input data from B8 and G8 ( I will likely move G next to B so the rest of the table populates from there.

In Column H, starting at row 8, I need an IF statement that plays on the conditions in row G8. So...

If G8 is in between I1 and G3, the text says Fall.
If G8 is greater than G3 but less than G4, it says Winter.
If G8 is greater than G4, but less than G5, it says Spring.

I1, and G3:G5 are fixed cells.

Column K works and calculates correctly based on this text (from a method I had previously tried, I decided to leave it there since I hadn't broken it yet!)

Once the text shows up in Column H correctly, I will conditionally format it to 3 different colors.

I just can't figure out how to get the date conditions to show properly. I've tried =IF( and also =IF(AND... all kinds of ways.

Someone please help!

 

[heathermchle]

You’re welcome…I’m happy to help.

Do you think you could take a look at the Google Doc file again and help me with one last thing? I populated some dates and applied the formulas down to row 24 so that it can be filled out by teachers as they enroll new children. However, I don't want any of the boxes to be filled if columns D and E do not have input. I've played with various conditional formatting options, that covered some of the 1900 dates and negative numbers and stuff but I have since removed it so I could visualize all the columns that are populating without input. So, I know my options there but just curious if there might be an easier work around. I was able to come up with a solution in Columns I and F to leave the cell blank if the input cell is blank (=IF(E18="",".... yada, yada) but I don't know how to apply that to some of the more complicated formulas that I have. And, also, don't worry.... these columns will be protected from staff messing with them! Our hard work won't be wasted.

 

[KRice]

Sure, I’d be happy to have a look. I’m away from my computer at the moment, but will look when I return. Is the same link still valid?

 

[heathermchle]

Sure, I’d be happy to have a look. I’m away from my computer at the moment, but will look when I return. Is the same link still valid?

Yes, it is

 

[KRice]

Here are my thoughts on the formulas and suggestions for dealing with errors. I'll refer to the top-level formulas that appear on row 9. Please let me know if you have any questions.

F9 and down:

=IF(D9="","",EDATE(D9,36))

...looks fine. The IF statement checking for a blank or empty cell in D9 (the DOB) is a good way to avoid having EDATE add 36 months to Excel's "0" date, which is 1/1/1900 or 1/1/1904 depending on computer systems/version of Excel.

G9 and down:
=IF(F9<TODAY(),"Preschool Comprehensive View","Infant Toddler Comprehensive View")
...look fine, assuming you are okay with this cell changing when a toddler turns 3 years old. Both TRUE/FALSE conditions are shown, so you should always get one of these, even if there is no student shown. If you want to trap that condition, you could take a similar approach to the D9 formula, and rather than daisy-chaining IF results (where a date in D9 must exist for F9 to yield an answer and we could reference the presence of a value in F9 for this formula, but I would go further back to the source...D9)...so:

=IF(D9="","",IF(F9<TODAY(),"Preschool Comprehensive View","Infant Toddler Comprehensive View"))

H9 and down:
When there is nothing for LOOKUP to search through, an error will be generated. To trap the error, the easiest fix is to wrap it with an IFERROR function and return a blank when an error is encountered.

=IFERROR(ARRAY_CONSTRAIN(ARRAYFORMULA(LOOKUP(2,1/(($E9>=$C$4:$C$6)*($E9<=$D$4:$D$6)),$B$4:$B$6)), 1, 1),"")

I9 and down:
The triple-nested IF formula works fine, with one exception, but I would probably use a different idea using the MATCH function. The MATCH function is designed to search for the 1st argument (H9 in this case) in the 2nd argument (where normally we would reference a range in the spreadsheet, but in this case, we can hardwire in an array list with the curly brackets). The 3rd argument (0) says we need an exact match. The function returns the position where the match is found, so 1 means Spring, 2 Winter, and 3 Fall. Should MATCH not find a match, then an error will be generated. To trap that error and clean up the display, an IFERROR wrapping would be the easiest:

=IFERROR(MATCH(H9,{"Spring","Winter","Fall"},0),"")

If you stick with the triple-nested IF formula, an error will be generated if all of the nested IFs evaluate to FALSE because one thing is missing. The current version:
=IF(H9="Fall","3", IF(H9="Winter","2",IF(H9="Spring","1")))
Compared to this one where a comma and empty quotes are tacked onto the last IF. Then if all evaluate to FALSE, this will insert a blank and you won't get an error.
=IF(H9="Fall","3", IF(H9="Winter","2",IF(H9="Spring","1","")))

J9 and down:

=IF($E9="","",EDATE(E9, 2))

...much like the F9 formula, this looks fine.

K9 and down:
=DAYS360(TODAY(),J9)
In the event J9 has no value, this will produce an odd-looking negative number because the blank J9 will be interpreted as 0. Since J9 depends on a user input in E9, again I would go back to that source and apply an error trapping IF statement like that used in J9:

=IF($E9="","",DAYS360(TODAY(),J9))

 

[heathermchle]

Here are my thoughts on the formulas and suggestions for dealing with errors. I'll refer to the top-level formulas that appear on row 9. Please let me know if you have any questions.

F9 and down:

=IF(D9="","",EDATE(D9,36))

...looks fine. The IF statement checking for a blank or empty cell in D9 (the DOB) is a good way to avoid having EDATE add 36 months to Excel's "0" date, which is 1/1/1900 or 1/1/1904 depending on computer systems/version of Excel.

G9 and down:
=IF(F9<TODAY(),"Preschool Comprehensive View","Infant Toddler Comprehensive View")
...look fine, assuming you are okay with this cell changing when a toddler turns 3 years old. Both TRUE/FALSE conditions are shown, so you should always get one of these, even if there is no student shown. If you want to trap that condition, you could take a similar approach to the D9 formula, and rather than daisy-chaining IF results (where a date in D9 must exist for F9 to yield an answer and we could reference the presence of a value in F9 for this formula, but I would go further back to the source...D9)...so:

=IF(D9="","",IF(F9<TODAY(),"Preschool Comprehensive View","Infant Toddler Comprehensive View"))

H9 and down:
When there is nothing for LOOKUP to search through, an error will be generated. To trap the error, the easiest fix is to wrap it with an IFERROR function and return a blank when an error is encountered.

=IFERROR(ARRAY_CONSTRAIN(ARRAYFORMULA(LOOKUP(2,1/(($E9>=$C$4:$C$6)*($E9<=$D$4:$D$6)),$B$4:$B$6)), 1, 1),"")

I9 and down:
The triple-nested IF formula works fine, with one exception, but I would probably use a different idea using the MATCH function. The MATCH function is designed to search for the 1st argument (H9 in this case) in the 2nd argument (where normally we would reference a range in the spreadsheet, but in this case, we can hardwire in an array list with the curly brackets). The 3rd argument (0) says we need an exact match. The function returns the position where the match is found, so 1 means Spring, 2 Winter, and 3 Fall. Should MATCH not find a match, then an error will be generated. To trap that error and clean up the display, an IFERROR wrapping would be the easiest:

=IFERROR(MATCH(H9,{"Spring","Winter","Fall"},0),"")

If you stick with the triple-nested IF formula, an error will be generated if all of the nested IFs evaluate to FALSE because one thing is missing. The current version:
=IF(H9="Fall","3", IF(H9="Winter","2",IF(H9="Spring","1")))
Compared to this one where a comma and empty quotes are tacked onto the last IF. Then if all evaluate to FALSE, this will insert a blank and you won't get an error.
=IF(H9="Fall","3", IF(H9="Winter","2",IF(H9="Spring","1","")))

J9 and down:

=IF($E9="","",EDATE(E9, 2))

...much like the F9 formula, this looks fine.

K9 and down:
=DAYS360(TODAY(),J9)
In the event J9 has no value, this will produce an odd-looking negative number because the blank J9 will be interpreted as 0. Since J9 depends on a user input in E9, again I would go back to that source and apply an error trapping IF statement like that used in J9:

=IF($E9="","",DAYS360(TODAY(),J9))

These ALL worked beautifully! I just tried replacing each one and everything worked exactly as I'd hoped for. I appreciate your help SOOOO much. Thank you, thank you!

 

[KRice]

That's good to hear. I'm happy to help.