how to tighten up unwieldy formula

[Green Fox]

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

 

[Pete_UK]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Herbert Seidenberg]

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