Averaging Multiple columns based on Weeknum compared to Month

[zdonner]

I'm stumped on this one.


=AVERAGEIFS($H$3:$J$27,$G$3:$G$27,(TEXT(($G$3:$G$27*7)-6,"mmmm")=O3))

This will be performed for every month so O changes for its chart as it is showing Monthly yield.

• Column H, I and J are Averaged Yields.
• Column G is Weeknum
• Column O is Month as Text (IE July)

What I need is where G = July, AVERAGE.

Currently it returns #VALUE!

The issue is clearly the Weeknum to Month comparison but I don't know of a way to make this work, TEXT((G3*7)-6,"mmmm")=O3 returns TRUE if used on its own but I need to find any/all instances where it is true.


Thanks in advance.

[daddylonglegs]

There are two things wrong with this:

1.) AVERAGEIFS requires all the ranges to be the same size, so you can't have the average range 3 columns wide while the criteria range is a single column

2.) The criterion in AVERAGEIFS needs to be a single condition, not a range of values as returned by TEXT(($G$3:$G$27*7)-6,"mmmm")=O3

Try an array formula like this

=AVERAGE(IF(TEXT($G$3:$G$27*7-6,"mmmm")=O3,$H$3:$J$27))

confirmed with CTRL+SHIFT+ENTER

Note that if, for example, G10 meets the criterion then H10, I10 and J10 will all be added to the average calculation, I assume that's how you want it to work?

[zdonner]

You got it exactly right, thank you so much it worked like a champion.