Nested IF/AND returning 0 instead of specified blank

[learningdaly]

My formula is returning a 0 when true instead of being blank as specified. If I designate a value other than "blank" (i.e. "A Word") it still returns a 0. What am I doing wrong?


=IF(AND(CBAuditType="PHYSICAL",TxtInsuredName=""),"",TxtInsuredName)


If you need more information to help let me know!

 

[JoeMo]

Are CBAuditType and TxtInsuredName named ranges?

 

[learningdaly]

They are named cells. CBAuditType uses data validation to select from a list. The user can enter any text into the TxtInsuredName cell.

 

[Jonmo1]

Test the cells individually to find the culprait.
What do these return?

=TxtInsuredName=""
=CBAuditType="PHYSICAL"

 

[learningdaly]

Thanks Jonmo1! I don't know why I didn't think of that but it's fixed now!

Thanks for your help everyone!

 

[Jonmo1]

Glad to help.

What exactly was the problem/solution?
Other readers with similar issue might be interested.

 

[JoeMo]

Under conditions where:
CBAuditType<>"PHYSICAL", and TxtInsuredName="" the OPs formula will return a 0 b/c the False condition of his formula returns TxtInsuredName which is a blank cell ("") that Excel reads as zero. One way around the 0 is this:

=IF(AND(CBAuditType="PHYSICAL",TxtInsuredName=""),"",Text(TxtInsuredName, "@"))

Edit: After testing, I find the formula modification I suggested in the line above does not work - the formula still returns a text "0".

 

[JoeMo]

Assuming txtInsuredName will always be a text string (including ""), then this avoids the "0"

=IF(AND(CBAuditType="PHYSICAL",TxtInsuredName=""),"",Text(TxtInsuredName, ""))