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

1. ## 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. ## Re: average should be 0 if it exists in the used cells?

use something like this for values in B4:B12

HTML Code:

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

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. ## 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. ## Re: average should be 0 if it exists in the used cells?

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

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

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

8. ## 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. ## Re: average should be 0 if it exists in the used cells?

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

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