Share via

Average Function Help

Anonymous
2025-02-27T20:36:40+00:00

Hi everyone,

I had a quick question about the using the average function. I am trying to write a formula that averages cell G6:G53. The numbers, in those cells, are based off an average of their own. My issue is that when those cells are blank, they return an error of #DIV/0!. I know that means excel is trying to divide by 0 and it can't. I want excel to ignore that error and only average the cells that have a value greater than 0.

I've tried Average (G6:G53)

           AverageIF(G6:G53,"<>0")

           AverageIF(G6:G53,"<>")

Unfortunately, all those options are returning #DIV/0!.

Does anyone have a formula that will only average the cell values greater than 0?

Thanks,

Chris

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Rich~M 20,370 Reputation points Volunteer Moderator
    2025-02-27T21:02:00+00:00

    I believe this formula will do what you want to average only the cells that have a value and not include 0 or error cells. Formula is in I6 below.

    =AVERAGE(FILTER(IFERROR(G6:G53,0),IFERROR(G6:G53,0)<>0))

    1 person found this answer helpful.
    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2025-02-27T23:09:36+00:00

    Hi,

    Does this work?

    =aggregate(1,6,G6:G53)

    0 comments
  2. Anonymous
    2025-02-27T22:26:38+00:00

    Thanks Rich. That appeared to do that trick. I appreciate the help.

    0 comments