AVERAGEIFS formula - adjust to exclude values of 0

[joshdog13]

I have a formula in column R of the example sheet that average returned values for multiple criteria being true in another tab. The formula works fine except I do not want it to average any 0's. For example the cell in the row for "Derek" should return an average of $15,345 if the one 0 he has is not included in the average.

[arnelgp]

can you use the Formula:

=IF(VALUE(O26)=0, "",IF(O26="-","",IF(O26>=0,NUMBERVALUE(O26,".",","))))

in "XA Data Pull" sheet, Column Q.

[joshdog13]

No I don't want to tell the formula in column Q to read the 0's in column O as blank as I need to build a formula in column T next to count the 0's in Q. Any idea why the current formula is not excluding the 0's? It has "<>0" in it currently which I thought would exclude them.

[arnelgp]

Try it first (i already did) and you will see the result.

[Bob Phillips]

I tried yours arnelgp, and it still threw an #VALUE! error, but a small adjustment worked here

=IF(ISERROR(VALUE(O26)),"",IF(O26="-","",IF(O26>=0,NUMBERVALUE(O26,".",","))))

and if the OP wants to count the zeros, he should count them in column O not in column Q, use the source data.

[arnelgp]

#Values! is caused by "-" from the Cells.

what I have noticed on that Sheet, is that the $ sign is entered Manually and importantly the "-" (for blank entries).
if you do a simple Sum() to O column it will result into 0 (because of those Text).

the OP need to remove those "-" from column O and just Format the Cells (that will show "-" instead of blank).

if you do these suggestions the New Formula will work.

[Bob Phillips]

Agree that that was the cause, but who knows how the data gets there, it might be imported and beyond the OP's control, it's impractical to edit the received data.

[arnelgp]

Why would it be impractical? Your the consumer. Like i said even a simple sum on the column will result to 0. That's what brain and common sense for.

[Bob Phillips]

Why would it be impractical? Your the consumer. Like i said even a simple sum on the column will result to 0. That's what brain and common sense for.

If there are thousands of rows, maybe received daily, with multiple data discrepancies, it is impractical to expect the consumer to correct it. That will not be what they are paid to do. Either correct it at source, or handle it.

[arnelgp]

It is Expected to Clean the data first.
whether you are clerk or high in stature.
obviously it is the OP job, because he is here
for some enlightenment.
Money is involved here, not just anything.
you don't want the employee barging on you because
of wrong computations.
btw these kind of computations are not hidden to the public.
they are also given to the concerned so they can compute it
for themselves.

if you are like that employee who don't care whether there is
an anomalies on your worksheet, and the boss expect that
you can be left unsupervised (because you know how to use excel), then
goodbye career.

if you think you cannot clean the data because it will tamper the workbook,
you secure a copy for your own.

like in my office, at the end of the month each employees photocopy
their timecards, so they know the amount of hours they have worked
for that month.

also, did you noticed that Colum Type, it is number (currency), so
it is expected that either blank or currency amount is on that column.

[georgiboy]

Dodgy data is my job, it is what I deal with every day. It is not feasible to ask for better data as it comes from companies bigger than ourselves. For this reason I go with the "Deal with it" statement from Bob's post above. I believe it is the skill of an 'Analyst' (my job, could be other jobs) to deal with it and provide accurate results regardless.

I know the response I would get if I said to my boss "hmmm I cant do this as the data is dodgy"

I personally will not cleanse the data if I can get accurate stats without cleaning, it is only when that becomes difficult that I will cleanse the data before I gather any stats from it.

Just my thoughts as I have made a career from other peoples poor data.

[arnelgp]

Some people are responsible therefore reliable.
Others are just payroll personnel, worthless.

[georgiboy]

Some people only see things from their point of view therefore blind to the views from others.

[Bob Phillips]

Some people are responsible therefore reliable.
Others are just payroll personnel, worthless.

Wow, insulting three of us with one statement! I've seen your contributions, I have seen georgiboy's contributions, and I know mine. It's pretty clear to me that the last two are worth more.

[Aussiebear]

Alright, this thread is no longer moving in a good direction and is therefore closed.