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