Hey everyone! I’m having trouble with my Google Sheets spreadsheet. I’ve got a column that’s supposed to calculate the average of cells K23 to M23 using average(K23:M23). The problem is, when those cells are empty, I get that annoying ‘#DIV/0!’ error.
I’m wondering if there’s a way to make the average only consider cells that actually have numbers in them (not zero or blank). I’ve heard the query command might help, but I’m not sure how to use it for this.
Does anyone know a good solution? It’d be great if you could share an example formula that works. Thanks in advance for your help!
I’ve dealt with this issue quite a bit in my work, and I’ve found a couple of reliable ways to handle it.
One approach that’s worked well for me is using the AVERAGEIF function. It lets you specify a range and only include cells that meet certain criteria – in this case, cells that aren’t blank.
Here’s a formula I’ve used successfully:
=AVERAGEIF(K23:M23, “<>”, K23:M23)
This tells Sheets to average only the cells in K23:M23 that aren’t empty. It’s been a lifesaver for me, especially when dealing with data sets where empty cells are common.
Another trick I’ve picked up is wrapping your average function in an IFERROR statement. It’s a bit clunkier, but it works:
=IFERROR(AVERAGE(K23:M23), “”)
This will display a blank cell instead of the #DIV/0! error if all cells are empty. Hope this helps!
I’ve found that using the ARRAYFORMULA function in combination with AVERAGE can be quite effective for this situation. Here’s a formula that has worked well for me:
=ARRAYFORMULA(IF(COUNTA(K23:M23)=0, AVERAGE(IF(K23:M23<>“”,K23:M23))))
This approach checks if there are any non-empty cells in the range, and if so, it calculates the average only for those cells. It’s particularly useful when you’re dealing with larger datasets or when you need to apply the same logic across multiple rows.
Another benefit of this method is that it’s dynamic - if you add more columns to your range in the future, you won’t need to modify the formula. It’s been a real time-saver in my projects.