This post may contain affiliate links. Please read my disclosure for more info:

Top tips for working with complex formulas

Tips for complex formulas

Tips for complex formulas

Note: This post was originally only shared with my newsletter subscribers, but I decided it was too good to only share once.

 

Have you ever had a formula which looks like this?

Complex Formula Example

Of course you have, we all have.  Just by looking at the formula you know it is going to take a while to unravel the logic behind it.  It’s even worse when it’s your formula and you can’t work out why it’s not working correctly.  Today, I want to share with you a few tips to help make working with long and complex formulas just that little bit easier.

 

TIP #1 – Using ALT + Enter to insert a line break

When you have text in a cell you can press ALT+Enter to create a line break, which forces the text onto a new line.  ALT+Enter also works with Formulas.  That’s right, you can actually break the formula down into specific parts so they appear on different lines.

Complex Formula with ALT+Enter

There we go, that’s much easier to read.

 

TIP #2 – Add comments into the function

It is possible to add a comment against a Cell by right-clicking the cell and selecting Insert Comment.  But, that’s not what I’m talking about here.  I’m talking about adding a comment into the formula itself.  In the formula type +N(“Your comment here”) to add a comment.

Complext Formula with Comments

Yes, this does make the formula longer, and in some circumstances, may even make it harder to read, but it’s an option, right?  Obviously, don’t include the comment within a text string, else it will output it as part of the text string.

 

Tip #3 – Use F9 to calculate specific sections of the formula

Within the formula bar it is possible to calculate the result of any section individually.  Simply highlight the formula

Complex Formula F9 Highlight

Press F9

Complex Formula F9 Result

Now you can see how each section of the formula is calculating – this is a great way to troubleshoot formula errors.
Here is the most important bit: Press Ctrl+Z to undo the calculation and turn it back to a function, or press Esc to leave the Formula Bar without making any changes.

 

Tip #4 – Use the Tool Tip to move around a function

The Tool Tip is the small box which appears below a function when you select any of the arguments.  You can click any of the arguments in the Tool Tip box, it will highlight the selected section of the function.

Complex Formula Tool Tip

In the screenshot above I have clicked on the “[value_if_true]” option from the first IF function.  It now highlights everything which is within that part of the function.

Save

2 thoughts on “Top tips for working with complex formulas

  1. I like tips 3 and 4, but tips 1 and 2 are potentially dangerous.

    Tip 1. In an Excel formula, both space and ALT+Enter act as the intersection operator. Therefore, putting ALT+Enter in a formula can lead to subtle bugs that are hard to find. A better solution for a long formula is to split it into simpler formulae across multiple cells.
    For example, consider the following two formulae:
    =SUM(A7:F10,C1:D17)
    =SUM(A7:F10
    C1:D17)
    The second formula uses ALT+Enter. If the cells A7:F10 and C1:D17 all contain the value 1, then the first formula returns a value of 58 while the second formula returns a value of 8. Why? Because I accidentally deleted the comma when I inserted the ALT+Enter. In a long and complex formula, this might be very difficult to notice. (The first formula double counts the cells C7:D10, which is probably also a bug.)

    Tip 2. The N() function is not passive and should never be added to a formula for the purpose of documentation. It would be much better to use a cell comment or a text box for the formula documentation.
    For example, consider the following formula:
    =IF(A1>=0,B1,”Must be positive”)+N(“Check that input value is positive”)
    If the value in A1 is >= 0 then this formula returns the value in B1. But if the value in A1 is not >=0, then the formula returns a #VALUE! error rather than the expected text “Must be positive”. Why? Because N(“Check that input value is positive”) has a value of zero, and “Must be positive”+0 is not a valid calculation. Another problem is that the formula returns #VALUE! if B1 contains anything other than a number.

    • Excel Off The Grid says:

      Bob – thank you for sharing your insights and experience.

      In regard to Tip #1, I certainly agree that care needs to be taken in terms of placement of the link break, else it could create incorrect results. I have always used it directly after the comma, which seems to be a reliable and logical place.

      In regard to Tip #2, I can see your point. I have not had issues with the N() function previously; must be luck that I’ve never used it with a scenario requiring a text result. I can’t argue with your example, when mixing text and the N() function it will create an error. Thank you for highlighting this issue.

Leave a Reply

Your email address will not be published. Required fields are marked *