I’m working with a Google Sheets formula that calculates average(B15:D15) but I keep getting a division by zero error when cells B15 through D15 are empty. This happens because the average function tries to calculate with no valid data.
What I really want is to compute the average only for cells that have actual numbers in them, not empty cells or zeros. I’ve heard there might be a way to do this with conditional functions or maybe some other approach, but I can’t figure out the right syntax.
Is there a clean way to handle this situation so my spreadsheet doesn’t show error messages? I just want it to either show the correct average or display nothing until there’s actual data to work with.
try using =if(counta(B15:D15)>0, average(B15:D15), ""). this way only averages non-empty cells, leaving a blank if there’s no data. it’s a neat fix!
Another approach that works well is wrapping your formula in IFERROR like =IFERROR(AVERAGE(B15:D15), ""). This catches division by zero errors and shows a blank cell instead. I’ve found this super useful with dynamic ranges where you might have partial data. IFERROR handles not just empty cells but any calculation errors, so your spreadsheet stays clean even when weird stuff happens. You can swap the empty string for whatever you want - “No data” or a dash work great.
AVERAGEIF is perfect for this. Try =AVERAGEIF(B15:D15,">0") - it’ll only average cells greater than zero, so empty cells and zeros get ignored automatically. Want to include negative numbers but skip empties? Use =AVERAGEIF(B15:D15,"<>") instead. The “<>” means “not blank.” Way cleaner than nesting a bunch of IF statements, especially with larger ranges where you’ve got empty cells scattered around.