Link to home
Start Free TrialLog in
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"))))))))

Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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

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.