= function

[Steve]

I'm trying to create a data sheet to compute more than 50 cells that are not
adjacent for an average. I use the data sheet to make sure the cells I need
to compute are adjacent. I use the = function to get all these cells with
the numbers I want adjacent to each other. This makes the numbers appear
where I want them but the cells with nothing in them come back as 0 instead
of blank, throwing off the average I need. Is there anyway to make the zero
appear as blank or as text? either would work for my purposes.

 

[Ken Wright]

See replies in worksheet.functions

 

[Dave Peterson]

You can change that = formula to something like:

=if(sheet3!a1="","",sheet3!a1)

 

[Franksta]

You can use an "if" command.

Something like this "=if(original_cell=0,"",original_cell)"

Basically, this says that if the original cell is equal to zero to return a
blank or as excel shows it in a formula "". If it is not equal to zero, to
return the value of that cell.


Hope that helps.

 

[CLR]

Hi Steve..........

You might consider creating a Non-Contiguious Range of the cells you wish to
average and use something like

=AVERAGE(MyNonContiguousRange)

or

=SUM(MyNonContiguousRange)/COUNTA(MyNonContiguousRange)

or

=SUM(MyNonContiguousRange)/COUNT(MyNonContiguousRange)


Vaya con Dios,
Chuck, CABGx3

 

[Steve]

Thanks Dave, that really worked!

You can change that = formula to something like:

=if(sheet3!a1="","",sheet3!a1)