how to tighten up unwieldy formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Green Fox - 30 Jan 2008 14:15 GMT

It has taken some time to finally come up with this formula, Chip
Pearson's page gets most of the credit.

=IF($R
$6>TODAY(),SUMPRODUCT((Fiscal="F2008")*(quarter="q2")*(WeekdayAll=4)),SUMPRODUCT((Fiscal="F2008")*(quarter="Q2")*(WeekdayAll=4))-
(SUM(IF(WEEKDAY($Q$6-1+ROW(INDIRECT("1:"&TRUNC($R$6-$Q
$6)+1)))=4,1,0))))

$r$6 is today's date, $Q$6 is (december 1, 2007) the first day of the
second quarter of our fiscal year (F2008)

the formula uses those two dates to determine how many Thursdays
remain in the quarter, (all of the Thursdays in the quarter minus all
of Thursdays in the quarter prior to today's date.

If the quarter has passed (quarter 1, sep-1-07 to nov-30-07) the
formula display 0 remaining, if the quarter is in the future (q3) it
displays the total number Thursdays in that quarter.

I'm thinking the TRUNC might go, because I'm not dealing with times,
unless I misunderstand.

Naming the formula that appears twice might be an idea too...

anything obvious to you more knowledgeble Excel-ers?

Andy Fox

Reply to this Message

Pete_UK - 30 Jan 2008 14:45 GMT

Your SUMPRODUCT term appears twice, and you basically have:

=IF( condition, SP_formula, SP_formula - SUM_IF_formula )

So, you could avoid some duplication by writing this as:

=SP_formula - IF( condition,0,SUM_IF_formula )

I've not really looked at the SUM_IF_formula.

Hope this helps.

Pete

> It has taken some time to finally come up with this formula, Chip
> Pearson's page gets most of the credit.
[quoted text clipped - 23 lines]
>
> Andy Fox

Reply to this Message

Rick Rothstein (MVP - VB) - 30 Jan 2008 17:55 GMT

You will need to check me out, of course, but if I did everything correctly,
this formula...

=(WEEKDAY(R6)=5)+SUMPRODUCT(--(WEEKDAY(R6+ROW(INDIRECT("1:"&(DATE(YEAR(Q6),MONTH(Q6)+3,1)-R6))))=5))

will return the number of Thursdays remaining in the quarter (that started
with the date specified in R6 and ended with the date specified in Q6).
Notice, all calculations are being done from the values in R6 and Q6 alone
and that the value in R6 (the start of the quarter) is assumed to always be
the first day of the month. Also note that if the date in R6 is a Thursday,
it is counted as one of the remaining Thursdays in the quarter (remove the
first weekday test and leave only the SUMPRODUCT part if you want to exclude
the starting date being a Thursday from the count).

Rick

> It has taken some time to finally come up with this formula, Chip
> Pearson's page gets most of the credit.
[quoted text clipped - 23 lines]
>
> Andy Fox

Reply to this Message

Herbert Seidenberg - 31 Jan 2008 00:33 GMT

No formula needed.
You can create a Pivot Table instead
from your existing data.
http://www.freefilehosting.net/download/3b8cl

Reply to this Message