Search code examples
excelexcel-formulaaverage

Average for last 6 cells which are greater than 0

I have the challenge that my list will get every month new data. I need now to get the average of the last 6 months (where the cell is bigger than 0). My list gets every month new data and I need to get the average calculated for the last 6 months.

Example: In January I need the average in cell V2 of the last 6 months (means Jul-Dez column K-P) or the last 6 cells which are bigger than 0 in column H-S In February I need the average in cell V2 of the last 6 months (means Aug-Jan column L-Q) or the last 6 cells which are bigger than 0 in column H-S

Can someone help me?

Example list

enter image description here

I tried this:

=AVERAGE(TAKE(FILTER(H2:S2,ISNUMBER(H2:S2)),-6))

But that gives me not the result I need.

I have now tried this formula from @rory =AVERAGE(TAKE(FILTER(H2:S2,H2:S2>0),-6))

BUT when I add new data into Jan then I get a different average result (see picture below

enter image description here

Solution

  • The following assumes that your columns are H:S and that your column headers are real EOM dates that you show formatted as mmm-dd

    • we create a list of the columnNames so as to be able to detect the position of the column name that is one month before TODAY()
    • then, using CHOOSECOLS create a list of all the entries in the row up to last month
    • FILTER that row to remove any zero's, then average the last six (6).
      • If there will never be no zero's in the months to be examined, the formula can be simplified

    Formula edited as column headers are "real dates" at EOM and not text strings

    =LET(
    cols, Sheet3!$H$1:$S$1,
    lastCol, XLOOKUP(EOMONTH(TODAY(), -1), cols, cols),
    chkRng, CHOOSECOLS(Sheet3!$H2:$S2, SEQUENCE(, XMATCH(lastCol, Sheet3!$H$1:$S$1, 0))),
    notZero, FILTER(chkRng, chkRng <> 0),
    avg, AVERAGE(TAKE(notZero, , -6)),
    avg
)