How to calculate an average based on number of days in a month

**rpclarke94** · 01-19-2020, 01:44 PM

Hi everyone,

I'm trying to do something which should be quite simple (I would have thought) but can't find any resources online.

I want to calculate an average number of sales for a given month. It seems easy enough to do this if you have a specific date, but what if I only have the month. For instance, in column A I would have the month (row 1 - January, row 2 - February etc...) and in column B I would have the number of sales for that month. Then in column C I want the average, so number of sales / number of days in that month. Does anyone know how I can do this please?

Many thanks,

Ryan

**Richard Buttrey** · 01-19-2020, 02:03 PM

Hi ,

One way with the date in A1 and the Sales in B1

Formula:

Please Login or Register  to view this content.

and if you have the month names in A1, January, February..etc and you want to SEE the names it would be better to put say 1/1/2020 in A1 as a date and format it to "mmmm"

**etaf** · 01-19-2020, 02:07 PM

=NETWORKDAYS(A1,EOMONTH(A1,0),holidays)
will give you the working days for each month - assuming you enter a date in A1 - ie 1/1/2020 and then format as MMM if you just want to display a month

now you have work days
then you can divide into B

=B1/NETWORKDAYS(A1,EOMONTH(A1,0),holidays)
should give the average for a workday in the month

Holidays are a named range for a table of when you have holidays in the month
no holidays just remove

=B1/NETWORKDAYS(A1,EOMONTH(A1,0))

**BMV** · 01-19-2020, 03:13 PM

Originally Posted by Richard Buttrey

Hi ,

Formula:

Please Login or Register  to view this content.

Could be more simple

Formula:

Please Login or Register  to view this content.

**rpclarke94** · 01-19-2020, 03:25 PM

Ok so I have to use a date. Thanks for your help.

**BMV** · 01-19-2020, 04:09 PM

you can generate the date but February should be used with year because of 28/29 days.
A1: "January"
C1: =B1/DAY(EOMONTH(--("1"&A1&"2020"),0))

How to calculate an average based on number of days in a month

LinkBack

Thread Tools

Rate This Thread

Display

How to calculate an average based on number of days in a month

Re: How to calculate an average based on number of days in a month

Re: How to calculate an average based on number of days in a month

Re: How to calculate an average based on number of days in a month

Re: How to calculate an average based on number of days in a month

Re: How to calculate an average based on number of days in a month

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] How to calculate and return exact number of days in a month based on criterias

[SOLVED] calculate the number of days in a month through 'Month Name'

[SOLVED] calculate total number of days between two days based on 365 days year

Running Average Based on Current number of days in month

[SOLVED] Calculate number of days in a month between two dates

[SOLVED] How to calculate an average for all days within a certain month

Calculate Average Number of Days based on Increase or Decrease of Price

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions