Can I recreate pivot tables using formulas?

Question

Can I recreate pivot tables using formulas?

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:

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:

Sort	Income
Month	Januari

Data
Class	Budget	Reality
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. 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

Posted on Nov 8, 2012 12:49 AM

Reply

Answer 1

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

Reply

Answer 2

Nov 8, 2012 7:48 PM in response to Jerrold Green1

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

TH

Reply

Answer 3

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

Reply

Answer 4

Nov 10, 2012 8:44 AM in response to Jerrold Green1

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:

Sort Income

Month
Januari

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.

Regards,

TH

Reply

Answer 5

Barry

Level 9

59,861 points

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

Reply

Answer 6

Nov 11, 2012 10:02 PM in response to Barry

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

Reply

Answer 7

Barry

Level 9

59,861 points

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

Reply

Answer 8

Nov 13, 2012 5:07 AM in response to Barry

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?

TH

Reply

Answer 9

Barry

Level 9

59,861 points

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.

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

Reply

Answer 10

Nov 13, 2012 7:19 PM in response to Barry

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

Reply