CODEWITHSUNDEEP
excelif-statementaveragezerononblank
BeasleyBiz
BeasleyBiz

Reputation: 11

Averageif cell(s) not blank

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.

Upvotes: 1

Views: 3526

Answers (1)

barry houdini
barry houdini

Reputation: 46331

#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"),"")

Upvotes: 1

Related Questions