Pivot table

[VegasNath]

I have an excel database laid out as follows:
Date
Period (Month & Year)
Opening
Additions
Removals
Closing

I would like to summarise the data using a pivot as follows:

Row = Period
Col1 = The opening balance of the first day of the period
Col2 = The sum of all additions within the period
Col3 = The sum of all removals within the period
Col4 = The closing balance of the last day of the period

Is this possible using a pivot, and if so, please how?

Edit: Example added.

Pivot.xls

[sdckapr]

Here is way without a pivot.

Steve

[VegasNath]

Here is way without a pivot.

Steve

Thankyou very much Steve, that is a great alternative and will come in very useful. I am however still interested in whether or not a pivot is possible for such an exercise. I rarely work with pivots so am not sure of the limitations, but for the large database that I am working with, my guess is that a pivot would be less demanding upon resource.

[sdckapr]

I don't use Pivot tables that extensively but it seems to me that you would need to add 2 new columns one listing the real opening and closing (and having nothing in the other rows) so that it can sum properly. I grouped the date by year and month to display similar to what you had.

If you really want the "period" (mmm-yy) you would need to calculate the period as the same date for all dates in the month. A formula like:
=DATE(YEAR(A2),MONTH(A2),1)
would work, then the pivot will automatically group.

Steve
PS My guess is that this pivot example would use MORE resources since each line in the dataset requires 2 array functions. Having them just in the summary table would be more effective...

[VegasNath]

I don't use Pivot tables that extensively but it seems to me that you would need to add 2 new columns one listing the real opening and closing (and having nothing in the other rows) so that it can sum properly. I grouped the date by year and month to display similar to what you had.

If you really want the "period" (mmm-yy) you would need to calculate the period as the same date for all dates in the month. A formula like:
=DATE(YEAR(A2),MONTH(A2),1)
would work, then the pivot will automatically group.

Hi Steve, Thankyou very much for your help here!

I changed the period formulas as you suggested and then amended the pivot to accomodate the period, so far so good.

PS My guess is that this pivot example would use MORE resources since each line in the dataset requires 2 array functions. Having them just in the summary table would be more effective...

In this case, I agree..... which generates further questions. Using XL2007, are there any alternatives to these array & sumproduct formulas? I'm guessing that SUMIFS could replace the SUMPRODUCTS but I could not get the syntax right. Is there an alternative in 2007 for the array?

Also, I rarely use pivots, my attempt to build from scratch (attached) is as close as I can get. Please can you explain the steps taken to generate the pivit? Even though the pivot in this case would not be the best method, I would still like to understand how you built it.

pivot example.xls

Many thanks!

[sdckapr]

To get your pivot table to match mine better, click on the "Data" label and drag it to the "Total". It will transpose them into columns. Then all you need to do is edit the labels used (instead of the "Sum of..." defaults).

Here is sample with SUMIF, that minimizes the arrays. You still need an array to get the min and max date for each month (I can't see a way out of this), but you only need it once for each month in the table, not for each calc or for each row entry.

Note the summary table must use the 1st of the month for the period to match the period in the dataset.
Steve

[VegasNath]

Great Steve, Thankyou very much! I managed to replicate your pivot eventually which is great, just a shame about the required arrays needed to generate it. The SUMIF option is another great alternative!

[VegasNath]

Playing with the pivot some more.... I notice that one can use Min of Opening & Max of Closing. In most cases, this would suit, but occasionally the closing can reduce. Shame.

Out of curiosity, are there any add-ins or anything that offer other "summarize by" options? If only I could replace MIN & MAX with earliest & latest.