Help with averaging a column with blanks

BigMike365

trying to average a column that will ultimately be used for a report however I am getting the #DIVIDEBYZERO error and the community notes haven't helped. I was able to write a formula to leave all zero values blank, now I am trying to average the column… little help please.

=AVG([Cost/Mile Customer]:[Cost/Mile Customer)

Victoria_Indimar

@BigMike365 Is there at least one value populated already for Cost/Mile Customer? And if so, are all entries numbers?

BigMike365

Yes & Yes… most of the fields have numbers. Here's a snip of what I am trying to build for clarity 🤞

VBAGuru

=Sum([Cost/Mile Customer]:[Cost/Mile Customer])/Count([Cost/Mile Customer]:[Cost/Mile Customer])

VBAGuru

@BigMike365 Your AVG should work, but if that formula above is your exact formula, then it is missing a ] at the end just before the final )!

=AVG([Cost/Mile Customer]:[Cost/Mile Customer])

VBAGuru

Other question is what is the formula to get those amounts in that column? If they are seen as text, then it won't average.

BigMike365

Thanks for your response VBAGuru! Unfortunately, I am having the same issue, #DIVIDEBYZERO with both your formula and mine (I forgot to include the last ] but it is in fact in there)

The formula that I used for the Cost/Mile Customer column is

=IF(Customer@row/Distance@row=0,"",Customer@row/Distance@row)

What I was trying to do is average the column in question, but ignore the "No bids" (Basically I didn't get a response in ordering a truck so the value is 0)

BigMike365

Do you have any other suggestions?

florian.zbinden7

hi,

have you tried to N/A the empty cells with your average calculation formula, instead of "" put "N/A" ?

and possibly use ISBLANK and/or IFERROR ?