Excel Chart

[John]

I am developing a chart on Excel. I have two columns
with numbers. A third column has a formula to average
the two numbers. I want to graph the average. Problem
is my graph looks bad. If I enter the two numbers, they
are averaged in the third column and graphed correctly.
The problem is Excel interprets rows that have not been
entered as zero and graphs them as zero.

I would like rows that have not yet have data entered
into them to be treated as if there is no data and thus
not be graphed.

Hope someone can help me? I need this for a project I
working on to graph the pH of a pond.

thanks
John

 

[Debra Dalgleish]

Use the NA function in the formula, and the cells won't be plotted as
zero. For example:
=IF(AND(B2="",C2=""),NA(),AVERAGE(B2:C2))

Your worksheet will show #N/A errors which you can hide with conditional
formatting. There are instructions here:

http://contextures.com/xlCondFormat03.html#Errors

 

[Jerry W. Lewis]

Use

=IF(COUNT(A2:B2)=0,#N/A,AVERAGE(A2:B2))

for your average column.

Jerry

 

[John Pfeiffer]

Thank you. I will give this a try.

John

-----Original Message-----
Use the NA function in the formula, and the cells won't be plotted as
zero. For example:
=IF(AND(B2="",C2=""),NA(),AVERAGE(B2:C2))

Your worksheet will show #N/A errors which you can hide with conditional
formatting. There are instructions here:

http://contextures.com/xlCondFormat03.html#Errors


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

.

 

[John Pfeiffer]

Thank you. I will give this a try too.

John

-----Original Message-----
Use

=IF(COUNT(A2:B2)=0,#N/A,AVERAGE(A2:B2))

for your average column.

Jerry


.