Nested IF Function

Hi Champs,

See below formula that i have updated in a cell.

Formula:

Please Login or Register  to view this content.

If I updated any other character like an Alphabet or Symbol in L6, it still gives me one of the true values in the formula.
What i am expecting is that, if a user updates an Alphabet or Symbol in L6, the formula should populate "ERROR"

Can you please help.

Aaron

try this.

Please Login or Register  to view this content.

JimBobBowie

Try

=IFERROR(CHOOSE(SIGN(L6)+2,"Debit NLPX-VAA - Credit NLP MAIN","No wire","Debit NLP MAIN - Credit NLPX-VAA",2,3),"Error")

Hi Jonmo1 - Your formula worked like a charm and all the if conditions are met. Would you please help me understand the formula though, specially this part

Please Login or Register  to view this content.

. Does it take the numbers as a sequence?

Hi JimBobBowie - Your formula worked for an alphabet. But if the cell is blank or 0, it would still show as Error instead of showing as No Wire

The SIGN function returns the SIGN of a given number.
If the number is Negative (<0) it returns -1
If the number is 0, it returns 0
If the number is Positive (>0) it returns 1
If it's not a number at all, it returns #Value! (that's where IFERROR comes in)

The +2 changes the result of SIGN so it returns 1 2 or 3 instead of -1 0 or 1

Then CHOOSE Chooses an option based on the number provided
CHOOSE(number,Option1,Option2,Option3,etc..)

I pretty much tried just the Sign and Choose function independently to understand it further. Thanks for your help.

You're welcome.