Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 19 of 19

Thread: Graph Gripes

  1. #11
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,648
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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?

  2. #12
    Board Regular
    Join Date
    Mar 2002
    Location
    Massachusetts, USA
    Posts
    255
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

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

  3. #13
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,648
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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

  4. #14
    Board Regular
    Join Date
    Feb 2002
    Location
    Brisbane, Down Under
    Posts
    542
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  5. #15
    Board Regular
    Join Date
    Mar 2002
    Location
    Massachusetts, USA
    Posts
    255
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  6. #16
    Board Regular
    Join Date
    Feb 2002
    Location
    Brisbane, Down Under
    Posts
    542
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  7. #17
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,648
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default


    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

  8. #18
    Board Regular
    Join Date
    Mar 2002
    Location
    Massachusetts, USA
    Posts
    255
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  9. #19
    Board Regular
    Join Date
    Mar 2002
    Location
    Massachusetts, USA
    Posts
    255
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!!!!

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •