Numbers Pivot Table or similar

Question

zanlo Author

Level 1

8 points

Numbers Pivot Table or similar

Hi Everybody!

First of all, Happy New Year!

Let's talk about Numbers (4.0.5), I'm working on a document that compute some statistics about my walks.

The document is based on a template for runners that I've found in Numbers and modified.

Now, I have a table with the following columns:

- DATE (which represent the day)

- NUMBER OF STEPS

- DISTANCE in KM

whit those data it is possible to elaborate statistics about every day activity.

I can create statistics including whole data, but i would like to create monthly stats.

To do so it should be useful to have a function similar to an Excel Pivot Table, but I didn't find anything similar in Numbers. Do you have some suggestions?

Thanks,

Lorenzo

😁

MacBook Pro, macOS Sierra (10.12.2)

Posted on Jan 4, 2017 2:22 AM

Reply

Answer 1

Best reply

Yellowbox

Level 7

20,305 points

Jan 4, 2017 4:11 AM in response to zanlo

Hi Lorenzo,

Some imaginary data, not covering every day, but to illustrate.

Add another column (Column B, Monthname) to your table

formula in B2 (and Fill Down)

=MONTHNAME(MONTH(A2))

In another table (Monthly Totals) type the month name into column A as Text.

Now SUMIF is your friend

Formula in B2 (and Fill Down, then Fill Right)

=SUMIF(Table 1::$B,$A2,Table 1::C)

Regards,

Ian.

Reply

Answer 2

zanlo Author

Level 1

8 points

Jan 4, 2017 7:07 AM in response to Yellowbox

Unfortunately I need your help again.

I worked on your answer and it works, but i figured out the problem of the years.

To solve it, I've used AVERAGEIFS which allow me to set up more conditions, sadly the result is this:

(Sorry for the italian headers, but I'm working in italian)

The results that are computed are correct, I don't know why others reports a problem.

Do you have some suggestions?

Thanks,

Lorenzo

Reply

Answer 3

Yellowbox

Level 7

20,305 points

Jan 4, 2017 10:48 PM in response to zanlo

Hi Lorenzo,

AVERAGEIF and SUMIF work the same way.

AVERAGEIFS and SUMIFS work the same way, but the order is different to AVERAGEIF and SUMIF.

Type = in a spare cell to bring up the Function Browser.

Type the first few letters of a function name in the search box.

Note the different order of arguments for AVERAGEIFS

Regards,

Ian.

p.s. keep practice and you will learn Italian soon!

Lesson 1. Ciao means both hello and goodbye 😉.

Reply

Answer 4

Yellowbox

Level 7

20,305 points

Jan 5, 2017 1:10 AM in response to zanlo

Ciao Lorenzo,

Formula in C2 (and Fill Down)

=MEDIA.PIÙ.SE(Table 1::D;Table 1::$B;$A2;Table 1::$C;$B2)

Formula in D2 (and Fill Down)

=MEDIA.PIÙ.SE(Table 1::E;Table 1::$B;$A2;Table 1::$C;$B2)

The red error triangles are because Table 1 is not complete. Some months are missing. MEDIA.PIÙ.SE can not find a match, and can not divide by zero

2. Use ; not , in formulas.

Saluti,

Ian.

Reply

Answer 5

Yellowbox

Level 7

20,305 points

Jan 4, 2017 8:04 AM in response to zanlo

Ciao Lorenzo,

(Sorry for the italian headers, but I'm working in italian)

Please forgive my poor Italian!

Si prega di perdonare il mio povero italiano!

Mese Anno Media Passi Media Distanza

Month | Year | Media Steps | Average Distance

What are you trying to do?

Saluti, and Happy New Year!

Ian.

Reply

Answer 6

zanlo Author

Level 1

8 points

Jan 4, 2017 10:22 PM in response to Yellowbox

Ciao Ian!

Sorry for the late answer.

What I'm trying to do is a auto-updating table that automatically elaborates montly stats starting from the list of raw data.

So, what I would like to have at the end is a table A with a very long list of data where I can input raw data easily, and a tableB where the data are automatically categorized by month and year which reports monthly stats.

how does AVERAGEIFS should work?

for what I know, this function should work as SUMIF, as you told me, but instead of calculating the sum of the values, it account for the mean value. Moreover, the function allow to set more than one factor of discrimination.

hoping to be clear enough,

Ciao,

Lorenzo

p.s. keep practice and you will learn Italian soon!

Reply

Answer 7

Barry

Level 9

59,861 points

Jan 4, 2017 10:57 PM in response to zanlo

Hi Lorenzo,

Cold you post a copy of the AVERAGEIFS formula used above, and a screen shot of the cells involved in one of the calculations that succeeded and the adjacent calculation that failed? That might provide some clues as to why it's sometimes working and other times not.

Regards,

Barry

Reply