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

  • Thread starter Thread starter John McMurry
  • Start date Start date
J

John McMurry

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
 
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")
 
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")
Click to expand...

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 where:

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
 
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.
 
Back
Top