Avatar of Software Engineer
Software Engineer
 asked on

Excel: Nested "If, Then" Statement

Excel is saying that there is an issue with my nested "if, then" statement below.  Would someone please tell me what the issue is?  Thanks!

=IF(LEFT([@[Shortcut Dimension 2 Code]],1)="4", "40", IF(LEFT([@[Shortcut Dimension 2 Code]],1)="3", "30", IF(LEFT([@[Shortcut Dimension 2 Code]],1)="2", "20", IF(LEFT([@[Shortcut Dimension 2 Code]],1)="1", "10"))))))))

Microsoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Patrick Matthews

8/22/2022 - Mon
Patrick Matthews

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Patrick Matthews

BTW, if you simply want this to be "take the first character, treat it as a number, and multiply by ten", this becomes really simple:

=10*LEFT([@[Shortcut Dimension 2 Code]],1)

Open in new window

Patrick Matthews

You could also go with something like this:

=CHOOSE(LEFT([@[Shortcut Dimension 2 Code]],1),10,20,30,40)

Open in new window

Use IFERROR, if desired, to handle the error condition when the leftmost character falls out of the range.

Finally, depending on your Excel version, consider using the new function IFS() instead of embedding your IF statements. In my opinion it's easier to read.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy