Graph Gripes

[MR STAT]

Having real problems with representing blank data in line charts. Need standard line graphs but when there is no data for a particular x axis point I want the line to break and I don't want the line dropping to zero. Does anyone know a way I can represent the blank data without deleting the formula that brings back the blank cell.

 

[Aladin Akyurek]

On 2002-05-03 09:03, nancyo wrote:
No, both c51 and g51 are formulas which return NA if there is no data, or else they return an average on a column of values.

Care to post the formula that is in C51 as well as the one G51?

 

[nancyo]

The formulas in C51 and G51 are the same, except the g is swapped with the c:

=if(istext(c25), "NA",(average(c25:c49)))

 

[Aladin Akyurek]

On 2002-05-06 13:41, nancyo wrote:
The formulas in C51 and G51 are the same, except the g is swapped with the c:

=if(istext(c25), "NA",(average(c25:c49)))

It should be:

=IF(ISTEXT(C25),#N/A,AVERAGE(C25:C49))

However, I have a problem with this, considering what you said in the previous post:

No, both c51 and g51 are formulas which return NA if there is no data, or else they return an average on a column of values.

Maybe you're looking for:

=IF(COUNT(C25:C49),AVERAGE(C25:C49),#N/A)

Aladin

 

[SamS]

Aladin, I found that using NA() instead of #NA caused me less problems in formulas. I am using 97 with NT4 which probably explains it.

 

[nancyo]

1. What is the difference between using "NA" and #NA?

2. How does the count formula differ from what I am using?

3. I will create a dummy spreadsheet and try your suggestion...

4. I have used the type of formula above in many spreadsheets, works well. Does not appear to work with graphing, why?

5. I am apparently pretty stupid with all this...

 

[SamS]

On 2002-05-07 05:09, nancyo wrote:
1. What is the difference between using "NA" and #NA?

nancy using "NA" returns the text of NA where as NA() is as follows:
Returns the error value #N/A. #N/A is the error value that means "no value is available." Use NA to mark empty cells. By entering #N/A in cells where you are missing information, you can avoid the problem of unintentionally including empty cells in your calculations. (When a formula refers to a cell containing #N/A, the formula returns the #N/A error value.)

Syntax

NA( )

Remarks

· You must include the empty parentheses with the function name. Otherwise, Microsoft Excel will not recognize it as a function.
· You can also type the value #N/A directly into a cell. The NA function is provided for compatibility with other spreadsheet programs.

 

[Aladin Akyurek]

1. What is the difference between using "NA" and #NA?

#N/A is a "reserved value" like built-in function names that Excel immediately recognizes and knows what it means.

2. How does the count formula differ from what I am using?

Your formula just checks the first cell (C25) of the range (C25:C49) that you feed to the AVERAGE function. The formula with the COUNT test says that if there is at least one number in the target range, then compute the average, otherwise return #N/A. Note that I asked you whether this was what you intended with "no data". Yours doesn't check for that.

3. I will create a dummy spreadsheet and try your suggestion...

Good idea. That would clarify things we probably didn't consider yet...

4. I have used the type of formula above in many spreadsheets, works well. Does not appear to work with graphing, why?

Which formula are you referring to?

Aladin

 

[nancyo]

OK - I am working with the count formula today. I have so many formulas referring to many other formulas, that this will take me a while to think through.

Thanks for all the help so far, I will reply with questions ASAP.

 

[nancyo]

OK - the count formula works fine, and I figured out how to adapt my formulas. This now works the way I wanted.

THANKS YOU GUYS!!!!!!!

BTW - reading and re-reading the excel help and books I have isn't nearly as helpful as this website!!!!