Excel 2007 – how to add a average formula to that cell/s that ignoring 0%’s

185 pts.
Tags:
Excel 2007
Microsoft Excel
Microsoft Excel formulas
Okay I figured out how to ignore zero %'s for a graph. Now what I'm stuck on is how to add a average formula to that cell/s This is what I had:
=IF(SUM(U171,U122,U74,U27)=0,#N/A,(SUM(U171,U122,U74,U27),(AVERAGE(U171,U122,U74,U27)) 
Thanks,
Jared

Answer Wiki

Thanks. We'll let you know when a new response is added.

How about this?

=IF(SUM(U171,U122,U74,U27)=0,#N/A,AVERAGE(U171,U122,U74,U27))

Is that what you mean?

(This question is related to this one: Excel 2007 – How do I ignore zero %’s for a graph?)

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • SbElectric
    If you want to find the average for all 4 cells (and excluding if sum is 0) then the suggestion by Carlsdt should do the trick. But if you want to exclude if any of the cells contain “0” then you need to do some more coding. Easiest may be to count the number of cells containing zero & then dividing by appropriate number. Let us know your actual specifications/requirements.
    2,540 pointsBadges:
    report
  • Kccrosser
    Assuming you can define an Excel range containing the cells, such as "myRange", then: sum(myRange)/countif(myRange, ">0") will yield the average, ignoring zeros. However, your formula (sum(range) / average(range)) is mathematically equal to the count of the cells, and (sum(range) / (sum(range) / average_excluding_zeros(range))) is just the count of non-zero cells. Is this what you are trying to compute?
    3,830 pointsBadges:
    report
  • SbElectric
    Yes. Carlosdl, kccrosser, and myself are thinking along the same line. In my last post, I enquired for more specification – have not seen any. Assume data are in cells A6 thru A11. Then the following code (say in Cell A13) will provide the needed answer. =IF (SUM(A6:A11)=0,"N/A",SUM(A6:A11)/(COUNT(A6:A11)-COUNTIF(A6:A11,0)))
    2,540 pointsBadges:
    report
  • Findsarfaraz
    Just to add to this.. There is Averageif() Function which is similar to countif which will useful in such scenarion. Sarfaraz Ahmed MS Excel help
    1,395 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following