Always refer formula to Grand Total row in pivot table

Posted on 2007-08-01
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 80

    Expert Comment

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

    Expert Comment

    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 92

    Expert Comment

    by:Patrick Matthews
    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.



    Author Comment

    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 80

    Accepted Solution

    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:


    Author Comment

    Thanks Brad.  

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    Suggested Solutions

    Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
    This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
    The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

    728 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

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now