I can't solve this one...can anyone help

[russ]

I few weeks ago, I posted this problem to this site. I
got a few replies that came close but none did the job,
can anyone else figure it out?

I recently inherited a project when I started a new job.
On the first day, I noticed that a spreadsheet formula was
incorrect but I cannot seem to solve it. Here is the data.

A B C D E F G
MI Minor Discrepancy 3 2 1 0 0

If C-F are quarterly totals of minor discrepancies and are
a sum of a column above them (i.e., the "3" below "C" is
C28 and is a sum of C20:C25, etc.)and G is NOT a running
total but a total of the most recent quarter, how do I
write the formula? For example, if we are in the fourth
quarter now (column F) with no discrepancies, I want G to
show 0, not the sum of C-F. The closest I got to an
answer was this formula:
=OFFSET(G28,,-SUM(N(MONTH(TODAY())<={3,6,9,12})))

I have to plead ignorant of what all this means. It did
manage to handle the problem for two quarters but then it
wanted to take whatever was put into G28 or F28 (3rd and
4th quarters). If anyone can help out explaining the
above formula and or rewriting it so it will work in my
situation I would greatly appreciate it. Thanks.

Major Russ Bartlett
USAF

 

[Bob Phillips]

Russ,

Explanation.

First it checks whether the month of today is less than or equal to the
array 3,6,9,12. This returns an array of True/False, which is converted to
0/1 values by the N function. This array is summed by the SUM function. So
for example, if the month of today is 7, you can see that this is less than
9 and less than 12, so the expression
MONTH(TODAY())<={3,6,9,12} evaluates to FALSE,FALSE,TRUE,TRUE
N(MONTH(TODAY())<={3,6,9,12}) evaluates to 0,0,1,1
SUM(N(MONTH(TODAY())<={3,6,9,12})) gives a result of 2

This summed valued is used as a column offset, negated as it is to the left,
within the OFFSET function. OFFSET is offsetting G28 by 0 rows and this sum
result columns. In the example here, this is -2 columns, which means the
OFFSET function returns E28, and so shows the value 1.

Now to fixing it. Well, here I am stumped, because it seems to return the
correct answer four me. Today, in July, I get the value in E28. If I use a
date rather than TODAY(), I get C28 for Q1, D28 for Q2, and F28 for Q4. You
say you get F28 and G28 for Q3 and Q4? Do you want to send me the workbook
direct check it?

 

[Tom Ogilvy]

, I posted this problem to this site.
You didn't post this problem to the site. At that time you showed blank
cells for quarters which had not yet been encountered. If you don't state
your situation clearly, you will get solutions that don't answer your
requirements.

Also, you haven't stated what your quarters are. Are you using US
Government Fiscal Years where the first quarter would be Oct-Nov? That
would be important to state in understanding why you claim the formula
doesn't work. If you only posted a short while ago, then I doubt you really
have any experience with how it functioned in quarters 1 and 2.

Using a Jan - Dec year and quarterly basis, the formula works for me as
well.

Regards,
Tom Ogilvy