Formula in pivot table

[Eucalypta]

Dear all,

Kind of new to pivot tables and hoping that someone out there is able to
help me ;-).

I have a pivot table that generates in colom A country names and in row 2
weeknumbers. The weeks are grouped to quarters evry time I update the table.

To avoid grouping and ungrouping, I am looking for a formula that gives me
the total per quarter per country starting in cell B19.

Thanking you in advance for your kind help.
Eucalypta

 

[Ed Ferrero]

Hi Eucalypta,

If the data is a date, Excel can automatically group into calendar quarters
in a Pivot Table.

To convert a week number to a date, if week number is in cell A2 then
=DATE(2008,1,A2*7-2) will give a date.

So, my suggestion is to add a column to the data for the pivot table, call
it 'weekEnded' and then use that column as a row field. Then group that
field. Quarter grouping will be an automatic option, so there is no need to
regroup when the pivot table is recalculated.

For more information, see my pivot table tutorial at
http://www.edferrero.com/ExcelTutorials/PivotTableTutorial2003/tabid/89/Default.aspx

Ed Ferrero
www.edferrero.com

 

[Eucalypta]

Hi Ed,

Thank you for your reply.

I have made the insertion you suggested and that works. What I cannot find
is where I tell Excel I want it sorted by quarter. Btw I am working with
Excel 2003.

Kindly advise.

Thanks in advance.
Eucalypta

 

[Ed Ferrero]

Hi Eucalypta,

You can drag-and-drop quarters to whatever order you want.

Otherwise, remove all row fields except the weekEnded date, then sort by
date, then add row fields to the pivot table. The pivot table should remain
sorted by date. Note that whenever grouping dates, I usually add both year
and quarter (or month) to the row fields to avoid confusion.

Ed Ferrero
www.edferrero.com

 

[Eucalypta]

Hi Ed,

Thanks for your reply.

The insertion of extra rows is what I want to avoid. If that is the
solution, I can keep the situation as it is, i.e. group and ungroup.

Preferably I like a formula like lookup or something. I think that I will
than only have to make the formula once for every quarter and not have to
bother anymore for the rest of the year.

Kindly advise.

Kind regards,
Eucalypta