Ask Me Help Desk

Ask Me Help Desk (https://www.askmehelpdesk.com/forum.php)
-   Spreadsheets (https://www.askmehelpdesk.com/forumdisplay.php?f=395)
-   -   Average (https://www.askmehelpdesk.com/showthread.php?t=462675)

  • Apr 5, 2010, 12:12 PM
    santoshdream
    1 Attachment(s)
    Average
    Hi,

    I am working on a spread sheet but unable to come up with the formula... here is what I want from the formula.

    Lets assume, in Col A; A to E are the various categories and in Col B, there is a score (0, 3, 6 & 9), In col C I need the average of Col B but if any of the score is "0" then the formula should not do the average but give "0" as a result. Attached file with some more details...
    A
    B
    C
    D
    E
  • Apr 5, 2010, 02:04 PM
    JBeaucaire

    Put this in C2:

    =IF(COUNTIF($B$2:$B$7, ">0")=0, 0, SUM($B$2:$B$7) / COUNTIF($B$2:$B$7, ">0"))
  • Apr 21, 2010, 04:16 AM
    WvR

    The formula =IF(COUNTIF($B$2:$B$7, ">0")=0, 0, SUM($B$2:$B$7) / COUNTIF($B$2:$B$7, ">0")) only solves the problem not to include a zero value in the average calculation
    The formula =IF(MIN($B$2:$B$7)=0, 0, AVERAGE($B$2:$B$7)) will not calculate an average if any of the values is zero but will rather display "0". This is if all other values are positive
  • Apr 21, 2010, 01:44 PM
    JBeaucaire

    Thanks for the correction WvR, I did misread the question. Unfortunate use of the disagree button for me, but it was wrong...
  • Apr 22, 2010, 03:32 AM
    WvR

    Sorry about that, I did go through some of your other answers and I am really impressed with your work.

  • All times are GMT -7. The time now is 09:18 PM.