Always refer formula to Grand Total row in pivot table

Posted on 2007-08-01
Medium Priority
Last Modified: 2012-08-14
I have a pivot table that produces cash flows for various categories of investments (with date in each column heading) and I want to produce an IRR of the "Grand Total" (i.e. bottom) row of the pivot table.  How do I refer the XIRR formula to the bottom row of the pivot table, no matter how many rows the table has?  My attempts so far all leave a reference to a defined row, which is useless when the number of rows changes.

Question by:DavidMorton
LVL 81

Expert Comment

ID: 19610898
You can return a range of cells using the INDEX and MATCH functions:
=INDEX($C$1:$K$1000,MATCH("Grand Total", $A$1:$A$1000,0))            returns columns C:K on row that has label "Grand Total" in column A

You can create a dynamic named range using the above formula, then use that in your XIRR formula.

Expert Comment

ID: 19610928
have you tried using the GETPIVOTDATA function?

i.e., =GETPIVOTDATA("Pivot Column",$A$5) where Pivot Column is the grand total column you want to return and $A$5 is the pivot table reference
LVL 93

Expert Comment

by:Patrick Matthews
ID: 19611245
Hello DavidMorton,

Brad answered your question.  That said, I urge you to be very careful in using IRR.  IRR
is, simply put, not reliable, and prioritizing investment decisions on an IRR basis can
lead to bad decision-making.

1) Depending on the nature of the cash flows, there can actually be more than one IRR
2) Some scenarios can have very high IRR but actually have negative NPV using any
rational discount rate.

Illustrating both...  Consider the following cash flows:

Today, -1,600
1 year from now, 10,000
2 yrs from now, -10,000

This stream actually has two IRRs: 25%, and 400%, both of which sure look attractive.  Yet,
any reasonable discount yields a negative NPV.

Bottom line: trust NPV, and eschew IRR.



Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.


Author Comment

ID: 19612947
To All,
Thank you for your comments.  I fear that I over-simplified the problem, so here goes again.
The pivot table is produced from  a data spreadsheet listing every cash flow item with date, amount, company to which it relates (there are many), sector and a couple of other cayegories that are irrelevant for this question.
The pivot table itself has one column for each date on which a cash flow occurred and a grand total column at the right-hand end and a row for each company with a grand total row at the bottom.
So to produce an IRR (I am fully aware of the technical limitations of this) using XIRR I need the following data :
(i) the row containing the total cashflows (i.e. the whole of the bottom row of the pivot table except the final column, which is the horizontal sum of these amounts.  The number of columns changes depending on the pivot table selections.;
(ii) the dates corresponding to each cash flow, which are contained in the top row of the table (again excluding the far right column) (the row does not change but, as in (i) above, the number of columns depends on the pivot table selctions); and
(iii) a guestimate of the solution, which is simple to include in the XIRR formula.

So what I cannot work out how to do is how to refer the XIRR formula to :
(i) a range which is the bottom row of the pivot table (variable row number) through to the far right of the table except the absolute last item  (a variable number of columns); and
(ii) a range which is the top row of the pivot table (row number fixed but variable number of columns) again through to the far right of the table except the absolute last item.

I tried recording a macro to automate the creation of named ranges and I can easily include the navigation commands but Visual Basic converts these into references to specific ranges, rather than running the macro anew and re=specifying the appropriate ranges each time.

Any tips more than gratefully received.

LVL 81

Accepted Solution

byundt earned 500 total points
ID: 19613196
Dynamic named ranges can return variable numbers of columns as well. I omitted the starting point for the dynamic named range. I've included it in the formulas below.

Grand Total row data starting in column B, but excluding last column
=INDEX($B$1:$B$1000,MATCH("Grand Total", $A$1:$A$1000,0)):INDEX($B$1:$IV$1000,MATCH("Grand Total", $A$1:$A$1000,0),COUNTA($B$4:$IV$4)-1)

Title row data, starting in column B, but excluding last column. Assumed to lie in row 4.

Sample workbook showing dynamic named ranges in action with a PivotTable: https://filedb.experts-exchange.com/incoming/ee-stuff/4224-DynamicGrandTotalRowQ22735153.zip


Author Comment

ID: 19615777
Thanks Brad.  

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question