Excel - rolling average across columns without zeros, no array

0
votes

I am currently using this formula to calculate a rolling average score across 12 columns for either the last 3 or 6 months.

=SUM(SUMIFS($E$54:P54,$E$54:P54,LARGE(IF($E$54:P54>0,$E$54:P54),{1,2,3})))

This is an array formula and is entered via CTRL + SHIFT + ENTER.

The problem now is that I need to deploy my work book on older machines and those being ancient office computers (we are talking windows XP and Office 2003...), I find that the array is killing the entire workbook. Now, I have already taken steps to speed up the workbook via VBA (disabling events, manual formula calc, etc.), but I need a way to convert the above array formula into a non-array formula which is NOT counting zeros or empty cells as part of the average.

I tried this below but couldnt get it to work with the zeros / empty cells. =SUM(OFFSET($E68,0,COUNT($E68:$P68)-IF(COUNT($E68:$P68)>3,3,COUNT($E68:$P68)),1,IF(COUNT($E68:$P68)>3,3,COUNT($E68:$P68))))

Picture of the sample data attached below. data

2
arraysexcelexcel-formulavba

2 Answers

0
votes

Since an Average is a "Sum" divided by a "Count", this can be accomplished using the simplicity of non-Array formulas.This formula avoids the inclusion of both zeros and blank cells:

=IF(SUM(A2:C2)>0,SUM(A2:C2)/(COUNT(A2:C2)-COUNTIF(A2:C2,0)),0).

If the row is filled with only zero values, then it shows the actual numerical average as zero, which is correct.If you want to avoid the display of zero averages in cells, then use this slightly different formula:

=IF(SUM(A3:C3)>0,SUM(A3:C3)/(COUNT(A3:C3)-COUNTIF(A3:C3,0)),"").

Of course, you will need to adjust the cell ranges for the 6 month and YTD averages; these formulas deal with 3- month ranges.

0
votes

For last 3-Month range average: =SUM(OFFSET($A3,0,COUNT($A3:$L3)-IF(COUNT($A3:$L3)>3,3,COUNT($A3:$L3)),1,IF(COUNT($A3:$L3)>3,3,COUNT($A3:$L3))))/(COUNT(OFFSET($A3,0,COUNT($A3:$L3)-IF(COUNT($A3:$L3)>3,3,COUNT($A3:$L3)),1,IF(COUNT($A3:$L3)>3,3,COUNT($A3:$L3))))-COUNTIF(OFFSET($A3,0,COUNT($A3:$L3)-IF(COUNT($A3:$L3)>3,3,COUNT($A3:$L3)),1,IF(COUNT($A3:$L3)>3,3,COUNT($A3:$L3))),0))

For last 6-month average: SUM(OFFSET($A2,0,COUNT($A2:$L2)-IF(COUNT($A2:$L2)>6,6,COUNT($A2:$L2)),1,IF(COUNT($A2:$L2)>6,6,COUNT($A2:$L2))))/(COUNT(OFFSET($A2,0,COUNT($A2:$L2)-IF(COUNT($A2:$L2)>6,6,COUNT($A2:$L2)),1,IF(COUNT($A2:$L2)>6,6,COUNT($A2:$L2))))-COUNTIF(OFFSET($A2,0,COUNT($A2:$L2)-IF(COUNT($A2:$L2)>6,6,COUNT($A2:$L2)),1,IF(COUNT($A2:$L2)>6,6,COUNT($A2:$L2))),0))

These are quite lengthy, but they exclude the zero value cells from your original non-Array formula.The data was assumed to begin in row A2:L2.