Consulting

Results 1 to 15 of 15

Thread: AVERAGEIFS formula - adjust to exclude values of 0

  1. #1

    AVERAGEIFS formula - adjust to exclude values of 0

    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.
    Attached Files Attached Files

  2. #2
    can you use the Formula:

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

    in "XA Data Pull" sheet, Column Q.

  3. #3
    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.

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

  5. #5
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,415
    Location
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  6. #6
    #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.

  7. #7
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,415
    Location
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  8. #8
    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.

  9. #9
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,415
    Location
    Quote Originally Posted by arnelgp View Post
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  10. #10
    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.

  11. #11
    VBAX Expert
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    680
    Location
    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.
    If things don't change they stay the same
    Quite often there is a picnic problem (problem in chair not in computer)
    "We were not told it was impossible, so we did it."

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

  13. #13
    VBAX Expert
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    680
    Location
    Some people only see things from their point of view therefore blind to the views from others.
    If things don't change they stay the same
    Quite often there is a picnic problem (problem in chair not in computer)
    "We were not told it was impossible, so we did it."

  14. #14
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,415
    Location
    Quote Originally Posted by arnelgp View Post
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  15. #15
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,017
    Location
    Alright, this thread is no longer moving in a good direction and is therefore closed.
    Remember To Do the Following....
    Use tags when posting code to the thread,
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •