Excel: Rearrange Equations

Any student of math will need to rearrange an equation to isolate a variable of interest. While it’s possible to do this in Excel, it isn’t as intuitive as it could be. Your formulas contain cell references instead of meaningful names (e.g. “A1” instead of “Inflation_Rate”). You can use meaningful names (via Named Ranges) but it’s time consuming to label each cell.

What if you could just type in the formula, and Excel could split out the variables for you. You could then enter values for each variable. At this point, you’ve got meaningful variables to work with. But if you also need to algebraically move terms around, wouldn’t it be nice to see the sequence of steps you applied? That is, both the formulas and the values.

I’ve made a template in Excel for just this purpose. Here’s a screenshot:

Template

Looking at the image above…

• Row 2: The formula is typed in.
• Row 4: The formula is split by the equal sign.
• Rows 8…16: Variables are created for both sides of the equation
• A button exists to add these variables as Named Ranges. If clicked, you can start using the variable names in your equations.
• Rows 19…21: Shows the algebraic steps being applied, both the formula and the values at each stage.
• A button exists to add the initial two rows at the top of this table. Both contain the same formula. The first is your reference point. The second is to apply your initial algebraic change.

How is this Built?

Fair-warning — I slapped this together. It works but could be improved. Below I describe how to create each section…

Sections: Sides of the Equation

=INDEX(TEXTSPLIT(B2,”=”),1)
=INDEX(TEXTSPLIT(B2,”=”),2)

The code above splits the formula by the equal sign and keeps either the left or the right.

Section: List of Terms

=LET(
  each_item, TEXTSPLIT(B5, " "),
  blank_out_non_words, MAP(each_item, LAMBDA(value, LET(first_character, LOWER(LEFT(value, 1)), IF(AND(first_character >= "a", first_character <= "z"), value, "")))),
  blank_out_functions,MAP(blank_out_non_words,LAMBDA(value, IF(NOT(ISNUMBER(FIND("(",value))), value, ""))),
  retain_non_blanks,TEXTJOIN(" ",TRUE,blank_out_functions), keep_words, TEXTSPLIT(retain_non_blanks, " "),
  TRANSPOSE(keep_words)
)

The code above is bulky. To help with readability, I like to use LET to itemize each step. Let’s break it down…

Split everything by a space

each_item, TEXTSPLIT(B5, “ “)

Examine each item to identify variables. Keep items that start with a letter, otherwise blank it out.

blank_out_non_words, MAP(each_item, 
  LAMBDA(value, 
    LET(
      first_character, LOWER(LEFT(value, 1)), 
      IF(
         AND(first_character >= “a”, first_character <= “z”), 
         value, 
         “”
      )
    )
  )
)

Similar to the above, but to remove functions.

Note: Given that earlier we split using a space, for this to work, there can’t be a space between the function name and the left parenthesis, e.g. “func(…” and not “func (…)”.

blank_out_functions, MAP(blank_out_non_words,
  LAMBDA(value, 
    IF(NOT(ISNUMBER(FIND("(",value))), value, "")
  )
)

Filter out the blanks by joining everything except blanks using a space.

retain_non_blanks, TEXTJOIN(“ “, TRUE, blank_out_functions)

Split everything again using a space.

keep_words, TEXTSPLIT(retain_non_blanks, “ “)

Finally transpose it so items go down a list.

TRANSPOSE(keep_words)

Phew…

Section: Equation

If there is a formula, display it without the preceding equal sign. Replace “<<EQUATION_CELL>>” with the cell that has the equation.

=LET(
  formula, FORMULATEXT(<<EQUATION_CELL>>), 
  IF(NOT(ISNA(formula)), SUBSTITUTE(formula,"=", ""), "")
)

Section: Button “Terms => Named Ranges”

This is a general purpose function to create a Named Range given the left-most heading cell of data.

It expands the selection around the heading. If there are less than 2 rows, it means there is no data, so quit. If there is data… it adds a named range for each row.

Sub CreateNamedRanges(cell)
    Dim rng As Variant
    Set rng = Range(cell).CurrentRegion
    
    Dim iRow As Integer
    iRow = rng.CurrentRegion.Rows.Count
    
    If iRow < 2 Then Exit Sub
    For i = 2 To iRow
        ActiveWorkbook.Names.Add _
            Name:=rng.Cells(i, 1).Value, _
            RefersTo:="=Template!" + rng.Cells(i, 2).address
    Next i
End Sub

Create named ranges for both sides of the equation by passing the left-most heading cell.

Sub CreateTerms()
    CreateNamedRanges ("B7")
    CreateNamedRanges ("E7")
End Sub

Section: Button “Clear Values” (Optional)

This button just clears out any value set for the terms. Handy if you want to quickly work on a new problem.

Sub ClearValues()
    Range("C8:C16").ClearContents
    Range("F8:F16").ClearContents
End Sub

Section: Button “Create Left and Right Equations”

This code block clears any equations from the “Value” column. It adds equal signs — just in case they were deleted. Finally, it adds the left and right equations as formulas, twice.

Sub CreateLeftAndRightEquations()
    Range("C19:C37").ClearContents
    Range("E19:E37").ClearContents
    
    Range("D19:D37").Value = "="
    Dim leftEquation, rightEquation As Variant
    leftEquation = Range("B5").Value
    rightEquation = Range("E5").Value
    Range("C19").FormulaR1C1 = "=" + leftEquation
    Range("E19").FormulaR1C1 = "=" + rightEquation
    Range("C20").FormulaR1C1 = "=" + leftEquation
    Range("E20").FormulaR1C1 = "=" + rightEquation
End Sub

Um, Wait, How Do I Use This?

1. Type the formula in. Use spaces to separate variable names from any syntax (e.g. parenthesis, multiplication sign, etc)
2. Click the button “Terms => Named Ranges”
   Now you have variables you can use in your formulas!
3. Set the values for each term
4. Click the button “Create Left and Right Equations”
5. Modify the last formula in the Value column.
   If needed, copy it down until you isolate the variable.

Troubleshooting

• Make sure the cell references in the code are correct.
  (It is a bit flaky to use absolute references — something to be improved later)
• Some variable names won’t work, e.g. “A1” as it is a cell reference, and surprisingly “R”, etc. Try longer and different variable names.
• One side is a number — nope, won’t work. You’re missing a variable. Put in a placeholder. e.g. “LHS” or “RHS”.
• Functions are not being removed? Remove any space between the function name and the left parenthesis.

Sign off

If you made it this far, congratulations! I hope you enjoy this tool. Please let me know if you have any issues or ideas on how this can be improved.