How to eliminate #N/A or replace it?

dennisli828

New Member
Joined
Jul 10, 2011
Messages
9
So my boss ask me to find the data for S&P 500 in different time frame and put it on separate sheets, he also want me to make a main page so that he can choose different time frame with a button, and the corresponding sheet will pop out. I have been trying to figure this out with nested IF but excel said my function exceeded the limit. So I decide to just using the array formula and show "daily" if he chooses "daily". Of course, I highlight the entire column but say for annual data, the length is much shorter and therefore there will be alot of #N/A s. Nobody know how to deal with that?I wanna leave it blank or get rid of it. I have copied my formula, any suggestions? (daily, weekly etc are the names for the data table)

={IF($B$1="daily",daily,IF($B$1="weekly",weekly,IF($B$1="monthly",monthly,IF($B$1="quarterly",quarterly,IF($B$1="annually",annually," ")))))}

you can see the snapshot on:
https://picasaweb.google.com/dennisli828/Jul142011?authkey=Gv1sRgCKOy6r7Tz5qbVg#5629228931035234978


Jul142011
 
That worked fine for me when I opened it with XL2007.

The picture you posted indicated you have XL2007.
But the file is in compatibility mode indicating you also use 2003..

So try

=IF(ISERROR(INDIRECT($B$1))," ",INDIRECT($B$1))

Also, make sure you are:
NOT typing in the { } by hand...
Instead, Highlight the ENTIRE range, and press CTRL + SHIFT + ENTER after entering the formula.
 
Upvote 0

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Hang on, I see it now.

It's something to do with the way you have the entire range entered as a single array.

Try these instead, not requiring CTRL + SHIFT + ENTER

C2: =IF(ROWS(A$1:A1)>ROWS(INDIRECT($B$1)),"",INDEX(INDIRECT($B$1),ROWS(A$1:A1),1))
D2: =IF(ROWS(B$1:B1)>ROWS(INDIRECT($B$1)),"",INDEX(INDIRECT($B$1),ROWS(B$1:B1),2))

Both filled down as far as needed (you have till row 2900)
 
Upvote 0
yea it is hard to do a dynamic table sometime. and btw, do you know if there is any way to draw different graph base on the option (time frequency) I choose? I dont mind to use the original data to draw graph on different sheets but the problem is i have to figure out a certain way to move it to the first sheet when it is chosen.
 
Upvote 0
Sorry, charts aren't my thing..
You might make a new thread for that, I'm sure someone can help.
 
Upvote 0

Forum statistics

Threads
1,224,616
Messages
6,179,909
Members
452,949
Latest member
beartooth91

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top