Cumulative Monthly Average

[T86157]

Hi I am looking for a way to calculate the monthly average of data that is entered in for each following row. The part I am having a challenging time is summing the data for each month and year, then dividing it by the number of those months that data is entered it to find the monthly average. The raw data would be entered in columns B and C, E2 would be the monthly average as the desired outcome. Please let me know how this could be achieved!

Thanks,
Tyler

[Richard Buttrey]

How are you calculating the result 23.95?

The monthly avaerage for November is 26.225

[T86157]

Hi Richard,

This is the overall monthly average. Not for a specific month. Say you have data for November and December. I was trying to trying first find the average for the November, and finding the average for December. The final result would then be averaging November and December averages. On a larger scale, each month of each year is separate as part of the monthly calculation.
Maybe my verbiage is incorrect on saying monthly average but I am not wanting to calculate any particular month but the months that do have data entered and having that as the "monthly average".

Thanks,
Tyler

[T86157]

I think the term might be average monthly average.

[63falcondude]

The average of averages is only equal to the average of all values in two cases:

1) The number of elements of all groups is the same
or
2) All the group averages are zero

Neither is the case here.

[63falcondude]

Post #5 being said, if you understand that taking the average of averages doesn't give you a true average but still want to go about it this way, you can do this:

Select B1:C10 > Insert > PivotTable > Existing sheet: I1 > Drag Date into ROWS and remove Quarters > Drag Value into VALUES then change from sum to average
Remove the Grand Total by selecting Design > Grand Totals > Off for Rows and Columns

Then you can use =AVERAGE(J:J) to return the 23.95.

See attachment.

[Richard Buttrey]

Even given the answer in #3 I get 23.845 for the average of the averages for the 5 months you show and not the 23.95 you show.

[protonLeah]

double post???
https://www.excelforum.com/excel-for...y-average.html