# Returning the average of 12 cells, if they have value more that 0 in it

I need to calculate holiday pay taking the average from the previous 12 weeks. If any of the week has no value in it and is showing 0 value, this week should be excluded from the calculation and an extra week included. So it takes always 12 weeks of pay. This is an ongoing formula.

The weekly wage is in cells B1:P1 (15 weeks of pay). 2 weeks have 0 value (F1 and M1), so should be excluded and 2 further weeks included.

So the formula should return the average of the latest 12 cells with value in them. So basically should return the average of the total value from cells C1,D1,E1,G1,H1,I1,J1,K1,L1,N1,O1,P1.

For the following week it must return value from D1,E1,G1,H1,I1,J1,K1,L1,N1,O1,P1,Q1 AND SO ON.

Kristina

• Please provide, what have you tried.
– ZygD
Apr 1, 2021 at 10:27
• I was working out the average manually so far. Apr 1, 2021 at 10:34

If you want to avoid using the volatile OFFSET function, you can use:

=AVERAGE(INDEX(2:2,1,AGGREGATE(14,6,ISNUMBER(1/(B2:P2))*COLUMN(B2:P2),SEQUENCE(12))))

If you do not have the SEQUENCE function, you can use:

=AVERAGE(INDEX(2:2,1,AGGREGATE(14,6,ISNUMBER(1/(B2:P2))*COLUMN(B2:P2),ROW(INDEX(\$A:\$A,1):INDEX(\$A:\$A,12)))))

Both formulas take the rightmost twelve columns that contain a non-zero numeric value.

If your range might extend past column P, merely change the range B2:P2 to something that is sure to encompass the potential used range. eg B2:XFD2 as a maximum will still work.

• This formula works wonderful. Thank you very much!!!!!! Apr 17, 2021 at 16:52
• @Kristina Glad to help. Since my answer seems to be what you require, kindly mark it as Accepted. See What should I do when someone answers my question. Apr 18, 2021 at 0:25

I have found a more robust solution and it looks intimidating but it breaks into chunks:

=AVERAGE(IFERROR(INDEX(FILTER(SORTBY(\$B1:P1,COLUMN(\$B1:P1),-1),SORTBY(\$B1:P1,COLUMN(\$B1:P1),-1)<>0),SEQUENCE(12,1,1,1)),""))

Let's break that into pieces, starting with the SORTBY() function that appears twice:

SORTBY(\$B1:P1,COLUMN(\$B1:P1),-1)

Note that the first part of the reference is fixed at \$B1 so that won't update as we copy/paste to the right. If we put this formula in P2 and copy/paste to the right, we'll always be looking at the range from B1 to whatever column we're on now. In short, all the data so far. Now, we're going to sort that data in reverse order (that's what the -1 does) by sorting it by the column numbers in descending order. Let's re-write the formula as a shorter version now that this piece has been discussed:

=AVERAGE(IFERROR(INDEX(FILTER(SORTBY(~),SORTBY(~)<>0),SEQUENCE(12,1,1,1)),""))

Next, focus on the INDEX(FILTER(),SEQUENCE()) setup. The FILTER() function takes that sorted array from above and filters out all the zero values. It'll treat blanks as zero so those are covered, too. Now we have an array of all non-zero values in reverse order. By combining that with INDEX() and SEQUENCE(), we can pull out just the first 12 values from that list. Combining all that means we now have the 12 most recent non-zero values from the range B1 to whatever. After you pull out those bits, the rest is pretty simple:

=AVERAGE(IFERROR(INDEX(~,""))

If there aren't at least 12 non-zero values, INDEX() returns an array with some error values in it. We use IFERROR() to replace those errors with blanks so AVERAGE() can work. The only way you should see an error now is if there aren't any non-zero values.

If you prefer, the LET() functions can save you from having to repeat things by giving them names. This is the same function as above but the SORTBY() mess has been pulled out:

=LET(sortedRange,SORTBY(\$B1:P1,COLUMN(\$B1:P1),-1),AVERAGE(IFERROR(INDEX(FILTER(sortedRange,sortedRange<>0),SEQUENCE(12,1,1,1)),"")))
• The formula Averageif worked well, but was counting 12 weeks in the direction from left to right. Would it be possible to adjust this formula, so it would take last 12 weeks going to the direction from right to left. so lets say for this week it would calculate columns P:C (total count 14, but 2 cells with 0 value, so total count 12 weeks). For next week it would count Q to D and so on. Apr 1, 2021 at 14:48
• @Kristina I went the wrong direction but, in investigating a solution, I found something much better. Apr 1, 2021 at 19:35
• I am sorry for late reply, I am going to check it all now. Just want to help you for all your help sorting this out. I really appreciate that. Apr 16, 2021 at 9:35

Use this macro

Sub average()

Dim sum As Long
Dim count As Long
Dim column As Integer
Dim row As Integer
Dim row_count As Integer

Let row_count = 2

For row = 1 To row_count
Let sum = 0
Let count = 0
Let column = 2
While count < 12
If Cells(row, column).Value > 0 Then
sum = sum + Cells(row, column).Value
count = count + 1
End If
column = column + 1
Wend
Cells(row, 1).Value = sum / 12
Next row

End Sub

It prints average value in first cell. You can change it by editing Cells(row, 1).Value = sum / 12
If you have multiple rows, just change row_count value.
It searches for first 12 valid (non-zero) values and then calculates the average.

• Thank you for the macros, but I am a bit unfamiliar with this function and how it works. Apr 1, 2021 at 14:51
• in Excel, go to view tab then click macros and select view macros. Then create a macro and paste this code and run it. Column "A" will be filled with each row's first 12 non-zero value's average. Apr 1, 2021 at 19:49

There is a certain beauty to this question. On paper, this is so easy - just average the past 12 numbers but skip any zeros. In Excel it demands that you really think up some complicated formulae to count zeros and sort them and exclude them.

Part of why this is so challenging is because this is actaully an iterative or recursive problem and could potentially go on forever (given enough past data and enough zeros). With old-school excel functions you can't approach this problem counting from the first/most recent number (like a human or turing machine would), but you have to consider all the numbers and exclude zeros and then take the last 12. At least until December 2020 that is when Excel became turing complete and introduced the LAMBDA function!

If you are running office 365, define a new name (Formulas > Define Name) as "SumNZ12" using a recursive LAMBA function:

=LAMBDA(i, ref, IF(ref=0, SumNZ12(i, OFFSET(ref, 1, 0)), IF(i=11, ref, ref + SumNZ12(i+1, OFFSET(ref, 1, 0)))))

SumNZ12 counts and sums backwards 12 non-zero numbers. Use it like this:

=SumNZ12(0, C1) / 12

Full disclosure: I have not tested this yet. There may be a chance the ref argument is passed as a number and not a variable in which case the formula should adjusted so that ref is a text argument and is used with indirect(ref).