Currently Being ModeratedNov 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.
Currently Being ModeratedNov 8, 2012 7:48 PM (in response to Jerrold Green1)
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 Date Month Description Sort Class Amount Budget 2012.1.1 Jan Salary Income Salary 2,599.00 Budget 2012.2.1 Feb Salary Income Salary 2,599.00 ... ... ... ... ... ... ... Budget 2012.1.1 Jan Loan for the house Expense House 1,000.00 Budget 2012.2.1 Feb Loan for the house Expense House 1,000.00 ... ... ... ... ... ... ... Budget 2012.1.1 Jan Health care Expense Health 80.00 Budget 2012.2.1 Feb Health care Expense Health 80.00 ... ... ... ... ... ... ... Budget 2012.9.1 Sept School fees kids Expense Edu 100.00 ... ... ... ... ... ... ... Account 2012.1.3 Jan Salary Income Salary 2,599.00 Account 2012.2.2 Feb Salary Income Salary 2,599.00 Account 2012.12.5 Dec Premies Income Salary 1,800.00 ... ... ... ... ... ... ... Account 2012.1.1 Jan Loan for the house Expense House 1,000.00 Account 2012.1.29 Jan Loan for the house (feb) Expense House 1,000.00 ... ... ... ... ... ... ... Account 2012.8.24 Aug Car trouble & reparations Expense Varia 799.00 ... ... ... ... ... ... ...
Currently Being ModeratedNov 10, 2012 8:44 AM (in response to Jerrold Green1)
Thank you for your continuous replies.
The pivot table problem is as I stated in my original post:
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:
Sort Income Month
Data Class Budget Account Class1 60.000 57.899 Class2 43.000 45.243 Class3 0 0 Total 103.000 103.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.
Currently Being ModeratedNov 10, 2012 11:44 PM (in response to TyasHuybrechts)
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.
Currently Being ModeratedNov 11, 2012 10:02 PM (in response to 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.
Currently Being ModeratedNov 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.
Currently Being ModeratedNov 13, 2012 5:07 AM (in response to Barry)
Okay, I have filled in the table with Data and names that come back in the source table above.
It looks as follows:
Sort Expense Month Januari Data Class Budget Account House 1,000.00 1,000.00 Health 80.00 79.99 Edu 100.00 50.00 Varia 300.00 799.00 ... ... ... Total 1,480.00 1,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?
Currently Being ModeratedNov 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.
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.
Fill right to column C.
The Income table is a duplicate of Expenses, using the same formulas.
Currently Being ModeratedNov 13, 2012 7:19 PM (in response to 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!