4

I'm looking to make a bunch of my complicated formulas more readable. For example:

Formula to make more readable

I know about Alt+Enter to add newlines within the formula itself. This makes the formula a little bit more manageable, but it's still not as readable as I'd like it. What else can I do to make big formulas like this one more readable?

Taelsin
  • 1,030
  • 12
  • 24
  • 3
    Named references would help some. I also suspect that formula could be simplified. – shawnt00 Dec 16 '16 at 16:46
  • What I have done in the past is to break down the if statements into hidden columns - then you can go to a specific point and make edits – Quintin Balsdon Dec 16 '16 at 16:47
  • What @shawnt00 said and also without reading through it looks to be a few levels of nesting - using arguements like `AND()` and `OR()` makes it easier to follow the logic – Jeremy Dec 16 '16 at 16:48
  • 1
    @shawnt00 For some reason named ranges didn't occur to me. Thank you! – Taelsin Dec 16 '16 at 16:52
  • 2
    Beyond names you basically can't make such huge formulas very readable. At some stage, the best approach is to use VBA instead. Above a certain complexity level such formulas become hard to debug and had to modify. – John Coleman Dec 16 '16 at 17:20
  • 1
    It looks like lots of this includes if part of a cell is some text (`if(left(G4,3)="DEC")...`, then you could instead just do a helper column perhaps, that has the left three letters, and use that? – BruceWayne Dec 19 '16 at 02:41
  • 1
    Can you post an example of the workbook you are using this formula in. We might be better able to help if we have something to actually work with. – Mike Dec 19 '16 at 14:31
  • 1
    Unfortunately no. There is information I'd rather not post here. I will be writing an answer to the question and posting it as I haven't really seen anything like this in my research. – Taelsin Dec 19 '16 at 15:27

1 Answers1

8

I'm going to answer this myself as Excel 2013 doesn't have any truly good ways of doing what I want with formulae.

In my research I have found three ways of making formulae more readable. The first is something I mentioned in my question: Alt + Enter. This will insert a newline into the formula so that you can break up the wall of text. As an example you can change this:

Without Alt + Enter

into this:

With Alt + Enter

The second method is to add a sort of comment to your formula. Personally I don't like this solution as it doesn't make formulae more readable to me, but others may like it. The method involves using the function N(). This function will return zero if given a string. For example:

No one knows the question...

Will give a result of 42 as N() returns zero.

The third method (shoutout to user shawnt00 for suggesting this in the comments) is to use named ranges. This can really help with readability as the user can convert something like A8:C14 to whatever name the user desires. To define a named range go to the Formulas table and click on "Define Name". Give the named range a name (preferably something that makes sense and not just "foo") and select the range of cells. Once done you can change something like this:

Something to do with February....

into a formula that is much more easily read:

Getting the how many days in a month

The last method is to create extra columns and hide them (Thank you to user Quintin Balsdon for suggesting this). If, for example, you need the same value multiple times in one formula you can put the formula used to get that value into a separate cell that you hide and reference that cell. Take this formula for example:

ALL THE INDEX MATCHES!!!

On its own this formula isn't too bad, but if you take a close look at the formula in my question you'd see I do a very similar thing. Simply taking the index/match out and putting that into another cell and changing the above formula to

No more index matches :(

will make a complicated formula much smaller and much easier to quickly check what the value of J1 represents.

Taelsin
  • 1,030
  • 12
  • 24