An easier formula please......

[Jock]

I have been asked to split down the entries in a post-logging spreadsheet
detailing how many entries of type 'A', 'B', 'C' and so on were input for
week 1, week2, week 3 etc of this year. There is a maximum of 33 different
types of post and, obviously, 52 weeks in the year. This means 1716
calculations! The formula below does the job where "AN$3" is the week number
and "$A3" is the post type:

SUMPRODUCT(--(1+INT((Chancery!$B$8:$B$9997-(DATE(YEAR(Chancery!$B$8:$B$9997),1,2)-WEEKDAY(DATE(YEAR(Chancery!$B$8:$B$9997),1,1))))/7)=AN$3)*(Chancery!$J$8:$J$9997=$A3))
This amount of calculations makes Excel grind to a halt when opening and
when the worksheet with the formulae has focus. I am therefore seeking an
alternative solution even if it's a code which only calculates the current
month rather than 52 weeks.
Any ideas?

 

[Bernard Liengme]

The answer lies in a Pivot Table.
Try Help; read visit sites below; then return with questions

http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm
http://www.techonthenet.com/excel/pivottbls/index.htm
http://www.dicks-blog.com/archives/2005/06/23/download-pivottable-parameters/
best wishes

 

[Ron Rosenfeld]

I have been asked to split down the entries in a post-logging spreadsheet
detailing how many entries of type 'A', 'B', 'C' and so on were input for
week 1, week2, week 3 etc of this year. There is a maximum of 33 different
types of post and, obviously, 52 weeks in the year. This means 1716
calculations! The formula below does the job where "AN$3" is the week number
and "$A3" is the post type:

SUMPRODUCT(--(1+INT((Chancery!$B$8:$B$9997-(DATE(YEAR(Chancery!$B$8:$B$9997),1,2)-WEEKDAY(DATE(YEAR(Chancery!$B$8:$B$9997),1,1))))/7)=AN$3)*(Chancery!$J$8:$J$9997=$A3))
This amount of calculations makes Excel grind to a halt when opening and
when the worksheet with the formulae has focus. I am therefore seeking an
alternative solution even if it's a code which only calculates the current
month rather than 52 weeks.
Any ideas?

Depending on how your data is organized, you might find a Pivot Table very
helpful.

You may need to add a column, in your data, for the weeknumber, as I do not
believe that the Pivot Table can group by weeks.
--ron

 

[CLR]

Another solution to problems like these, is to use code to fill your field
with the formulas you have that work, then in the same macro, do Copy >
Pastespecial > Values to delete the formulas, leaving only the
results.......whenever you want a "recalc" just fire the macro..........

Vaya con Dios,
Chuck, CABGx3

 

[Jock]

Thanks for the suggestions guys, I'm going to run with the pivot table...

Jock

 

[Harlan Grove]

I have been asked to split down the entries in a post-logging spreadsheet
detailing how many entries of type 'A', 'B', 'C' and so on were input for
week 1, week2, week 3 etc of this year. There is a maximum of 33 different
types of post and, obviously, 52 weeks in the year. This means 1716
calculations! The formula below does the job where "AN$3" is the week
number and "$A3" is the post type:

=SUMPRODUCT(--(1+INT((Chancery!$B$8:$B$9997
-(DATE(YEAR(Chancery!$B$8:$B$9997),1,2)
-WEEKDAY(DATE(YEAR(Chancery!$B$8:$B$9997),1,1))))/7)=AN$3)
*(Chancery!$J$8:$J$9997=$A3))

....

If all the dates in Chancery!$B$8:$B$9997 come from the same year, one
efficiency would be using another cell for the formula

=DATE(YEAR(Chancery!$B$8),1,2)-WEEKDAY(DATE(YEAR(Chancery!$B$8),1,1))

and, FTHOI, I'd name it FDOY. That would reduce the formula to

=SUMPRODUCT((INT(1+(Chancery!$B$8:$B$9997-FDOY)/7)=AN$3)
*(Chancery!$J$8:$J$9997=$A3))

At this point you could gain even greater efficiency by using indexing to
partition the table IF it's sorted on col B in ascending order. If so, then
use a table that has the 1st day of each week of the year in its first
column, and a MATCH formula in its second column. If that table were in
X1:Y54 (every once in a while you need to have a 53 week year since years
are 52 weeks plus 1 or 2 extra days) and were named FDOWIT, enter the
formulas

X1:
1

X2:
=X1+1

Y1:
=MATCH(FDOY+7*(X1-1),Chancery!$B$8:$B$9997,0)

Y2:
=IF(X2-X$1<365+(DAY(X$1)<>DAY(X$1)+365),MATCH(FDOY+7*(X2-1),
Chancery!$B$8:$B$9997,0),ROWS(Chancery!$B$8:$B$9997)+1)

Fill X2:Y2 down into X3:Y54. Then change the counting formula to

=COUNTIF(INDEX(Chancery!$J$8:$J$9997,LOOKUP(AN$3,FDOWIT))
:INDEX(Chancery!$J$8:$J$9997,LOOKUP(AN$3+1,FDOWIT)-1),$A3)

This should produce about as fast recalculation as Excel could provide for
something like this. Note: the 0 3rd arguments to MATCH in the col Y
formulas above are necessary to locate the 1st instance of each beginning of
week date. Even though Chancery!$B$8:$B$9997 would be sorted, Excel could
return the row index of ANY match to X#. That's one of the gotchas of
MATCH's binary search implementation.

 

[Jock]

Harlan,
thanks for the serious amount of input given - much appreciated.
I'm going to have a go at this on a copy of the data.

 

[Jock]

Need clarification on a couple of issues here....
Everything works fine until I get to the X,Y column part:
In column X, should each cell show a week number (1 down to 52(53) or day
number of the week (1 down to 365 or so in increments of 7)?
I get #N/A in every cell in Y
HELP....


Jock

 

[Harlan Grove]

In column X, should each cell show a week number (1 down to 52(53)
or day number of the week (1 down to 365 or so in increments of 7)? ....
I get #N/A in every cell in Y ....
"Harlan Grove" wrote: ....

Week numbers from 1 to 53

Note: I had defined the name FDOY to be the date of the first day of
the year. In Y1, X1 = 0, so the 1st arg to MATCH evaluates to the
first day of the year.

In Y2, X2 = 2, so the 1st arg to MATCH is 7 days after the 1st day of
the year, so the 1st day of the 2nd week of the year. And so on in col
Y through either Y53 or Y54. If the year in question is one of the
peculiar ones with 53 weeks, Y53 would evaluate to the 1st day of the
53rd week and Y54 to one more than the number of rows in Chancery!
B8:B9997, which I've been assuming is a range of dates. If the year in
question has 52 weeks, then both Y53 and Y54 should evaluate to one
more than the number of rows in Chancery!B8:B9997.

So, does Chancery!B8:B9997 contain standard date values?

 

[Jock]

Hi Harlan,
Yes, there are standard dates in 'B', and as time goes on, the column will
fill up with sequential dates.
I'm off home now but I shall tackle this again tom.