Multi-Conditional Formula in Excel

[lttardif73]

Hello,

Appreciate any pointers/guidance why my multi-conditional formula in excel is not working in the below example.
Criteria
a. GA < today < EOL : Available, supported
b. EOL < today < EOSL : Not available, supported
c. Today >= EOSL : Not available, not supported

Sample data set
B1 = Today's Date = 2/23/2016
B4= GA = 4/1/2011
E4 = EOL = 1/19/2015
G4 = EOSL = 6/30/2020

Multi-Conditional Formula I created:-
=IF(AND(B4<$B$1<F4),"Available, supported",IF(AND(E4<$B$1<G4),"Not avaliable, supported",IF(AND($B$1>=G4),"Not available, not supported","ERROR")))

Result = ERROR (which is incorrect). Using the formula with the sample data set, my correct result should be 'Not available, supported'.

Can anyone share where my formula logic is not working? I've tried office support, your Excel Questions forum, google, bing, etc and still failing.

I greatly appreciate any help. Thanks much in advance!

Respectfully,
Excel Learner

 

[lttardif73]

Apologies, here's the Multi-Conditional Formula I created:-
=IF(AND(B4<$B$1<F4),"Available, supported",IF(AND(E4<$B$1<G4),"Not avaliable, supported",IF(AND($B$1>=G4),"Not available, not supported","ERROR")))

 

[jtakw]

Hi,

Maybe you mean this:

=IF(AND(B4<$B$1,B4< G4),"Not available, not supported","ERROR")

if you mean B4 is smaller than B1 and smaller than G4.

Otherwise, let us know what you mean.

 

[Eric W]

This might work for you:

=IF(B1 < B4,"ERROR",IF(B1 < E4,"Available, supported",IF(B1 < G4,"Not available, supported","Not available, not supported")))

This assumes the B4, E4, G4 dates are in ascending order.

One other note: the bulletin board software here interprets a < as an HTML tag. In order to make it print, and not lose the second half of your formula, you need to put spaces before and after any < in your formula.

Hope this helps!

 

[jtakw]

Hi,

Yeah, just realized your formula got cut off, but from what I gather, this is what you're looking for, let us know if that's what you need. Don't know what's in F4 (since you didn't explain in your post), so can't test it.

=IF(AND(B4< $B$1,B4< F4),"Available, supported",IF(OR(AND(E4< $B$1,E4< G4),$B$1>=G4),"Not avaliable, supported","ERROR"))

 

[jtakw]

Hi,

Sorry, ignore my last post, this should be the correct version:

=IF(AND(B4<$B$1,B4< F4),"Available, supported",IF(AND(E4<$B$1,E4< G4),"Not avaliable, supported",IF($B$1>=G4,"Not available, not supported","ERROR")))

 

[lttardif73]

Hello Eric,

Firstly, thanks much for the prompt response. Your formula helped! Secondly, I finally realized why my formula was not fully copied onto my original forum thread and your explanation in your thread is good insight on my next forum post.

My bad on F4, It was typed in error. It should reference E4. The formula you provided worked on 80% of my data set. The issue is the first criteria (I believe).
a. GA < today < EOL : Available, supported
Which you had broken out the logic in 2-folds.