1

I am trying to average a percentage commission range on another sheet. On sheet Lines Sales column K I can enter a percentage commission charge if there is one. More frequently I don't have a commission as I am selling my own property.

Through my various searches on here I found that Excel should ignore blank cells in this function and if I want to ignore zeros I should format like this:

=AVERAGEIF('Line Sales'!K2:'Line Sales'!K150,"<>0")

In using this formula with no commissions entered I get a #DIV/0! error. As soon as I enter a commission percentage on a line I get a valid result. I plan to start a new workbook each year to track what I do but I don't want to spend possibly months looking at that error until I get a commission sale.

What I hope to accomplish is to average only commission percentages from K2 to K150 on the Line Sales sheet and have this formula entered into Totals sheet B13, if there are no commission sales I'd like to see it remain blank or be zero.

David Glickman
  • 713
  • 1
  • 11
  • 18
BeasleyBiz
  • 11
  • 1
  • 2
  • Also relevant there should be no zeros, only blank or a percentage in the cells. – BeasleyBiz Mar 31 '17 at 03:30
  • =AVERAGE('Line Sales'!K2:'Line Sales'!K150) still gives the #DIV/0! error. – BeasleyBiz Mar 31 '17 at 03:31
  • I don't know what is the problem. In my Excel 2010, if some cell is blank `average()` ignores it and if some cell is zero `average()` includes it. No `#DIV/0!` error. Please provide a sample Excel file. – Sangbok Lee Mar 31 '17 at 06:24

1 Answers1

1

#DIV/0! error is the expected result for AVERAGEIF if there is nothing to average (it's basically doing the calculation 0/0), so just wrap in an IFERROR function to make the result blank or zero, e.g.

=IFERROR(AVERAGEIF('Line Sales'!K2:K150,"<>0"),"")

barry houdini
  • 45,615
  • 8
  • 63
  • 81