If cell is left blank, or equals zero, then cell equals a different cell
 

I'm working on a relatively easy spreadsheet and just can't figure
this one out.

I'd like the average of the column (which I already have a cell for)
to represent any cell in which the user doesn't enter a number, or
enters zero.

Also, I'd like this cell to be differentiated (highlighted, different
color font, etc.) so as to know when this condition is used.

Many thanks,
John

If cell is left blank, or equals zero, then cell equals a differen
 

Say we have a column of values that may contain either blanks or zeros. We
want to average only the non-zero, non-blank values:

=SUM(A:A)/COUNTIF(A:A,"0")


--
Gary''s Student - gsnu200714


"John McMurry" wrote:

I'm working on a relatively easy spreadsheet and just can't figure
this one out.

I'd like the average of the column (which I already have a cell for)
to represent any cell in which the user doesn't enter a number, or
enters zero.

Also, I'd like this cell to be differentiated (highlighted, different
color font, etc.) so as to know when this condition is used.

Many thanks,
John

If cell is left blank, or equals zero, then cell equals a differen
 

On Apr 12, 10:08 am, Gary''s Student
wrote:
Say we have a column of values that may contain either blanks or zeros. We
want to average only the non-zero, non-blank values:

=SUM(A:A)/COUNTIF(A:A,"0")


Yes, however, that isn't what I'm asking. I apologize if I wasn't
clear.

I want to know the formula for a cell whe

data will be entered by the user and if the user leaves the cell
blank, or inputs zero, then the cell will equals another cell in the
worksheet

AND

the cell color or font will reflect the conditional situation.

Thanks,
John

If cell is left blank, or equals zero, then cell equals a diff
 

Now I understand. Let's say we have a table of values from A1 through A9.
We want the average, but if any of the values in the table is either blank or
zero, then use the contents of B1 instead for that value.


For example: if B1 contains 13 and the table contains:

1
2
3
4
5
6
7
8
9
then the average should be 5, but if the table is:

1
2
3
4
0
6
7
8
9
then the average would be 5.888888889
since we would use the 13 in place of the 0

The formula is:

=(SUM(A1:A9)+(9-COUNTIF(A1:A9,"0"))*B1)/9

This can easily be adjust for any table size or location and any substitute
cell.

To highlight the cells use Conditional Formatting and test for the cell
value equal 0 or blank.
--
Gary''s Student - gsnu200715