Monthly data

Part 2…

After rebuilding your table and filling in random data, I did some exploration to determine what placing the four tokens into different categories.

I added a column (Yr & Mo) to extract the year and month of each date and lace them n a single column.

Having done that, and found myself no further ahead, I did a little more reading, and found that the (Year) was a specification that could be changed, as were the other specs in ( ) format. A little more exploration led me to this:

Adding the Yr&Mo column with the formula as shown entered in E2 and filled to the end of that column gave a set of values on which the table could be categorized, and on which a Pivot Table could be based.

The 'Year', "Month" and *Yr&Mo" columns are NOT needed in constructing the Pivot Table, which references only the the values in columns A and B (date and rainfall).

To create the Pivot Table:

Select the main table, then click the Pivot Table button in the tool bar.

Open the Pivot Options pane in the right side bar, then click the check boxed beside Date and Rain.

Thes will automatically populate the Rows and Values boxes. If they do go elsewhere, drag Date to Rows and Rain to Values.

Click on the circle at the right end of Date (Year) to open the options, then click the menu bar showing Year and select Year-Month.

Leave the Rain (Sum) token as is.

Results should be similar to mine (although the numbers wll be different).

Regards,

Barry

fotonut wrote:

I am still unable to organise the data to tally the monthly data using pivot table. Seems I still need a column which tallys the monthly sum. How shoul I formulate this in the table so that the pivot can display it?

The thing with Pivot Tables is that they are so powerful and flexible that step-by-step instructions cannot possibly cover every situation. There is no substitute for just experimenting until you "get the feel for them." Be sure to click on those circled i's ⓘ to see what they offer. That, I believe, is the answer here.

You can experiment without worry because Pivot Tables do not alter your original data table. And if you are "almost there" but want to see if you can make the result "even better" then just click the data table and start a new Pivot Table and compare that to your original Pivot Table. You can have multiple Pivot Tables based on your data table. You can quickly try different things and In the end just keep the one(s) you like best.

Remember to 'refresh' them if you add or change data in the original data table on which they are based. (Organize > Refresh Pivot Table or click the circular arrows.)

SG

I was wondering in your other thread if you meant you wanted to sum all the rainfall for all the January's for all the years, sum all the Februarys rainfalls, etc. Is that what you want, 12 sums? If so, I think all you need to do from what SGIII presented is change the pivot table "rows" to be "Date (month)" by clicking on the (i) next to "Date (year)".

Hi fotonut,

Haven't done much with Pivot Tables yet, so these are more observations than a solution.

Your Pivot Table here seems to be doing what you've told it to do, but not what you intended to tell it to do.

The first column tells it to group and sort the rows by date, using Year as the group size,

For the second column, you placed the Rainfall data token in the Values box, telling Numbers you wanted to do some calculations with that data. You accepted the default calculation of SUM, and the table correctly displays the total rainfall for each year on the row for that year, and the Grand Total for all years recorded in the Footer row of that column.

In the third column, you placed the token for this column in the Values box as you did with Rainfall. According to the information on the token, you want the sum of the data in this column for each year (and the grand total of all the values in this column in the footer row.

Note that, except for the partial year listed in the main table for the last year, all of these annual totals are one of the other of two values: 2384 or 2382. The two larger numbers appear in the rows for 2016 and 2020—both leap years, with one more row (day) in February than the others.

The light clicked on… The table is doing what it was told: SUM the numbers in the Month column, where the numbers are the month number of each day the rain was recorded.

What to do:

Click on the circle to the right of (Year) to open a menu.

And change it to the setting shown in the image above.

No time to go into further details, so do some experimenting. I'll try to return later to decribe and show what I did with the same data table setup as yours (but random data).

Regards,

Barry