10 Replies Latest reply: Nov 13, 2012 7:19 PM by TyasHuybrechts
TyasHuybrechts Level 1 Level 1 (25 points)

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 (29,950 points)

    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

  • TyasHuybrechts Level 1 Level 1 (25 points)

    Jerold,

     

    The answer to that is simple. I could indeed use Excel to work with pivot tables, and I am doing so. But the reason why I want to switch to Numbers is because of its features for layout.

    For example, Excel has a limit of one table for one sheet. This automatically restricts one pivot table to one sheet. But, when I would want to see comparison between budget and reality for each class apart for income and expense and for each month, I will need about 24 different sheets with each a pivot table in.

    If I could create a similar feature in Numbers using formulas or other approaches, I could place multiple table in one sheet and have a better sight on the data.

     

    The source table I am refering to resembles the one I recreated below. Does this make it easier to imagine the problem?

     

    Code
    DateMonthDescriptionSortClassAmount
    Budget2012.1.1JanSalaryIncomeSalary2,599.00
    Budget2012.2.1FebSalaryIncomeSalary2,599.00
    .....................
    Budget2012.1.1JanLoan for the houseExpenseHouse1,000.00
    Budget2012.2.1FebLoan for the houseExpenseHouse1,000.00
    .....................
    Budget2012.1.1JanHealth careExpenseHealth80.00
    Budget2012.2.1FebHealth careExpenseHealth80.00
    .....................
    Budget2012.9.1SeptSchool fees kidsExpenseEdu100.00
    .....................
    Account2012.1.3JanSalaryIncomeSalary2,599.00
    Account2012.2.2FebSalaryIncomeSalary2,599.00
    Account2012.12.5DecPremiesIncomeSalary1,800.00
    .....................
    Account2012.1.1JanLoan for the houseExpenseHouse1,000.00
    Account2012.1.29JanLoan for the house (feb)ExpenseHouse1,000.00
    .....................
    Account2012.8.24AugCar trouble & reparationsExpenseVaria799.00
    .....................

     

    TH

  • Jerrold Green1 Level 7 Level 7 (29,950 points)

    Tyas,

     

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

     

    Jerry

  • TyasHuybrechts Level 1 Level 1 (25 points)

    Jerold,

     

    Thank you for your continuous replies.

     

    The pivot table problem is as I stated in my original post:

    TH wrote:

     

    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
    ClassBudgetAccount
    Class160.00057.899
    Class243.00045.243
    Class300
    Total103.000103.142

     

    And then the above table for each month for as well income as expense.

    So to restate the problem, Now I am creating these tables on one sheet each in Excel, but I want to be able to group them together on one sheet which improves visability and is a possibility in Numbers.

     

    What I want to retrieve in one table, based on the source table given in my former post, is the sum for "Budget" and "Account" (Code) separated per "Class". This in a table per "Month" and separate for "Income" and "Expense" (Sort).

     

    I am looking for a sollution through recreating a turntable like function, though if there are any other possible sollutions you can suggest, I wouldn't mind to adapt my strategy.

     

    I am wanting to make the best of Numbers because graphically it is great software especially because of its separate tables per sheet behaviour, but maybe it still lacks a bit of functionality as a spreadsheet. I hope we can find a sollution to my problem here.

     

    Regards,

    TH

  • Barry Level 7 Level 7 (29,455 points)

    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

  • TyasHuybrechts Level 1 Level 1 (25 points)

    Barry,

     

    No, the tables have no direct relation for what concerns their content. The product table is only a representation of how it should look like, but it's not a product from what I have provided in the source table.

     

    The source table itself has way more data in it than I have shown above.

     

    Please let me hear what you find.

     

    regards,

    TH

  • Barry Level 7 Level 7 (29,455 points)

    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

  • TyasHuybrechts Level 1 Level 1 (25 points)

    Barry,

     

    Okay, I have filled in the table with Data and names that come back in the source table above.

    It looks as follows:

     

     

    SortExpense
    MonthJanuari

     


    Data
    ClassBudgetAccount
    House1,000.001,000.00
    Health80.0079.99
    Edu100.0050.00
    Varia300.00799.00
    .........
    Total1,480.001,928.99

     

    I have had to add a few numbers because it is impossible to give all in the source table because it would take up too much space. The total in this table is correct for the numbers I provided, so don't get distracted by the "..." above the "total" row. They just mean there are supposed to be more classes.

    The data substracted from the source table here is all data for the Expenses in januari.

     

    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?

     

    TH

  • Barry Level 7 Level 7 (29,455 points)

    "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

  • TyasHuybrechts Level 1 Level 1 (25 points)

    Barry,

     

    Thank you very much, that is exactly what I have been looking for!

    I just kept troubling myself with ifs in ifs and trying to implement "sumif" in there as well, but it just didn't work out. If I only had known numbers had this easy new formula implemented in version '09 it would have saved me so much trouble.

     

    "SUMIFS" is what I needed.

     

    I am sorry for the inconsistency in the information I gave above. Thank you very much for finding the sollution!

     

    TH