# Calculate the Monthly Average based on the current day

1. ## Calculate the Monthly Average based on the current day

Hi there! I am trying to calculate the average number of widgets sold per month based on the current day.

A1 = YTD Sale of Widgets
B1 = Average Monthly Production of Widgets

I am currently using the following formula in B1: =A1/(MONTH(TODAY()))

This worked fine in January, and would work fine on the last day of February, though it skewed the Average Sale of Widgets on February 1st to think there were 2 whole months to calculate the average....

I tried using the following formula in B1: =A1/(MONTH(TODAY())-1)

This skews results though that I've added for sales in the current month to think it should take the total YTD Widget Sales and divide by 1 (for only counting January since we are in February).

Is there a forumula that could calculate the Average Monthly Sales based on the Day of of the year? Thanks in advance for any help you can provide!  Register To Reply

2. ## Re: Calculate the Monthly Average based on the current day

I would suggest that if you are in the middle of a month you should subtract the number sold in that month (obtained as the YTD number divided by the number of days so far, times the day number for the month), and then divide the remaining amount by the number of full months that you have encountered so far - you will need to treat January as a special case. If you agree with this logic, then your formula will be something like:

=IF(MONTH(TODAY())=1,A1,(A1-A1/(TODAY()-DATE(YEAR(TODAY()),1,1))*DAY(TODAY()))/(MONTH(TODAY())-1))

Hope this helps.

Pete  Register To Reply

3. ## Re: Calculate the Monthly Average based on the current day

Perfect! Thank you Pete!  Register To Reply