Year to Date Average based on Todays Date

[pete333]

Hi

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

My data looks like this:

[TABLE="width: 504"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Month
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Revenue
[/TD]
[/TR]
[TR]
[TD]January
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] $ 15,000.00[/TD]
[/TR]
[TR]
[TD]February[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] $ 37,000.00[/TD]
[/TR]
[TR]
[TD]March[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] $ 22,500.00[/TD]
[/TR]
[TR]
[TD]April[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] $
[/TD]
[/TR]
[TR]
[TD]May[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] $ 109,100.00
[/TD]
[/TR]
[TR]
[TD]June[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] $ 32,940.00
[/TD]
[/TR]
[TR]
[TD]July[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] $ 28,875.00
[/TD]
[/TR]
[TR]
[TD]August[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] $ 37,865.00
[/TD]
[/TR]
[TR]
[TD]September[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] $ 62,683.33
[/TD]
[/TR]
[TR]
[TD]October[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] $
[/TD]
[/TR]
[TR]
[TD]November[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] $
[/TD]
[/TR]
[TR]
[TD]December[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] $
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Totals[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] $ 345,963.33
[/TD]
[/TR]
[TR]
[TD] Averages [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] $ 28,830.28
[/TD]
[/TR]
</tbody>[/TABLE]

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

Please help ?

Thank you

 

[pete333]

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

 

[Michael M]

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

 

[pete333]

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:

There are 3 possible answers:

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

 

[Michael M]

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

=AVERAGE(F7:F18)

 

[pete333]

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

=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.

 

[Michael M]

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

 

[pete333]

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.

 

[pete333]

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