Struggling with conditions of IF statements

heathermchle

New Member
Joined
Jun 7, 2022
Messages
15
Office Version
  1. 2010
Platform
  1. Windows
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!

Screenshot 2022-06-07 111000.png
 

heathermchle

New Member
Joined
Jun 7, 2022
Messages
15
Office Version
  1. 2010
Platform
  1. Windows
I noticed a few typos in my version, but I see that you are now using a different criteria. You originally said to use the Cut-Off dates for determining the Fall/Winter/Spring terms, but your latest formula shows that you are using the Start and End dates. Which do you want? The LOOKUP formula used like that will return the last value array position satisfying the conditional criteria...and it would look like this if you want to use Start & End dates rather than the cutoff dates.
Excel Formula:
=LOOKUP(2,1/(($E8>=$C$3:$C$5)*($E8<=$D$3:$D$5)),$B$3:$B$5)
I don't know really know WHICH ones I want, I just keep trying them all in different variations until something works. I really do appreciate you looking a look and helping out.

I've been working on it all day so I've been trying everything! I was following another commenters lead on the LOOKUP function and that was as far as I got. The formula that you gave me pulled up an NA BUT.... with what I have been working on, I've been able to figure it out so far until I get a #DIV/0! error because an inputted start date exceeds the start dates in my tables. I don't really know what to do next to include that information, but otherwise, it works!

Here's the update:
 

Attachments

  • Screenshot 2022-06-07 155828.png
    Screenshot 2022-06-07 155828.png
    63.1 KB · Views: 3

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

heathermchle

New Member
Joined
Jun 7, 2022
Messages
15
Office Version
  1. 2010
Platform
  1. Windows
I noticed a few typos in my version, but I see that you are now using a different criteria. You originally said to use the Cut-Off dates for determining the Fall/Winter/Spring terms, but your latest formula shows that you are using the Start and End dates. Which do you want? The LOOKUP formula used like that will return the last value array position satisfying the conditional criteria...and it would look like this if you want to use Start & End dates rather than the cutoff dates.
Excel Formula:
=LOOKUP(2,1/(($E8>=$C$3:$C$5)*($E8<=$D$3:$D$5)),$B$3:$B$5)
Just kidding! I realized some of the cell references were off and I fixed them and now it works with the formula that you gave me! OMG. You saved my day and my sanity. Thank you!
 

heathermchle

New Member
Joined
Jun 7, 2022
Messages
15
Office Version
  1. 2010
Platform
  1. Windows
I noticed a few typos in my version, but I see that you are now using a different criteria. You originally said to use the Cut-Off dates for determining the Fall/Winter/Spring terms, but your latest formula shows that you are using the Start and End dates. Which do you want? The LOOKUP formula used like that will return the last value array position satisfying the conditional criteria...and it would look like this if you want to use Start & End dates rather than the cutoff dates.
Excel Formula:
=LOOKUP(2,1/(($E8>=$C$3:$C$5)*($E8<=$D$3:$D$5)),$B$3:$B$5)
One last question, since it is working in Excel, I have to upload this to Google Drive for my teachers to be able to access their own version of it, but the formulas turn up NA in Google Sheets? Is this a lost cause?? Anyone know a quick fix for this one?
 

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
2,232
Office Version
  1. 365
Platform
  1. Windows
Which formula is producing the NA error? Did you settle on using the LOOKUP formula or the IF/LARGE ...and are you using Start & End dates instead of cutoff dates?
 

heathermchle

New Member
Joined
Jun 7, 2022
Messages
15
Office Version
  1. 2010
Platform
  1. Windows
Which formula is producing the NA error? Did you settle on using the LOOKUP formula or the IF/LARGE ...and are you using Start & End dates instead of cutoff dates?
I used the LOOKUP and Start & End Date times, from the formula you gave me. It works perfectly in Excel now but I'll need it to work in Google Sheets, too, and it doesn't.
 

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
2,232
Office Version
  1. 365
Platform
  1. Windows
I uploaded my mock up file to Sheets and didn’t see any issues. Do you want to post a link to a non-sensitive version of the file?
 

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
2,232
Office Version
  1. 365
Platform
  1. Windows
Enter this for the LOOKUP formula in H9 and drag down...Sheets requires some extra attention where arrays are concerned:
=ARRAY_CONSTRAIN(ARRAYFORMULA(LOOKUP(2,1/(($E9>=$C$4:$C$6)*($E9<=$D$4:$D$6)),$B$4:$B$6)), 1, 1)
 

heathermchle

New Member
Joined
Jun 7, 2022
Messages
15
Office Version
  1. 2010
Platform
  1. Windows
It worked! You're a genius! Thank you so much for your help! I won't even tell you how much time I've actually spent trying to get this to work, I should have come here sooner. Thank you so so so much.
 

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
2,232
Office Version
  1. 365
Platform
  1. Windows
You’re welcome…I’m happy to help.
 

Forum statistics

Threads
1,176,678
Messages
5,904,411
Members
435,090
Latest member
zero20

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top