Search results

don't mind having a go at SAS. I've never formally studied a language but am logical enough to have picked up VBA to an intermediate level, in my down time.

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...

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...

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)*...

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. Several years ago Ben, I looked into what would be the fastest method. Sumproduct was considered by many to be the most efficient...

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)...

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...

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).

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.