+ 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
    5

    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,669

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

    use something like this for values in B4:B12

    HTML Code: 
    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 365 (Windows)
    Posts
    5,936

    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
    5

    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,669

    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
    5

    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 365 (Windows)
    Posts
    5,936

    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 365 (Windows)
    Posts
    5,936

    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
    5

    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)

Similar Threads

  1. [SOLVED] Taking the average of multiple groups of cells with ability to extend range of average
    By 2597Gar in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-29-2019, 11:07 AM
  2. [SOLVED] VBA to search for value and if it exists copy text from 4 cells into new cells
    By smudgers9 in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 02-13-2019, 01:03 PM
  3. HOW CHECK FILE.NAME HAS EXISTS OR NOT IN FOLDER,if not exists then copy
    By daboho in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-13-2018, 03:50 AM
  4. Combine multiple text cells if value exists in other cells
    By epoch555 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-22-2014, 03:50 PM
  5. Replies: 7
    Last Post: 10-30-2013, 01:12 PM
  6. The rank of average ranks excluding empty cells but including their average.
    By Terminal45 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-21-2012, 03:44 AM
  7. Replies: 2
    Last Post: 04-13-2010, 08:46 PM

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