# Year to Date Average based on Todays Date

#### pete333

##### New Member
Hi

I need and average formula that looks at today month and then averages the results accordingly.

My data looks like this:

 Month Revenue January \$ 15,000.00 February \$ 37,000.00 March \$ 22,500.00 April \$ May \$ 109,100.00 June \$ 32,940.00 July \$ 28,875.00 August \$ 37,865.00 September \$ 62,683.33 October \$ November \$ December \$ Totals \$ 345,963.33 Averages \$ 28,830.28

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

You can see from my example, the standard average formula gives me the result based on all 12 months.

I need the formula to follow the following logic:

Calculate Average of data in Column B based on months in column A (January through to current month)

I think I would need to use a combination of Aveage, Match and Index and date

Thank you

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
This is what I tried, but it did NOT work:

=AVERAGEIFS(F7:F18,B7:B18,MATCH(TEXT(NOW(),"mmmm"),B7:B18,1))

Where F7:F18 is my range of numbers
B7:B18 are the months of the year
and the match was my attempt at getting it too look at today's month and less than to calculate the average

Sorry, but how do you get 28,830 as the averages ??
Can you explain the logic a little better ?

I would have thought the Average was 43,245 YTD

Sorry, but how do you get 28,830 as the averages ??
Can you explain the logic a little better ?

I would have thought the Average was 43,245 YTD

Here lies my problem:

1) average of just the months that have a result = \$43245
2) average of ALL months Jan - Dec including zero months = \$ 28830
3) average of Months from January to Current month... in this case Jan through Sept including zero month, = \$ 38440.37

I need a formula that generate ANSWER 3 based on the current month

Hope this makes sense

Thanks

Peter

Excel will always see April as a blank, not a zero month !!
Why don't simply put zero in April and average the whole 12 months !!!
Comes out to \$38k
Code:
``=AVERAGE(F7:F18)``

Excel will always see April as a blank, not a zero month !!
Why don't simply put zero in April and average the whole 12 months !!!
Comes out to \$38k
Code:
``=AVERAGE(F7:F18)``

Thanks, but that is what I originally started with. The average of all 12 months is different to the average of the months year to date.

Not if the rest are blank !!
Make April zero and leave the dates past today as blank and the Average will be \$38k
The Average function won't include blanks, even if they are in the selected range

Last edited:
Not if the rest are blank !!
Make April zero and leave the dates past today as blank and the Average will be \$38k
The Average function won't include blanks, even if they are in the selected range

My excel seems to work differently to yours! and zero is shown as \$- I cannot have some as zero and some as blank as the data is pulled from elsewhere based on formula.

Either way, I am wanting to use some sort of averageif parameter like sumifs.... my data is being pulled from many other sources with calculations behind the scenes, and updates automatically based on other criteria.

Okay, got this to work!

=AVERAGEIFS(F\$7:F\$18,\$A\$7:\$A\$18,"<="&\$A\$3)

Where f7:f8 is the values to be average
a7:a8 are numeric values 1 to 12 for each month
and A3 is the month value of current month.

so the formula says.... average all results from month 1 to current.

This is different from the standard Average calculation and allows for more dynamic updates.

I know its a little unusual, but works for me!

Thanks

Replies
3
Views
114
Replies
2
Views
108
Replies
5
Views
557
Replies
2
Views
122
Replies
3
Views
368

1,203,105
Messages
6,053,544
Members
444,670
Latest member
laurenmjones1111

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back