Skip navigation

Can I recreate pivot tables using formulas?

672 Views 10 Replies Latest reply: Nov 13, 2012 7:19 PM by TyasHuybrechts RSS
TyasHuybrechts Level 1 Level 1 (25 points)
Currently Being Moderated
Nov 8, 2012 12:49 AM

Hello everyone,

 

I have searched for possible answers to my question. I checked out MacMost's video on using Categories as a replacement for pivot tables, I found out that it is impossible to actually work with the numbers in those Categories and I found that there are a few simple ways as SUMIF formulas to make some numbers workeable. Yet I failed to find find a real answer to my question. That is why I will ask it here again.

 

The possibilities you have with Categories and SUMIF formulas are limited. With SUMIF formulas you can give about one indicator to extract workeable totals, but pivot tables use more indicators. The table I want to use has up to five indicators and wanted to know if this functionality can be recreated in a numbers table with formulas.

 

For example I have a data list with following data:

Code | Date | Month | Description | Sort | Class | Amount

 

This is the working set for a budget / reality comparison table. Code is divided in Budget and Reality, Sort is split up in Income and Expenses, Class defines smaller calculating units.

 

I want to create separate tables for income/expense for each month, showing the sum for Budget and Reality aligned for each Class. One separate table should look something like this:

 

SortIncome
Month

Januari

 


Data
ClassBudgetReality
Class160.00057.899
Class243.00045.243
Class300
Total103.000103.142

 

And then the above table for each month for as well income as expense. Could you help me with finding a way to extract the data from the list and get hold of it in a table as the above. Is there a formula or various formulas to make this approach work?

 

I count on your help.

 

Regards,

TH

  • Jerrold Green1 Level 7 Level 7 (28,135 points)
    Currently Being Moderated
    Nov 8, 2012 7:13 AM (in response to TyasHuybrechts)

    If you know how to solve your problem with an actual Pivot Table, you probably would be better off using an application with that feature. If, however, the problem doesn't really need all the power of a full-blown pivot table, then there may be more options than the ones you have already explored.

     

    If you post an example of your source table it may be easier for us to imagine a solution.

     

    Jerry

  • Jerrold Green1 Level 7 Level 7 (28,135 points)
    Currently Being Moderated
    Nov 9, 2012 4:42 AM (in response to TyasHuybrechts)

    Tyas,

     

    Thanks for posting the sample data. What is the Pivot Table question / problem statement that you want to solve for?

     

    Jerry

  • Barry Level 7 Level 7 (29,095 points)
    Currently Being Moderated
    Nov 10, 2012 11:44 PM (in response to TyasHuybrechts)

    Hi TH,

     

    Either I'm misinterpreting your tables, or there is no relation between the sample data you presented November 8 and the result tables you presented November 10.

     

    Do these tables represent the same data set? If so, how does the second arise from the first?

     

    I've done a summary of January Income, using the sample data provided in your earlier post, but the number are far different from those in your second post, so I'm hesitant to muddy the waters by posting it here.

     

    Regards,

    Barry

  • Barry Level 7 Level 7 (29,095 points)
    Currently Being Moderated
    Nov 12, 2012 6:27 PM (in response to TyasHuybrechts)

    It's difficult to 'find' anything when presented with a set of sample data and an expected result with no discernable relation to that data.

     

    The data does not have to be 'real', but does have to correctly represent the relationship between input and output.

     

    Regards,

    Barry

  • Barry Level 7 Level 7 (29,095 points)
    Currently Being Moderated
    Nov 13, 2012 11:03 AM (in response to TyasHuybrechts)

    "Does this clear up my purpose with the tables I want to create and did it provide better understanding for the relation between source table and product table?"

     

    Muddies the waters, actually.

     

    The table(s) in your most recent post shows budgeted amounts and expenditures for January in all four listed categories. The source table shows budgeted January expenses for only House (1000) and Health (80), and Accounted January expenses only for House (two payments of 1000 each).

     

    Here is what I think you are looking for, but with results arising from the actual values in your source table. coloured fill in the cells is there only to show where the data filling the summary table comes from (and to indicate why some data has not been transferred to that table. The source table in named "Data", and this name appears in the formulas used.

    Picture 2.png

    The formula used is a SUMIFS statement with three conditions. All conditions must be TRUE for an amount to be included in the sum. The formula is entered in Expenses::B3 and filled right into B3 and down to row 7.

     

    Expenses::B3: =SUMIFS(Data :: $G,Data :: $C,"="&$B$1,Data::$A,"="&B$2,Data :: $F,"="&$A3)

     

    Row 8 is a Footer row.

     

    B8: =SUM(B)

    Fill right to column C.

     

    The Income table is a duplicate of Expenses, using the same formulas.

     

    Regards,

    Barry

Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.