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

Question

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

Answer 1

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.

Answer 2

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)),"")))

Answer 3

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.

Answer 4

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