excel pivot table guru

W

WinstonWolfe

Besides trying MrExcel's forum, I thought I'd ask if anyone here has experience with custom calculations in pivot tables.

I am trying to reduce use of sumproduct formula in a xls and achieve the same thing with pivot tables.....but it will require some manipulaiton of data within the p.tables.
 
W

WinstonWolfe

Hmmmm.... some of the responses are a little dubious at that site Geoff. Think I'll be better sticking with Bill Jelen (Mr Excel) and Mike Girvin (ExcelisFun).
 
W

WinstonWolfe

thx for offer Tess.

sorry for the ramble, but thought it might help orient you. let me know if you need clarification......

OK, I developed my own cashbook to keep my financial records, which my acct helped me set up.

Others want to use the cashbook and I have been defensively programming it into a series of template modules.

To improve its performance, I am trying to reduce the use of sumproduct formula....which are used heavily in the cashbook, income statement, and cash flow.

I am exploring two options:
- use pivot tables
- do more calcs in a memory array using vba, minimizing read/writes by working with larger blocks.

The second option will require a larger time investment.

So, what I am trying to do with pivot tables is thus:

I have 2 worksheets, one each for receipts and payments. THese are consolidations from several dynamic imported bank statement worksheets.

I currently have separate auto updating pivot tables for rcpts and pmts.
However, these pivots are both dynamic, in that rows are months and will expand down with time.
Columns are cashbook categories, and empty categories are left out of the pivot table. This effects the number of columns in the p.table.

What I want to do is:

- subtract total pmts from total rcpts to get net monthly cash flows.
I presume this is a straight forward matter of using custom calcs to take one column from another.

- run a cumulative balance on this CF.

I figure the solution is to either:

1.
create a new database table where I can make one pivot table with rcpts and pmts categories and totals in it....and simply subtract total pmts column from total rcpts column..... though I haven't thought how to set up a custom calculation to get a column running a cumulative net CF balance, especially considering the dynamic time nature of the database.

2.
create a third p. table to do the subtraction and cumulative balances, that uses the data within the current 2 pivot tables.


So what I need help with is:
- am I better to set up a single pivot table wiht rcpts and pmts in it, or use a third table that uses the data in the existing two tables.
- how do I set up a cumulative balance column in a p.table that links to a dynamic database.

TIA
WW
 
B

Belu

had a quick read of your requirements - have you tried a SUMIF? if you run another column next to dates with the following formula in it, it will convert them into months:

=TEXT(B1,"MMM-YY")

Which as an example would output JAN-10

Then you can do a SUMIF on JAN-10 to get monthly totals quite simply.

Personally I use formula's over Pivots - but that is just my preference :)

Cheers

Ben
 
W

WinstonWolfe

Belu said:
had a quick read of your requirements - have you tried a SUMIF? if you run another column next to dates with the following formula in it, it will convert them into months:

=TEXT(B1,"MMM-YY")

Which as an example would output JAN-10

Then you can do a SUMIF on JAN-10 to get monthly totals quite simply.

Personally I use formula's over Pivots - but that is just my preference :)

Cheers

Ben
Click to expand...

Yes Ben, I use that for in the current cash flow, income statement, and bas tables.

i.e. for receipts
where
rdate = transaction date column
rcat = category (from chart of accounts) column
ramount = gross receipt column

=SUMPRODUCT((TEXT(rdate,"yyym")=TEXT($A49,"yyym"))*(rcat=C$48)*ramount)

originally I started a new workbook every FY, but am now leaving several FYs of data in the same wb. that's where the heavy use of sumproduct slows the auto calc down and necessitates manual calc mode.

the advantage of having multiple fy's in the same wb is it allows analysis of expenditures through time, and allows the setting up of budget projections and actuals based on previous years figures.
 
T

tess85

OK, I just spent about 5 mins reading through the thread. I think I know what WW is getting at, but is there any chance you can upload a spreadsheet for me to play with (Rather than have to create my own mockup etc etc)?

Otherwise, can't you just do what Belu said, and do that for the monthly sum of the payments and receipts, thus giving you your net position?

Re: cumulative balance at any point in time - check out the "running total" section of this website - http://www.contextures.com/xlPivot10.html

sorry for the terrible explanation, I think I really need to see the spreadsheet before I can do anything sensible...
 
B

Belu

Winston - what about a concatenation of the category and month series? that will give a unique value you can sumif against?
 
W

WinstonWolfe

tess85 said:
OK, I just spent about 5 mins reading through the thread. I think I know what WW is getting at, but is there any chance you can upload a spreadsheet for me to play with (Rather than have to create my own mockup etc etc)?

will PM you a link Tess by 12.15pm. thanks for your time and effort.....btw, you won't see the sumproduct formula in the cashbook, as they are put their using VBA code, then removed after calculation is complete. (this helps protect against corruption). will explain more in private message.

Otherwise, can't you just do what Belu said, and do that for the monthly sum of the payments and receipts, thus giving you your net position?

the issue with using sumif or sumproduct is that if you set up a worksheet like a formal cashbook, you have transaction details on the left, and chart of account categories on the right. for each category column, you have to use a sumproduct or sumif formula in each row. If you have 1000 transactions and 20 categories, that's 20,000 sumproduct formula......this slows the spreadsheet down dramatically and requires the wb to be put into manual calc mode.

I have the cashbook set up like this now.
I want to use pivot tables for the income statement, cash flow, and BAS tables, which are derivatives of the cashbook.

All will be clear when you see the file.

Re: cumulative balance at any point in time - check out the "running total" section of this website - http://www.contextures.com/xlPivot10.html

sorry for the terrible explanation, I think I really need to see the spreadsheet before I can do anything sensible...
Click to expand...

Thanks for the contextures link. They have some good stuff on that site. And I think that answers the cumulative total question nicely. Will mess with it tonight.


Belu said:
Winston - what about a concatenation of the category and month series? that will give a unique value you can sumif against?
Click to expand...

Several years ago Ben, I looked into what would be the fastest method. Sumproduct was considered by many to be the most efficient.
Sumif in 2003 only allows single conditional summation. I understand Sumifs? in 2007 is a lot faster and allows more than single condition summation. Nevertheless, pivot tables are exponentially faster still.

I have avoided them in the past because it is a learning curve working out how to use their results outside the tables and do custom calcs..... but am giving it a go now.
 
J

Jonathon

I've been reviewing a client's model recently - it had 27,000 cells with SUMPRODUCT, and as some of them had two SUMPRODUCTs in the formula, there were over 36,000 SUMPRODUCT calcs in the model.

Needless to say the calc time was a little on the slow side, but nothing a litle OFFSET/MATCH won't fix for them.
 
W

WinstonWolfe

Jonathon said:
Needless to say the calc time was a little on the slow side, but nothing a litle OFFSET/MATCH won't fix for them.
Click to expand...

Interesting. I am struggling to visualize how offset/match is used to conditionally sum.
Imagine you use it to pull a conditional array from each column, then multiply each, and hopefully end up with a sumproduct. :)

= offset(xxx(match(condition1,x,x)xxx)*
offset(xxx(match(condition2,x,x)xxx)*
offset(xxx(match()xxx)

What do you estimate the speed gain as????
 
J

Jonathon

In this particular example they weren't using SUMPRODUCT to conditionally sum, or at least they weren't using it to conditionally sum more than one number. It was being used to look up a company (row), GL code (row)and period (column) to return an actual value.

The calc speed with the SUMPRODUCTs was about 90 seconds. Hardcoding all the SUMPRODUCT cells to their value reduced the calc speed to about 1.5 seconds, but I didn't try to establish what it would go up to with OFFSET/MATCH or INDEX/MATCH as there were too many of them. My gut feeling is about 10-20 seconds, on 2003 on a normal-ish PC.

The model overall was about 200 worksheets, about 40,000 unique formulae, 400,000 non-unique formulae and about 35 Mb.
 
T

tess85

That's a massive model. Just checked the one I'm working on (not WW's, but client's one) out of curiosity, needs 15 spreadsheets, 90 MB altogether, most have to be opened at the same time......

WW, after having had a quick look at your problem, it's easily doable in SAS, any chance you have that installed? Excel isn't so great when it comes to what you're doing, especially pivot tables etc which are just plain annoying to use sometimes. Custom calc pivot tables are even more of a pain but hopefully there should be a workaround...
 
W

WinstonWolfe

tess85 said:
That's a massive model. Just checked the one I'm working on (not WW's, but client's one) out of curiosity, needs 15 spreadsheets, 90 MB altogether, most have to be opened at the same time......

WW, after having had a quick look at your problem, it's easily doable in SAS, any chance you have that installed? Excel isn't so great when it comes to what you're doing, especially pivot tables etc which are just plain annoying to use sometimes. Custom calc pivot tables are even more of a pain but hopefully there should be a workaround...
Click to expand...

Thx for your time again Tess.
I am oh so aware of the limitations of excel..... :)

Though the idea of doing it in xls was my accountants have always asked for it that way. And I am continuing to develop it into a generic anyone can use and understand thing.

If SAS was free and ubiquitous, I'd jump at it. Maybe they need a free 'lite' version to compete against excel...... imho, nothing confirms the slackness of Microsoft more than the development to date of excel.

I am out of office for rest of arvo.
 
W

WinstonWolfe

Tess, I am so losing confidence in p.tables.
The contextures running total example has significant limitations it seems.

It seems when I group mthly data into qtrs and years, the running total restarts with each qtr, rather than providing a running total from the top to bottom of the table.

Can't believe how clumsily restricted p.tables are.

As far as excel goes, I think developing more efficient vba to minimize worksheet read/writes and do the calcs in memory would be more flexible and a better investment of time. Once one wants to use excel for dynamic databases, excel reveals how hobbled and poorly developed it is. Naming and working with dynamic ranges (then charting them) should be a matured feature of excel, but it is still a time consuming pain to set it up.

Now, let's talk SAS and whether it can be used for application development :)
 
T

tess85

Yep - I agree with you re: clumsiness of pivot tables - they're really not suited to that purpose that you have proposed.

Personally I'd write a formula to calculate the cumulative totals outside of the pivot table, but linking to the cells in the pivot table eg. e1, e2, e3 etc. does that make sense?

Note that I only spent about 10 mins last night looking over the spdsheet, no new developments yet, should have more time on weekend though.

Re: SAS - what do you want to know? It's awesome for dealing with cashflows, the dataset I am dealing with has 6,000,000 transactions! (20 yrs worth of data) Cumulative stuff is a cinch, and it can handle dates easily too.

You can easily merge between all sorts of files (eg. master file and transaction file) and it's very speedy to run and use. Downside is that you'll need to learn SAS, but it's quite a nice procedural language to learn, and there are a lot of good books out there.
 
Somersoft has been archived - you can continue the discussion at PropertyChat
Top