Trouble simplifying an IF formula

[Beware Wet Paint]

Hello lovely people,

I'm trying to write an IF formula which returns a certain value if a referenced cell contains any text and a different value if it is blank.

As it stands the only way I've been able to do this is to use a formula which nests each different possible text value the cells could contain- as there are numerous text values the cells could contain the resulting formula is rather long and complex.

I was wondering if there could be a different approach or an alternate function I could use to simplify this situation. Any suggestions are gratefully received, thank you.

 

[s hal]

you could just swap the order of operations, check if its blank first and then do the other thing:

=IF(A1="",something_if_true,something_if_false)

 

[Beware Wet Paint]

Thanks for the suggestion, I'll try it out and let you know how I get on.

 

[Aladin Akyurek]

What are text values you want to check for? And what is the formula you have tried?

 

[Beware Wet Paint]

Sorry for the late reply- the text values are 'Holiday', 'Part', 'Mat leave', 'LTS' and 'SVR', if any of these values appear in the referenced cell I want the cell containing the formula to appear blank, if none of the values appear in the referenced cell I want the formula containing cell to show 'Mr Jones'.

So far the simplest formula I have managed to come up with is =IF(A1="Holiday",""),IF(A1="Part",""),IF(A1="Mat leave",""),IF(A1="LTS",""),IF(A1="SVR","","Mr Jones"))))) I was just wondering if it could be condensed down something like =IF(A1='any text',"","Mr Jones"), it's figuring out if there is an appropriate function for the 'any text' part which I'm struggling with as my knowledge of different functions is pretty limited.

 

[Aladin Akyurek]

B1, copied down:

=IF(LIST,$A1),"","Mr Jones)

where LIST stands for a range housing the text values of interest.

 

[FormR]

Just another possible option:

=IF(OR(A1={"Holiday","Part","Mat leave","LTS","SVR"}),"","Mr Jones")

 

[thisoldman]

Do you really need to check all the conditions? It would be simpler to only check for "Mr Jones" and otherwise leave the cell blank:

=IF(A1="Mr Jones", "SVR", "")

If you truly must check for a text entry, then this would work:

=IF(A1="Mr Jones", "SVR", IF(ISTEXT(A1),""))

Note that it will return FALSE if A1 is blank, a number, or an error.

 

[thisoldman]

Do you really need to check all the conditions? It would be simpler to only check for "Mr Jones" and otherwise leave the cell blank:

=IF(A1="Mr Jones", "SVR", "")

If you truly must check for a text entry, then this would work:

=IF(A1="Mr Jones", "SVR", IF(ISTEXT(A1),""))

Note that it will return FALSE if A1 is blank, a number, or an error.

I misread the question. Sorry for the noise.

 

[Beware Wet Paint]

Thank you for all the replies, I've had success using =IF(ISTEXT(A1),"","Mr Jones") but I will also be trying out the list option as I think =IF(ISTEXT(A1),"","Mr Jones") will become invalid if A1 contains a formula of it's own as I think the ISTEXT function recognizes a formula as text?