Results 1 to 15 of 22
Thread: changing errors to 0 (excel xp)

20030531, 21:02 #1
 Join Date
 Aug 2001
 Location
 NY, NY, USA
 Posts
 654
 Thanks
 10
 Thanked 0 Times in 0 Posts
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)

20030531, 22:17 #2
 Join Date
 Jan 2002
 Location
 The Hague, Netherlands
 Posts
 283
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: changing errors to 0 (excel xp)
What is a good yahoo result  a number of which ISNUMBER is TRUE?
Microsoft MVP  Excel

20030531, 23:14 #3
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 5 Times in 5 Posts
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))John ... I float in liquid gardens
UTC 7ąDS

20030601, 00:37 #4
 Join Date
 Aug 2001
 Location
 NY, NY, USA
 Posts
 654
 Thanks
 10
 Thanked 0 Times in 0 Posts
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 000% 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

20030601, 02:32 #5
 Join Date
 Aug 2001
 Location
 NY, NY, USA
 Posts
 654
 Thanks
 10
 Thanked 0 Times in 0 Posts
Re: changing errors to 0 (excel xp)
works perfectly many many thanks
Jerome

20030601, 09:26 #6
 Join Date
 Jan 2002
 Location
 The Hague, Netherlands
 Posts
 283
 Thanks
 0
 Thanked 0 Times in 0 Posts
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))
which requires the free morefunc.xll addin, downloadable from:
http://longre.free.fr/english/index.html
If you can't use the above addin 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.Microsoft MVP  Excel

20030601, 11:39 #7
 Join Date
 Aug 2001
 Location
 NY, NY, USA
 Posts
 654
 Thanks
 10
 Thanked 0 Times in 0 Posts
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

20030601, 12:40 #8
 Join Date
 Jan 2002
 Location
 The Hague, Netherlands
 Posts
 283
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: changing errors to 0 (excel xp)
Jerome,
I think the target numbers (the numbers that we want to retrieve) are either (a) textformatted or ([img]/forums/images/smilies/cool.gif[/img] surrounded with extraneous chars:
Would you please check whether....
=ISNUMBER(TargetCell)
returns TRUE when appropriate?
Also., what do we get with
=CODE(RIGHT(TargetCell))Microsoft MVP  Excel

20030601, 13:32 #9
 Join Date
 Aug 2001
 Location
 NY, NY, USA
 Posts
 654
 Thanks
 10
 Thanked 0 Times in 0 Posts
Re: changing errors to 0 (excel xp)
Aladin:
=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

20030601, 15:39 #10
 Join Date
 Jan 2002
 Location
 The Hague, Netherlands
 Posts
 283
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: changing errors to 0 (excel xp)
That's weird. The data appear OK.
Microsoft MVP  Excel

20030601, 16:00 #11
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 5 Times in 5 Posts
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>.)John ... I float in liquid gardens
UTC 7ąDS

20030601, 16:36 #12
 Join Date
 Aug 2001
 Location
 NY, NY, USA
 Posts
 654
 Thanks
 10
 Thanked 0 Times in 0 Posts
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:????

20030601, 16:45 #13
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 5 Times in 5 Posts
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>John ... I float in liquid gardens
UTC 7ąDS

20030601, 17:37 #14
 Join Date
 Aug 2001
 Location
 NY, NY, USA
 Posts
 654
 Thanks
 10
 Thanked 0 Times in 0 Posts
Re: changing errors to 0 (excel xp)
Doesn;t work for n/a  n/a or 00.00 0.00 wont return 0 for these.
Aladins seems to work better
Thanks
Jerome

20030601, 20:41 #15
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 5 Times in 5 Posts
Re: changing errors to 0 (excel xp)
What column is the 0.00%  0.00% and N/A in?
John ... I float in liquid gardens
UTC 7ąDS