excel – How to calculate a monthly average when the month is not finished

excel – How to calculate a monthly average when the month is not finished

Divide the current total by the total number of days in the month that have passed so far. Then multiply by the total number of days in the month.

If you are only looking for a projection for the year I would recommend this:

Divide the sum you have for the current year * 365 so far in the data by days to date. That will give you a cheap sum.

sum * 365 / total days to date

Ideally you could figure out the ratio of revenue and you have a seasonal business:

Last year to date / last year total = Last year percentage to date

This year to date / Last year percentage to date : would project the average for the full year.

excel – How to calculate a monthly average when the month is not finished

Easy 2-step process:

I have my data in two columns: Date, # of Widgets Sold

  1. Add a 3rd column Month, and use the month and year function to return the month for each date (format cells as a number) (e.g., =MONTH(A2)&/&YEAR(A2) )
  2. Generate a pivot table using the # of Widgets Sold and Month columns that sums/averages the data by month

Leave a Reply

Your email address will not be published.