Overcoming the limit of 7 nested Ifs in Excel XP. Is it possible?

[pcw]

Any suggestion on this would be greatly appreciated.
I thought there was a way to overcome the limit of 7 nested IFs in the one
formula.

My worksheet is setup like this:
Column A hold dates for an entire year. The dates are entered in the
following format: 1-2-07
In Column B I need to setup an IF formula that checks if the month is either
Jan, Feb, March and so on to December.
If the month is Jan return "X", if the month is Feb return "Y", if the month
is March return "Z", and so on to December.

In Column B the formula works fine with 7 IFs

=IF(MONTH(B10)=1,"Jan",
IF(MONTH(B10)=2,"Feb",
IF(MONTH(B10)=3,"March",
IF(MONTH(B10)=4,"April",
IF(MONTH(B10)=5,"May",
IF(MONTH(B10)=6,"June",
IF(MONTH(B10)=7,"July",False)))))))

Below is the formula I have setup for 11 Ifs.
I use the IF formula above, then I position the text cursor at the end of
the formula (outside the last closing parenthesis), then I select IF in the
Functions dropdown list located above A; as soon as I do this Excel
automatically inserts a + sign. I leave the + sign there then enter another
4 Ifs.

=IF(MONTH(B10)=1,"X,
IF(MONTH(B10)=2,"Y",
IF(MONTH(B10)=3,"Z",
IF(MONTH(B10)=4,"XX",
IF(MONTH(B10)=5,"YY",
IF(MONTH(B10)=6,"ZZ",
IF(MONTH(B10)=7,"XXX",False)))))))+
IF(MONTH(B10)=8,"YYY",
IF(MONTH(B10)=9,"ZZZ",
IF(MONTH(B10)=10,"XXXX",
IF(MONTH(B10)=11,"YYYY","ZZZZ"))))

When I press Enter Excel displays an error message telling me there is an
error in the formula.

Would anybody know if this is feasible?

Thank you very much in advance.

Pc.

 

[Pete_UK]

You can do this without using IFs - use a table instead. I put the
following in cells M1 to M12:

X
Y
Z
XX
YY
ZZ
XXX
YYY
ZZZ
XXXX
YYYY
ZZZZ

I put a few dates in column B to test this formula out:

=INDEX(M$1:M$12,MONTH(B1))

and copied the formula down to suit the number of dates in column B -
it returns what you want.

Hope this helps.

Pete

 

[Bob Phillips]

I am sure this could be simplified, but here is a start

=IF(INT((MONTH(B10)-1)/3)=0,CHAR(87+MONTH(B10)),IF(INT((MONTH(B10)-1)/3)=1,
"X"&CHAR(84+MONTH(B10)),IF(INT((MONTH(B10)-1)/3)=2,"XX"&CHAR(81+MONTH(B10)),"XXX"&CHAR(78+MONTH(B10)))))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Pete_UK]

Hi Bob,

here's a simpler way (I think):

=REPT(CHAR(MOD(MONTH(B10)-1,3)+88),INT((MONTH(B10)+2)/3))

but it's not so obvious how it works !! <bg>

Pete

 

[Roger Govier]

Hi

One way
=REPT(CHOOSE(MOD(MONTH(A1),3)+1,"Z","X","Y"),INT(MONTH(A1)/3)+1)

 

[Pete_UK]

Or another one:

=CHOOSE(MONTH(B10),"X","Y","Z","XX","YY","ZZ","XXX","YYY","ZZZ","XXXX","YYYY","ZZZZ")

Pete

 

[Roger Govier]

Apologies

That last formula steps the number of X's etc. up too soon
Try
=REPT(CHOOSE(MOD(MONTH(A3),3)+1,"Z","X","Y"),INT(MONTH(A3)/3)+1-1*(MOD(MONTH(A3),3)=0))

 

[Roger Govier]

Hi Pete

Very nice!!!

I wish I had seen that before posting my solution, as I then wouldn't
have bothered.

--
Regards

Roger Govier


Hi Bob,

here's a simpler way (I think):

=REPT(CHAR(MOD(MONTH(B10)-1,3)+88),INT((MONTH(B10)+2)/3))

but it's not so obvious how it works !! <bg>

Pete

 

[Guest]

Variation on this same theme. I am sure that this has been asked/
answered by thousands of accountants across the world , but was not
exactly sure how to quickly search for it...
Cell A1: An optional manually entered AsOf date (for planning/
snapshot in time purposes)
Column B: Account descriptions
Column C: YTD Budget totals
Column D: YTD Actual totals
Column E: YTD Variance
Column F: Jan Budget amounts
Column G: Jan Actual amounts
Column H: Jan Variance amounts
Column I: Feb Budget amounts
....
If there is NOT a manually-entered AsOf date, then I would like the
YTD columns to compare TODAY() to the month indicated in the column
and only take values for =SUM for months that are complete. For
example, if today is 2/15, then I would like the sum function in the
YTD columns to only include the amounts from the January columns. But
if today was 12/15, then then I would like the sum function in the YTD
columns to include the amounts from the January - November columns.

Similarly, if there is a manually entered AsOf date, then I would like
the YTD columns to use that date rather than TODAY() for the
comparison to determine what to include in the SUM.

Surely, there is an easy way to do this since I could not get the
brute-force nested if approach to work.

Thanks for your help.

 

[pcw]

Many thanks to all of you. Can't wait to try. Will let you know once I have
digested it all.

pc

 

[Pete_UK]

Thanks, Roger,

I think my first posting of using a table and the INDEX() function is
the most straightforward.

Pete

 

[Roger Govier]

Hi

Assuming data starts in row 3. In A1 just enter the Month number you
want to include up to in your Year to Date figures
1 for Jan 12 for Dec.

In cell C3
=SUMPRODUCT(--(OFFSET($F3,0,0,1,$A$1*2)),--(MOD(COLUMN(OFFSET($F3,0,0,1,$A$1*2)),2)=0))
in cell D3
=SUMPRODUCT(--(OFFSET($F3,0,0,1,$A$1*2)),--(MOD(COLUMN(OFFSET($F3,0,0,1,$A$1*2)),2)=0))

OR for the non-volatile solution,
in cell c3
=SUMPRODUCT(--($F3:INDEX($F3:$AC3,$A$1*2)),--(MOD(COLUMN($F3:INDEX($F3:$AC3,$A$1*2)),2)=0))
in cell D3
=SUMPRODUCT(--($G3:INDEX($G3:$AD3,$A$1*2)),--(MOD(COLUMN($G3:INDEX($G3:$AD3,$A$1*2)),2)=1))

To provide the Date for your "YTD up to" use
="YTD up to" &TEXT(DATE(2007,A1+1,0),"dd mmm yy")

 

[Guest]

Hi Roger,

Thanks for replying back with your solution. I do not actually
understand how either of those formulas work but will bring up the
Help to research those functions. Could you explain the comment about
volatility?

Thanks again for your assistance.

 

[pcw]

Thank you all for your help.

The following 2 answers returned what I wanted

Answer 1

=IF(MONTH(B17)=1,"X",
IF(MONTH(B17)=2,"Y",
IF(MONTH(B17)=3,"Z",
IF(MONTH(B17)=4,"XX",
IF(MONTH(B17)=5,"YY",
IF(MONTH(B17)=6,"ZZ",
IF(MONTH(B17)=7,"XXX","")))))))&
IF(MONTH(B17)=8,"YYY",
IF(MONTH(B17)=9,"ZZZ",
IF(MONTH(B17)=10,"XXXX",
IF(MONTH(B17)=11,"YYYY",
IF(MONTH(B17)=12,"ZZZZ","")))))

Answer 2

=CHOOSE(MONTH(B17),11,22,33,44,55,66,77,88,99,111,222,333)

pc

 

[Roger Govier]

Hi

Offset is what is known as a volatile function, which has to be
re-calculated whenever there is a change in any value in the sheet. With
just a few volatile functions, this is no problem, but when there are
many the additional re-calculation overhead can slow down the
performance of the workbook considerably.

The Offset function works as
Starting cell $F$3 fixed with absolute referencing ($Column$Row)
0 number of rows offset from start
0 number of columns offset from start
1 the height of the range (1 row)
$A$1*2 the length of the range (twice the value entered
in cell A1)

The second part is taking the range as above and looking at the Column()
numbers for the cells in the range
MOD(column(),2) =0 is testing whether the column number is Even
(Budget)
MOD(Column(),2)=1 is testing whether the column number is Odd (Actual)

The double unary minus -- converts the results of the tests from True to
1 and False to 0
It is not strictly needed for the first part as the values returned are
numbers.
The second part will either return True or False , 1 or 0 which when
multiplied by the corresponding number will return that number or 0 (
0*1000 = 0)

Sumproduct then just adds the results of the various multiplications to
give the result.

Index is working in much the same way, except we are giving it a range
$F3:$AC3 and telling it how far along that range to go from the starting
position of $F$3 i.e go as far $A1*2 columns from $F$3.
The rest of the calculation is as above

Because Index is non-volatile, the overhead on calculation is much
reduced.

 

[Guest]

If I want to avoid the volatile solution (although I am guessing that
I do not have enough to cause a problem, it is probably good practice
to avoid volatile solutions?)...
If my repeating sequence is Budget, Actual, Variance, Budget, Actual,
Variance, etc. wouldn't I need the length of the range to be *3 (since
there are 3 repeating columns) for the Index?

And if so, does that mess up the odd/even column approach?

 

[Roger Govier]

Hi

I had not noticed the Variance columns across the page.
Just amend the formulae as follows

=SUMPRODUCT(--($F3:INDEX($F3:$AC3,$A$1*3)),
--(MOD(COLUMN($F3:INDEX($F3:$AC3,$A$1*3)),3)=0))

=SUMPRODUCT(--($G3:INDEX($G3:$AD3,$A$1*3)),
--(MOD(COLUMN($G3:INDEX($G3:$AD3,$A$1*3)),3)=1))

 

[Guest]

Thank you for the update. This still does not work for me. I am
going to assume that AC and AD must also be adjusted to reflect the 3
repeating columns rather than the 2 repeating columns.