+ Reply to Thread
Results 1 to 9 of 9

average should be 0 if it exists in the used cells?

  1. #1
    Registered User
    Join Date
    10-01-2019
    Location
    Turku, Finland
    MS-Off Ver
    office 365 proplus subscription
    Posts
    4

    average should be 0 if it exists in the used cells?

    Hi everyone
    I'm doing a school task where i need to calculate the average but give 0 as answer if 0 is a value in a cell that is being included in the the formula, how do I do this? tried for an hour with nothing but errors as results...

    Any help would be greatly appreciated!

    #rookieexceluser

  2. #2
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,598

    Re: average should be 0 if it exists in the used cells?

    use something like this for values in B4:B12

    HTML Code: 
    =IF(MIN(B4:B12)=0,0,AVERAGE(B4:B12))
    1. Click on the * Add Reputation if you think this helped you
    2. Mark your thread as SOLVED when question is resolved

    Modytrane

  3. #3
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2003, 2010, 2016 (Windows)
    Posts
    4,311

    Re: average should be 0 if it exists in the used cells?

    Either
    =IF(COUNTIF(cells,0)>0,0,AVERAGE(cells))
    or
    =IF(MIN(Cells)=0,0,AVERAGE(cells))


    where cells is the range of values used, like B2:M2. I have assumed that the values are contiguous.
    Bernie Deitrick
    Excel MVP 2000-2010

  4. #4
    Registered User
    Join Date
    10-01-2019
    Location
    Turku, Finland
    MS-Off Ver
    office 365 proplus subscription
    Posts
    4

    Re: average should be 0 if it exists in the used cells?

    Thanks for the replies, but I got a pop up saying theres a problem with the formula in all 3 cases... why is that? shoul i write the formula instead of copy pasting it and just change the cell references to what i want?

  5. #5
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,598

    Re: average should be 0 if it exists in the used cells?

    Since it seems like you are learning you should write the formula and use correct cell references.
    All three formulas suggested should work.

  6. #6
    Registered User
    Join Date
    10-01-2019
    Location
    Turku, Finland
    MS-Off Ver
    office 365 proplus subscription
    Posts
    4

    Re: average should be 0 if it exists in the used cells?

    I've tried but it won't work... Attachment 643561

  7. #7
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2003, 2010, 2016 (Windows)
    Posts
    4,311

    Re: average should be 0 if it exists in the used cells?

    For every formula, replace all the , with ; (Commas with semicolons)

  8. #8
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2003, 2010, 2016 (Windows)
    Posts
    4,311

    Re: average should be 0 if it exists in the used cells?

    You likely use semicolons instead of commas as the argument separators, so try this:

    =IF(MIN(Cells)=0;0;AVERAGE(cells))

    And you may need to use a different language for IF MIN and AVERAGE - this is an English language site and English versions are assumed.

  9. #9
    Registered User
    Join Date
    10-01-2019
    Location
    Turku, Finland
    MS-Off Ver
    office 365 proplus subscription
    Posts
    4

    Re: average should be 0 if it exists in the used cells?

    The semicolon worked, thank you modytrane and Bernie Deitrick for your help!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1