# Thread: changing errors to 0 (excel xp)

1. ## changing errors to 0 (excel xp)

I download quotes from yahoo and import them into an excel template how do I deal with yahoo results like 0.00 - 0.00%, N/A - N/A, n/a, etc
right now i am using the following which still doesn't deal with n/a which i have to use the second formula. If i don't do this get a value error. Any shortcut formulas to deal with these variations.?????
IF(IF(VLOOKUP(C8,'C:MSOFFICEExcel[d.xls]d'!\$A\$2:\$E\$300,5,FALSE)="0.00 - 0.00%",0,VLOOKUP(C8,'C:MSOFFICEExcel[d.xls]d'!\$A\$2:\$E\$300,5,FALSE))="N/A - N/A",0,IF(VLOOKUP(C8,'C:MSOFFICEExcel[d.xls]d'!\$A\$2:\$E\$300,5,FALSE)="0.00 - 0.00%",0,VLOOKUP(C8,'C:MSOFFICEExcel[d.xls]d'!\$A\$2:\$E\$300,5,FALSE)))

=IF(ISERROR(VLOOKUP(C8,'C:MSOFFICEExcel[d.xls]d'!\$A\$2:\$E\$300,3,FALSE)),0,E8)

2. ## Re: changing errors to 0 (excel xp)

What is a good yahoo result -- a number of which ISNUMBER is TRUE?

3. ## Re: changing errors to 0 (excel xp)

Perhaps something like:

=IF(OR(ISNUMBER(SEARCH("0.00",C8)),ISNUMBER(SEARCH ("N/A",C8))),0,IF(ISERROR(VLOOKUP(C8,'C:MSOFFICEExc el[d.xls]d'!\$A\$2:\$E\$300,3,FALSE)),0,E8))

4. ## Re: changing errors to 0 (excel xp)

John:
looks great but does not work right.
It has to do the following
cell g8 has to check for the stock symbol in cell c8 and then cross reference the stock symbol of c8 in a sperate file called d.xls that has the stock change in column 5. and report it back to cell g8 in the main excel sheet
The problem comes up with column 5 of d.xls has n/a - n/a and 0-00% see the below
EP 8.7 5/30/2003 4:02pm -0.0266
ET 7.65 5/30/2003 4:01pm 0.2076
ETR 51.69 5/30/2003 4:01pm 1.0881
FBGRX 35.13 5/30/2003 6:00pm 0.437
FDX 63.98 5/30/2003 4:01pm 1.3284
FFSL 0 N/A N/A N/A - N/A
FMAGX 86.47 5/30/2003 6:00pm 1.1266
FON 13.56 5/30/2003 4:03pm 0.4985
FRESX 19.97 5/30/2003 6:00pm 0.3227
GCO 15.15 5/30/2003 4:01pm 0.317
GE 28.7 5/30/2003 4:01pm 0.4341
GILD 52.26 5/30/2003 4:00pm 1.7154
GITAX 5.78 5/30/2003 6:00pm 0.066
GM 35.33 5/30/2003 4:02pm 0.7283
GMH 12.2 5/30/2003 4:02pm 0.0184
GP 17.3 5/30/2003 4:01pm 0.00 - 0.00%
GS 81.5 5/30/2003 4:02pm 1.886
GSF 24.88 5/30/2003 4:04pm 0.7859
HGSI 14.65 5/30/2003 4:00pm 0.0093
HLT 13.86 5/30/2003 4:08pm 0.2131
IBM 88.04 5/30/2003 4:00pm 0.6722
INKT 0 N/A N/A N/A - N/A
Right now using =IF(IF(VLOOKUP(C8,[d.xls]Sheet1!\$A\$2:\$E\$300,5,FALSE)="0.00 - 0.00%",0,VLOOKUP(C8,[d.xls]Sheet1!\$A\$2:\$E\$300,5,FALSE))="N/A - N/A",0,IF(VLOOKUP(C8,[d.xls]Sheet1!\$A\$2:\$E\$300,5,FALSE)="0.00 - 0.00%",0,VLOOKUP(C8,[d.xls]Sheet1!\$A\$2:\$E\$300,5,FALSE)))
which does not work with n/a and seems overly convoluted
Jerome

5. ## Re: changing errors to 0 (excel xp)

works perfectly many many thanks
Jerome

6. ## Re: changing errors to 0 (excel xp)

(Edited by HansV to activate URL - see <!help=19>Help 19<!/help>)

This answers my question, hence what follows. Hope John wouldn't mind.

=IF(ISNA(SETV(VLOOKUP(C8,[d.xls]Sheet1!\$A\$2:\$E\$300,5,0))),0,IF(ISNUMBER(GETV()),GE TV(),0))

http://longre.free.fr/english/index.html

If you can't use the above add-in for some reason, add the following code as a module to your workbook and use...

=IF(ISNA(V(VLOOKUP(C8,[d.xls]Sheet1!\$A\$2:\$E\$300,5,0))),0,IF(ISNUMBER(V()),V(),0 ))

Written in C, morefunc functions work faster.

7. ## Re: changing errors to 0 (excel xp)

Correction, Today just putting all 0's not certain why. Using =IF(ISNA(SETV(VLOOKUP(C8,[d.xls]Sheet1!\$A\$2:\$E\$300,5,0))),0,IF(ISNUMBER(GETV()),GE TV(),0))
here is the d.xls
^DJI 8850.26 5/30/2003 4:04pm 139.064
AEE 45.5 5/30/2003 4:01pm 0.4009
AEP 29.04 5/30/2003 4:02pm 0.9643
AGRa 2.44 5/30/2003 4:06pm -0.0921
AGRb 2.34 5/30/2003 4:03pm -0.0469
AMN 34.89 5/30/2003 4:01pm -0.1069
AOL 15.22 5/30/2003 4:01pm 0.3451
ARBA 3.73 5/30/2003 4:00pm 0.0868
AT 47.88 5/30/2003 4:01pm 0.7634
AV 6.62 5/30/2003 4:04pm 0.2525
AWE 7.77 5/30/2003 4:02pm -0.0611

jerome

8. ## Re: changing errors to 0 (excel xp)

Jerome,

I think the target numbers (the numbers that we want to retrieve) are either (a) text-formatted or ([img]/forums/images/smilies/cool.gif[/img] surrounded with extraneous chars:

=ISNUMBER(TargetCell)

returns TRUE when appropriate?

Also., what do we get with

=CODE(RIGHT(TargetCell))

9. ## Re: changing errors to 0 (excel xp)

=ISNUMBER(TargetCell) returns true for a number and for n/a - n/a and 0.00- 0.00% false
=CODE(RIGHT(TargetCell)) for n/a - n/a yields 65 and for 0.0 -0.00% yields 37 and for a number yields 53 and other values.
When I opened the spreadsheet just now seems to work but his morning all 0's, I am confused as to what is going on.
Jerome

10. ## Re: changing errors to 0 (excel xp)

That's weird. The data appear OK.

11. ## Re: changing errors to 0 (excel xp)

Since your data doesn't include columns headings and such, I may be off track, but this should work [Edit; adjusted to assume that if the ticker symbol is in column C, then the data values shold be in column G]:

=IF(ISNUMBER(G8),VLOOKUP(C8,[d.xls]Sheet1!\$A\$2:\$E\$300,5,0),)

(For posting the data with row and column headers, see Hans' Star Post on <!post=Copying Excel Tables to Lounge Posts,164109>Copying Excel Tables to Lounge Posts<!/post>.)

12. ## Re: changing errors to 0 (excel xp)

So I don't need all the rest???
=IF(ISNA(SETV(VLOOKUP(C8,[d.xls]Sheet1!\$A\$2:\$E\$300,5,0))),0,IF(ISNUMBER(GETV()),GE TV(),0))
or
=IF(ISNA(V(VLOOKUP(C8,[d.xls]Sheet1!\$A\$2:\$E\$300,5,0))),0,IF(ISNUMBER(V()),V(),0 ))
Jerome:????

13. ## Re: changing errors to 0 (excel xp)

Ah! If you don't want an N/A# result when a ticker symbol isn't matched, yes. Try this:

=IF(ISNUMBER(G8),if(ISNA(VLOOKUP(C8,[d.xls]Sheet1!\$A\$2:\$E\$300,5,0)),0,VLOOKUP(C8,[d.xls]Sheet1!\$A\$2:\$E\$300,5,0)),0)

I may still be off track. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

14. ## Re: changing errors to 0 (excel xp)

Doesn;t work for n/a - n/a or 0-0.00 0.00 wont return 0 for these.
Thanks
Jerome

15. ## Re: changing errors to 0 (excel xp)

What column is the 0.00% - 0.00% and N/A in?

Page 1 of 2 12 Last

#### Posting Permissions

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