Long Formula

Posted by Hal T. on February 06, 2001 10:24 AM

Have a formula below:
Basically, it sums columns based on system date.

P9 = Jan
q9 = Feb and so forth.

We're always working a month behind.

Is there a more practical way of writing this formula?

thanks,

Hal

IF(MONTH(NOW())=2, P9) + IF(MONTH(NOW())=3, P9+Q9) + IF(MONTH(NOW())=4, P9 + Q9 + R9) + IF(MONTH(NOW())=5, P9 + Q9 + R9 + S9) + IF(MONTH(NOW())=6, P9 + Q9 + R9 + S9 + T9) + IF(MONTH(NOW())=7, P9 + Q9 + R9 + S9 + T9 + U9) + IF(MONTH(NOW())=8, P9 + Q9 + R9 + S9 + T9 + U9 + V9) + IF(MONTH(NOW())=9, P9 + Q9 + R9 + S9 + T9 + U9 + V9 + W9) + IF(MONTH(NOW())=10, P9 + Q9 + R9 + S9 + T9 + U9 + V9 + W9 + X9) + IF(MONTH(NOW())=11, P9 + Q9 + R9 + S9 + T9 + U9 + V9 + W9 + X9 + Y9) + IF(MONTH(NOW())=12, P9 + Q9 + R9 + S9 + T9 + U9 + V9 + W9 + X9 + Y9 + Z9) + IF(MONTH(NOW())=1, P9 + Q9 + R9 + S9 + T9 + U9 + V9 + W9 + X9 + Y9 + Z9 + AA9)

Posted by Tim Francis-Wright on February 06, 2001 10:49 AM

This is a bit tricky:
SUM(INDIRECT("P9:" & IF(MONTH(NOW())=1, "AA9", CHAR(78 + MONTH(NOW())) & "9")))

CHAR(78+MONTH(NOW())) turns the month into a
letter; 1->P, 2->Q, etc.
If you move these cells inthe worksheet, then
you'll need to modify the formula.

The IF is needed to make sure that in January
you get everything from P9 to AA9.

Hope this helps!

Posted by Hal T on February 06, 2001 11:45 AM

Thanks, but I need to copy the formula both down the column, as well as to other worksheets. As it is, it would be a long process changing the formulas in each cell to accomodate the cells in the totals. "Hard Coding" is something I don't care to really get involved in. Takes too much time.

Posted by Scott R on February 06, 2001 1:21 PM

Try this:
=SUM(OFFSET(P9,,,,MOD(MONTH(NOW())-2,12)+1))
where P9 represents the January cell.

Posted by Tim Francis-Wright on February 06, 2001 1:35 PM

Right you are. I sat in my thinking chair
and came up with:
[with the data in P9 to AA9:-]
IF(MONTH(NOW())=1, SUM(P9:AA9), SUM(OFFSET(P9, 0, 0, 1, MONTH(NOW()) - 1)))

This doesn't require any hard-coding whatsoever.
[OFFSET(P9,A,B,C,D) generates a range starting at
P9, offset by A rows and B columns, that is
C rows high and D columns wide.]

I originally feared that you might have to use
something like:
IF(MONTH(NOW())>=2, P9) + IF(MONTH(NOW())>=3, Q9) + IF(MONTH(NOW())>=4, R9) +...+ IF(MONTH(NOW())=12, Z9) + IF(MONTH(NOW())=1, SUM(P9:AA9))
This still works but is a bit unwieldy.

Posted by Hal T. on February 06, 2001 1:41 PM

That worked fine! thanks

Can you explain the formula to me?

Posted by Scott R on February 06, 2001 2:03 PM

The OFFSET provides a dynamic range width to sum, driven by the current month, starting with P9 as the leftmost cell. If it's Month X, you want to sum the leftmost X-1 cells. January is an exception to this because when X = 1, X - 1 returns 0 (no cells summed), therefore the MOD function translates that 0 into a 12, and 12 cells will be summed. The MOD function is not intuitive; suffice to say it's what accommodates your one-month lag.

Posted by Mark W. on February 06, 2001 3:09 PM

Sweet!

Posted by Hal T. on February 07, 2001 6:18 AM

Thanks for the explanation.......

I thought I was pretty savy on Excel....
Just shows you can never think you know it all.
Great Job!