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

1. ## 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. ## 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?

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

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

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

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