How to simplify this formula

Factual Questions
1

=IF(AND(B52=1, A52 <> A5, A52 <> A11, A52 <> A17), A52, IF(AND(B53=1, A53 <> A5, A53 <> A11, A53 <> A17), A53, IF(AND(B54=1, A54 <> A5, A54 <> A11, A54 <> A17), A54, IF(AND(B55=1, A55 <> A5, A55 <> A11, A55 <> A17), A55, IF(AND(B56=1, A56 <> A5, A56 <> A11, A56 <> A17), A56, IF(AND(B57=1, A57 <> A5, A57 <> A11, A57 <> A17), A57, IF(AND(B58=1, A58 <> A5, A58 <> A11, A58 <> A17), A58, IF(AND(B59=1, A59 <> A5, A59 <> A11, A59 <> A17), A59, IF(AND(B60=1, A60 <> A5, A60 <> A11, A60 <> A17), A60, IF(AND(B61=1, A61 <> A5, A61 <> A11, A61 <> A17), A61, IF(AND(B62=1, A62 <> A5, A62 <> A11, A62 <> A17), A62, IF(AND(B63=1, A63 <> A5, A63 <> A11, A63 <> A17), A63, " "))))))))))))

Note B52 to B63 is only either 1 or 0 and A52 to A63 is months of Jan until Dec :smack:

2

I think it’d be easier if you just describe what the formula is intended to do…

3

What’s in A5, A11, and A17?

One thing that would make it easier to read, if not actually shorten or simplify, would be to put the constants in it. For ex: “Jan” instead of “B52”.

4

Not knowing what environment or language you’re using, and being completely unwilling to try to follow the actual flow of that statement, I can still suggest you probably want a Switch statement. Depending on the language you are using it might be called a ‘Case statement’.

5

I’m sure that’s an Excel formula.

Pretty obvious now that A5, A11, and A17 are also months.

I’m guessing here but in C52:C63 one could put =iferror(match(b52,a4:A11,0),1) which will return 1 if the month in A52:A63 is not a check month. In D52: D63 put if(b52*c52=1,a52,""). The non-blank in D52: D63 is the output month. It’s possible to have a null output?

6

If the environment is MS Excel I suggest using a ‘select case’ statement.

7

I second investigating the use of the “Match function” proposed by Dave Hartwick, and you can probably combine it with “Offset” to return the data you want.

=Offset (start cell probably A52 or A51, by row as determined by nested Match function minus 1 depending on whether you’re using A52 or A51 as starting cell, by columns probably as 0)

You’ll probably have to play around with it, but the good news is there’s probably lots of ways to do it (like Dave’s response) once you know the proper functions to use.

Match, Offset, and Index are life-savers for these kinds of problems.

8

If readablity is the goal (and it helps with debugging), I sometime use interim cells which can be hidden. For example, if you put this in cell C53:
=AND(B52=1,A52<>A5,A52<>A11,A52<>A17)
and copy it down to C63 you will want the month that corresponds to the first TRUE. A MATCH and INDEX will easily pull that month for you.

You can either deal with an error when all or false in the INDEX or you can put an explicit TRUE in C64 with a corresponding entry in A64 that correponds to the " " in your original formula.

9

I didn’t try to decipher your formula. Just popped in to say that in my experience using the CHOOSE function simplifies nested if statements.
It also looks like the sort of formula that can be broken down to a couple of cells fairly easily. You could for example have one formula that works out which of your B52 to B63 cells is the first to be 1. I know it is klutzy, but I occasionally use CONCATENATE for this kind of thing. You could for example use FIND(“1”,CONCATENATE(B52,B53,B54,B55…),1) to good effect.