How do i tell excell not to enter zero values into cells?

Question

Hi

I'm currently teaching myself excel and am working on formulas.

i have copied a formula into a range of cells that are dependant upon data in other cells.

when there is no data present, the answetr to the formula is 0

i have hidden this zero using conditional formatting eg if value = 0, then print in white - its still there - you just cant see it!

the problem is, when working with a range of data, i want to find an average value for the range - excell knows that the zeros are there and is ruining my average count

ok long hand over now short hand

what formula do i need to use to tell excell not to print the answer "0"

Answer 1

Instead of telling it to enter 0 for no value, have it enter a space. This will do the same thing as showing no result, but will not be included in an average calculation.

Your formula sounds like an If function , so using the structure

=If(condition,true,false) where true will be your value if there is one, and false is your zero. Enter a space in quotations (" ") as the last part and that should do the trick.

If the formula is different and you can't see where to change it, let me know the full formula.

EDIT:

Ok, it seems as if you could have used an if formula to only record a value if it is greater than zero, otherwise record a space. The formula would have been

=if(B4*(1/(1-(B3*0.01)))> 0,B4*(1/(1-(B3*0.01)))," ")

basically saying if the calculation is greater than zero then enter the calculation, otherwise enter a space. As a space has no numerical value it doesn't get included.

I like to use this method as it always you alter individual cells as you need to.

However, with the information you have since provided, I have to say that AI's answer below will definitely be easier to use now rather than editing many cells, and would probably have been my suggestion if I had found your edit sooner !

Either way, I hope it all works out for you.

Answer 2

Sounds like the SUMIF and COUNTIF functions would be best to use, saves having to ammend all the formulae to not show any zeros.

use

=SUMIF(range,">0")/COUNTIF(range,">0")

That will calculate the average, so you don't have to change the table of data.

for the ranges, the first is all the cells containing the discharge cycles, the second is the cells containing all the samples.

The range should be something like A1:B20.

Edit.

D'oh! I forgot about the AVERAGE function.

Just put

=AVERAGE(range)