A tool to help you write Excel formulas and IF statements?

Question

This is an Excel formula with nested IF expressions:

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

To 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 ""

Is it possible to write Excel formulas in such a "more readable" way and convert it to official syntax? Is there any tool for writing Excel formulas?

It may be a superuser question ... but only programmers can know the answer!

Answer 1

Rob van Gelder's Excel Formula Formatter extension , mentioned in the Daily Dose of Excel.

Excel , Alt + Enter , . , . , .

Answer 2

- VBA .

, VB, :

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

:

=DirectionAsInt(B2)

Answer 3

- Excel ?

, , .

:

• , ccall - CompassPoints ={"South";"North";"West";"East"}
• =MATCH(CompassPoints, B2, 0)

Answer 4

When I did this, before I used my favorite text editor and replaced it. Not a great solution, but it works. Alternatively, you can make this a vb function that is a bit readable. The drawback of doign is the increase in runtime and the need to include macros.

In your example, you can also just create a lookup table on another sheet or in some hidden cells.