Log In
Register
Home
July 11, 2017
Offline
Hi
The following formula finds the average of the selected weeks, but I want the average to exclude weeks that are zero?
=AVERAGE(INDIRECT(ADDRESS(ROW(),MATCH($V$1,$2:$2,0),3,1),1):INDIRECT(ADDRESS(ROW(),MATCH($W$1,$2:$2,0),3,1),1))
Any help greatly appreciated.
Thanks Paul
See Answer
July 16, 2010
Offline
What version of Excel are you working with, Paul? Hopefully 365.
If you have 365 for Group 1:
=IFERROR(AVERAGE(FILTER(F257:S257,($F$256:$S$256>=$V$1)*($F$256:$S$256<=$W$1)*F257:S2570)),"")
If you have Excel 2019 or earlier for Group 1:
=IFERROR(SUMPRODUCT(G257:S257*($G$256:$S$256>=$V$1)*($G$256:$S$256<=$W$1)*(G257:S2570))/SUMPRODUCT(($G$256:$S$256>=$V$1)*($G$256:$S$256<=$W$1)*(G257:S2570)),"")
Note: You should avoid using INDIRECT at all costs.
Mynda
Answers Post
July 11, 2017
Offline
Thanks Mynda
I do use 365, Worked perfectly,
Out of interest why not use INDIRECT?
Regards
Paul
July 16, 2010
Offline
INDIRECT is a volatile function, which can put enormous strain on Excel's calc engine resulting in slow workbooks. Volatile functions recalculate almost every time anything in your workbook changes, as opposed to only calculating when cells the formula is dependent on change, like regular functions. For that reason I only use it as a last resort, especially if the formula is going to occupy many cells.
July 11, 2017
Offline
Thanks Mynda
July 11, 2017
Offline
Hi
Formula worked fine until Week number changes to start with 2, then result is wrong?
I can't work out what has gone wrong< any help greatly appreciated
Thanks
Paul
Trusted Members
Moderators
November 1, 2018
Offline
In an Alphanumeric comparison, the comparison works left to right, character by character. So W2 is greater than W18 because it compares the 2 to the 1, not to 18. You could simply format your single digit weeks to use a leading 0 - i.e. W01, W02, W03 etc.
1 Guest(s)
