+ Reply to Thread
Results 1 to 8 of 8

Formula help; I want #NA to show so it doesn't show on graph

  1. #1
    Registered User
    Join Date
    05-16-2018
    Location
    Pittsburgh, PA
    MS-Off Ver
    2010
    Posts
    59

    Formula help; I want #NA to show so it doesn't show on graph

    I am using the sumif function and referencing from another sheet. If a month has not occurred then in the actual's a "0" will appear. The point is to automatically update the sheets which then leads to graphs in the workbook. Which is why if I could have "#NA" show up if a "0" is shown then the graphs wont be skewed. I have tried several ideas with another if statement and what not and cant figure out what to do. Hopefully someone is able to help me out.

    Formula being used: (is referencing correctly)

    =SUM(IF('H:\[12 MO ACTUAL.XLS]033300-020'!$A$9:$A$20=$A14,INDEX('H:\[12 MO ACTUAL.XLS]033300-020'!$A$9:$N$14,,COLUMN(B3))))

    Thanks,
    Ant

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    45,550

    Re: Formula help; I want #NA to show so it doesn't show on graph

    Using your formula as a base...
    =if(SUM(IF('H:\[12 MO ACTUAL.XLS]033300-020'!$A$9:$A$20=$A14,INDEX('H:\[12 MO ACTUAL.XLS]033300-020'!$A$9:$N$14,,COLUMN(B3))))=0,#N/A,SUM(IF('H:\[12 MO ACTUAL.XLS]033300-020'!$A$9:$A$20=$A14,INDEX('H:\[12 MO ACTUAL.XLS]033300-020'!$A$9:$N$14,,COLUMN(B3)))))

    (although Im not sure why you are using SUM there anyway care to share a sample file?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

    STOP PRESS: Forum Rules Updated September 2018! Please read them here.

  3. #3
    Registered User
    Join Date
    05-16-2018
    Location
    Pittsburgh, PA
    MS-Off Ver
    2010
    Posts
    59

    Re: Formula help; I want #NA to show so it doesn't show on graph

    Hello,

    I'm using the sum function because last time I tried something similar it ended up working out so I stuck with it when a similar project came up. Trying to learn different ways to mix in everything. But yes, I attached an excel document containing what I am doing.

    -Went ahead and moved the file over to the same workbook to make it easier (the file that I am referencing from)
    -In may and june of actual's you will see where it still shows on the graph. operating income, with formula; total direct expense, with a "0", POM with #N/A (doesnt show) which is what I would like it to have so I can just pull the updated actual's into a folder at it'll update all of tabs to latest month entered.

    Thanks,
    Ant
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    05-16-2018
    Location
    Pittsburgh, PA
    MS-Off Ver
    2010
    Posts
    59

    Re: Formula help; I want #NA to show so it doesn't show on graph

    Actually it works. Forgot to ctrl shift enter to set the array.

    If you know another way to do this besides using the sum function, please let me know.

    Thanks,
    Ant

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    45,550

    Re: Formula help; I want #NA to show so it doesn't show on graph

    If you use the exact same headings in both sheets, you just use this regular formula, because you really ant not adding anything, just pulling back a single value...

    =INDEX(Sheet2!$B$9:$N$24,MATCH(Sheet1!$A4,Sheet2!$A$9:$A$24,0),MATCH(Sheet1!B$13,Sheet2!$B$7:$N$7,0))

    To return NA...
    =IF(INDEX(Sheet2!$B$9:$N$24,MATCH(Sheet1!$A4,Sheet2!$A$9:$A$24,0),MATCH(Sheet1!B$13,Sheet2!$B$7:$N$7,0))=0,#N/A,INDEX(Sheet2!$B$9:$N$24,MATCH(Sheet1!$A4,Sheet2!$A$9:$A$24,0),MATCH(Sheet1!B$13,Sheet2!$B$7:$N$7,0)))

    Also, thanks for the feedback

  6. #6
    Registered User
    Join Date
    05-16-2018
    Location
    Pittsburgh, PA
    MS-Off Ver
    2010
    Posts
    59

    Re: Formula help; I want #NA to show so it doesn't show on graph

    Could you please explain in more detail, compared to the file I gave you? I am using the index match function you gave me now, and it keeps returning #na.

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    45,550

    Re: Formula help; I want #NA to show so it doesn't show on graph

    Sure, no problem
    1. That is a regular formula, no need for CSE
    2. The basic formula is
    B14=INDEX(Sheet2!$B$9:$N$24,MATCH(Sheet1!$A4,Sheet2!$A$9:$A$24,0),MATCH(Sheet1!B$13,Sheet2!$B$7:$N$7,0))
    what happens when you just enter that?

  8. #8
    Registered User
    Join Date
    05-16-2018
    Location
    Pittsburgh, PA
    MS-Off Ver
    2010
    Posts
    59

    Re: Formula help; I want #NA to show so it doesn't show on graph

    Oh okay got it.

    So when I enter the formula into cell B14 and drag it across it for the rest of the months it still returns the "#na" for all of the months.

    But also the formula is different from the previous one that you gave.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1