INT((MONTH($C$1:$C$7)+2)/3)=$A$2

[kathi]

Still don’t understand the +2)/3 part of the formula. Is that for fiscal
years?

‘COPY LINK’ SHEET
COLUMN A COLUMN B COLUMN C COLUMN D COLUMN E*
1 E5S040001 10/23/2003 MELTON 855
2 E5S050234 03/31/2004 04/20/2005 SMITH 385
3 E5S051234 07/25/2005 02/25/2006 SMITH 215
4 E5S060032 01/25/2006 01/25/2006 MELTON 1

*(COLUMN E has the formula
-------=MAX(IF(ISBLANK(D199),TODAY(),D199)-C199,1)--------to calculate the
number of days the invoice was open)

ON ‘TRENDS’ SHEET I have:
A formula that reads:
=AVERAGE(IF((YEAR((‘COPY LINK’!$C$1:$C$7548=$A$31))*(INT((MONTH(‘COPY
LINK’!$C$1:$C$7548)+2)/3)=$A$32),’COPY LINK’!$N$1:$N$7548))
(NOTE: I have A31:L31 with 2003, 2004, 2004, 2004, 2004, 2005, 2005, 2005,
2005, 2006, 2006, 2006, 2006
And A32:L32 with 4 1 2 3 4 1 2 3 4 1 2 3 4)

And another formula that reads: =AVERAGE(‘COPY LINK’$N$1:N$_____)
I have to manually put these numbers in when I add invoices to the COPY LINK
sheet..

PROBLEM: I would like to rely on the first formula so that I don’t hae to
manually change the formulas daily or whenever I add invoices to the COPY
LINK sheet HOWEVER they each come up with entirely different answers.
A B C D E F G H I J K L
Formula 1: 855 193 # 215 855 193 # 215 855 193 # 215
Formula 2: 855 385 0 0 0 0 215 0 0 1 0 0

[bpeltzer]

+2)/3 would convert your months to quarters. (Months 1 through 3 all become
quarter 1, etc).
As for entering the final row in your fomula... if there's nothing else in
those columns (totals, etc), you could just leave out the row qualifiers.
That is, use $N:$N rather than $N$1:$N$____

"kathi" wrote:

> Still don’t understand the +2)/3 part of the formula. Is that for fiscal
> years?
>
> ‘COPY LINK’ SHEET
> COLUMN A COLUMN B COLUMN C COLUMN D COLUMN E*
> 1 E5S040001 10/23/2003 MELTON 855
> 2 E5S050234 03/31/2004 04/20/2005 SMITH 385
> 3 E5S051234 07/25/2005 02/25/2006 SMITH 215
> 4 E5S060032 01/25/2006 01/25/2006 MELTON 1
>
> *(COLUMN E has the formula
> -------=MAX(IF(ISBLANK(D199),TODAY(),D199)-C199,1)--------to calculate the
> number of days the invoice was open)
>
> ON ‘TRENDS’ SHEET I have:
> A formula that reads:
> =AVERAGE(IF((YEAR((‘COPY LINK’!$C$1:$C$7548=$A$31))*(INT((MONTH(‘COPY
> LINK’!$C$1:$C$7548)+2)/3)=$A$32),’COPY LINK’!$N$1:$N$7548))
> (NOTE: I have A31:L31 with 2003, 2004, 2004, 2004, 2004, 2005, 2005, 2005,
> 2005, 2006, 2006, 2006, 2006
> And A32:L32 with 4 1 2 3 4 1 2 3 4 1 2 3 4)
>
> And another formula that reads: =AVERAGE(‘COPY LINK’$N$1:N$_____)
> I have to manually put these numbers in when I add invoices to the COPY LINK
> sheet..
>
> PROBLEM: I would like to rely on the first formula so that I don’t hae to
> manually change the formulas daily or whenever I add invoices to the COPY
> LINK sheet HOWEVER they each come up with entirely different answers.
> A B C D E F G H I J K L
> Formula 1: 855 193 # 215 855 193 # 215 855 193 # 215
> Formula 2: 855 385 0 0 0 0 215 0 0 1 0 0

[kathi]

Thanks. I get the +2)/3 now, I think, but these become quarters in a
calendar year not fiscal year, correct? Then what I did to make it fiscal is
put 2003 with a 4 to get the 1st quarter of the FiscalYear2004. Correct?
Also, I thought that in formula arrays you couldn't use column references?
Could I then us $C:$C also?
But that still doesn't explain my greatest confusion and the completely
different answers I am getting for supposedly the same question? Can you
help me see that?
Thanks again for your time. Truly appreciated.

"bpeltzer" wrote:

> +2)/3 would convert your months to quarters. (Months 1 through 3 all become
> quarter 1, etc).
> As for entering the final row in your fomula... if there's nothing else in
> those columns (totals, etc), you could just leave out the row qualifiers.
> That is, use $N:$N rather than $N$1:$N$____
>
> "kathi" wrote:
>
> > Still don’t understand the +2)/3 part of the formula. Is that for fiscal
> > years?
> >
> > ‘COPY LINK’ SHEET
> > COLUMN A COLUMN B COLUMN C COLUMN D COLUMN E*
> > 1 E5S040001 10/23/2003 MELTON 855
> > 2 E5S050234 03/31/2004 04/20/2005 SMITH 385
> > 3 E5S051234 07/25/2005 02/25/2006 SMITH 215
> > 4 E5S060032 01/25/2006 01/25/2006 MELTON 1
> >
> > *(COLUMN E has the formula
> > -------=MAX(IF(ISBLANK(D199),TODAY(),D199)-C199,1)--------to calculate the
> > number of days the invoice was open)
> >
> > ON ‘TRENDS’ SHEET I have:
> > A formula that reads:
> > =AVERAGE(IF((YEAR((‘COPY LINK’!$C$1:$C$7548=$A$31))*(INT((MONTH(‘COPY
> > LINK’!$C$1:$C$7548)+2)/3)=$A$32),’COPY LINK’!$N$1:$N$7548))
> > (NOTE: I have A31:L31 with 2003, 2004, 2004, 2004, 2004, 2005, 2005, 2005,
> > 2005, 2006, 2006, 2006, 2006
> > And A32:L32 with 4 1 2 3 4 1 2 3 4 1 2 3 4)
> >
> > And another formula that reads: =AVERAGE(‘COPY LINK’$N$1:N$_____)
> > I have to manually put these numbers in when I add invoices to the COPY LINK
> > sheet..
> >
> > PROBLEM: I would like to rely on the first formula so that I don’t hae to
> > manually change the formulas daily or whenever I add invoices to the COPY
> > LINK sheet HOWEVER they each come up with entirely different answers.
> > A B C D E F G H I J K L
> > Formula 1: 855 193 # 215 855 193 # 215 855 193 # 215
> > Formula 2: 855 385 0 0 0 0 215 0 0 1 0 0