Nested IF AND OR Statement Working Some Line, Others Not At All

[brenda1996]

Hi all!

Wondering if anyone can help me answer why a formula I created works on some lines but will not on others with identical logical tests.

I am trying my best to learn Excel's more complicated formulas and can't get past this one that makes no sense why not working on other lines.

The formula is =IF(AND(C2=365,OR(AND(D2="MON"),OR(D2="YR"),OR(D2=""))),"SHOULD BE DAY","").

For instance Cell C2 has 365 and Cell D2 has MON or YR and returns a value of "SHOULD BE DAY" which is what I need.

Some lines where is formula is placed work and on others it does not.

Other cells with same logical tests and same values (365 and MON or YR) come up blank, I believe because the ELSE return is set for "".

Conditional formatting is not an issue, also all cells are formatted exactly alike (Number for Column C and General for Column D and CTRL-SHIFT-ENTER for array does not work.

I am puzzled. Could there be an issue with the formula?

Thanks in advance for your help, it is sincerely appreciated.

Brenda

 

[jtakw]

Hi,

Let's fix the formula first, then see if there may be problems with your data.
Based on your Written description, your formula should be as follows:

Book3.xlsx
	C	D	E	F
1			Shorter way	Standard way
2	365	YR	SHOULD BE DAY	SHOULD BE DAY
3	365	YR	SHOULD BE DAY	SHOULD BE DAY
4	365	MON	SHOULD BE DAY	SHOULD BE DAY
5		MON
6	365	MON	SHOULD BE DAY	SHOULD BE DAY
7	365	MON	SHOULD BE DAY	SHOULD BE DAY
8	365	MON	SHOULD BE DAY	SHOULD BE DAY
9	365
10	365	YR	SHOULD BE DAY	SHOULD BE DAY
11	365	MON	SHOULD BE DAY	SHOULD BE DAY
Sheet992

Cell Formulas
Range	Formula
E2:E11	E2	=IF(AND(C2=365,OR(D2={"MON","YR"})),"SHOULD BE DAY","")
F2:F11	F2	=IF(AND(C2=365,OR(D2="MON",D2="YR")),"SHOULD BE DAY","")

 

[brenda1996]

Hi jkakw and for your help.

I opted for the standard formula copied down to bottom of spreadsheet and got the exact same result as in using my formula.

I tried both of your formulas and got the same result for both which was the exact same as my screwed up formula.

Any ideas?

Brenda

 

[brenda1996]

Hi again jkakw!

I think I fixed it. When you mentioned data problem that reminded me of the CLEAN function.

I cleaned numbers and that wiped out ALL results.

I cleaned the DAY, MON , YR data and still nothing, everything now blank.

I changed the formula and put quotes around the 365 and it seems to be working!

Yay!

Brenda

 

[jtakw]

Hi again jkakw!

I think I fixed it.

I changed the formula and put quotes around the 365 and it seems to be working!

Yay!

Brenda

If you had to put quotes around 365, like "365" in the formula, that means that value is Text, Not a number.
Glad you worked it out, but curious, how is that "365" entered into the cell, by formula, or manually entered.
If by formula you should fix That formula to produce a real number, for example:

=IF(this,that,"365") Should be
=IF(this,that,365)

Any number surrounded by quotes turns into Text.

 

[brenda1996]

If you had to put quotes around 365, like "365" in the formula, that means that value is Text, Not a number.
Glad you worked it out, but curious, how is that "365" entered into the cell, by formula, or manually entered.
If by formula you should fix That formula to produce a real number, for example:

=IF(this,that,"365") Should be
=IF(this,that,365)

Any number surrounded by quotes turns into Text.

Hi again jtakw!

To answer your question it looks like the data was a mix of typed in and data gained from another source thus forcing the CLEAN.

I used the VALUE function and converted all what should be numerical data as you mentioned to numbers which is what I needed and your formula works perfectly.

I have been able to get rid of the "" marks since they now numbers.

Now working on extending that formula into =IF(AND(C3<>365,OR(D3="DAY",D3="")),"SHOULD BE MON or YR","") for another piece of the puzzle.

It is working except if Column C3 is blank I get a return of SHOULD BE MON or YR which needs to be blank so as not to be counted.

Again I am missing a simple step or placement code. I have experimented with added C3="" with no success. I wish I understood or new Excel formulas better.

Thank you and enjoy your Sunday!

Brenda

 

[jtakw]

Thanks for posting back.

Regarding your latest requirement, if I understand your logic correctly, see if this works for you:

Book3.xlsx
	C	D	E
1
2	365	YR	SHOULD BE DAY
3
4		DAY
5	365	MON	SHOULD BE DAY
6	30		SHOULD BE MON or YR
7	365	MON	SHOULD BE DAY
8	30	DAY	SHOULD BE MON or YR
9	365	MON	SHOULD BE DAY
10	365
11	365	YR	SHOULD BE DAY
12	365	MON	SHOULD BE DAY
Sheet992

Cell Formulas
Range	Formula
E2:E12	E2	=IF(C2="","",IF(C2=365,IF(OR(D2="MON",D2="YR"),"SHOULD BE DAY",""),"SHOULD BE MON or YR"))

 

[brenda1996]

Hi again jtakw!

To answer your question it looks like the data was a mix of typed in and data gained from another source thus forcing the CLEAN.

I used the VALUE function and converted all what should be numerical data as you mentioned to numbers which is what I needed and your formula works perfectly.

I have been able to get rid of the "" marks since they now numbers.

Now working on extending that formula into =IF(AND(C3<>365,OR(D3="DAY",D3="")),"SHOULD BE MON or YR","") for another piece of the puzzle.

It is working except if Column C3 is blank I get a return of SHOULD BE MON or YR which needs to be blank so as not to be counted.

Again I am missing a simple step or placement code. I have experimented with added C3="" with no success. I wish I understood or new Excel formulas better.

Thank you and enjoy your Sunday!

Brenda

Got it. Yaaaaaaaaaaaaaay! Thank you for all of your help! I sincerely appreciate you taking time out of your day top help me yesterday.

 

[jtakw]

You're very welcome, thanks for the feedback.