Tool to help write Excel formulas and IF statements?

Question

This is an Excel formula with nested IF statements:

=IF((B2="East"),4,IF((B2="West"),3,IF((B2="North"),2,IF((B2="South"),1,""))))

To essentially accomplish this:

If cell B2 = "East"
   return "4"

ElseIf cell B2 = "West"
   return "3"

ElseIf cell B2 = "North"
   return "2"

ElseIf cell B2 = "South"
   return "1"

Else
   return ""

Can Excel formulas be written in such a "more readable" manner and converted to the official syntax? Is there any tool to help write Excel formulas?

This may be a "superuser" question ... but only programmers might know the answer!

Solution 1

Excel Formula Formatter add-in by Rob van Gelder, mentioned at Daily Dose of Excel.

Excel's formula bar ignores line feeds and white space, so you can Alt+Enter and spacebar to format the formulas however you like. I've tried it and I quickly stopped doing it. Too much spacebar-ing, especially if you need to edit.

Solution 2

Assuming you don't have a lookup table you can do an index match with an array.

In the index function you create an array with the values you want. Then instead of entering a specific row as the next argument of the function, you add the match function.

In the Match function you specify the value you want to find (In this case we want to find the value in B2) and then the array you want to search in. We then use the zero argument to tell the match statement to use an exact match.

As long as the two arrays are in the same order, you will get the value from the first array in the same location as the found value in the second array.

=INDEX({2,1,4,3},MATCH(B2,{"North","South","East","West"},0))

Solution 3

One option would be to use VBA to create a User Defined Function.

You'd add a new Module to the Workbook and then put in some VB that looked something like this:

Function DirectionAsInt(Direction)
    Select Case (Direction):
        Case "East":
            DirectionAsInt = 4
        Case "West":
            DirectionAsInt = 3
        Case "North":
            DirectionAsInt = 2
        Case "South":
            DirectionAsInt = 1
    End Select
End Function

Then in your cell you could put:

=DirectionAsInt(B2)

Solution 4

Is there any tool to help write Excel formulas?

I don't think there is, I'm afraid.

In the specific example given, I'd be inclined to do the following:

1. Create a new Name, ccall it something like CompassPoints and set its value to ={"South";"North";"West";"East"}
2. Now your formula becomes =MATCH(CompassPoints, B2, 0)

View more solutions