Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    5 Star Lounger
    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)

  2. #2
    3 Star Lounger
    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

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 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

  4. #4
    5 Star Lounger
    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 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. #5
    5 Star Lounger
    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

  6. #6
    3 Star Lounger
    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 add-in, downloadable from:

    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.
    Microsoft MVP - Excel

  7. #7
    5 Star Lounger
    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

  8. #8
    3 Star Lounger
    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) text-formatted 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

  9. #9
    5 Star Lounger
    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

  10. #10
    3 Star Lounger
    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

  11. #11
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 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

  12. #12
    5 Star Lounger
    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:????

  13. #13
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 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

  14. #14
    5 Star Lounger
    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 0-0.00 0.00 wont return 0 for these.
    Aladins seems to work better
    Thanks
    Jerome

  15. #15
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 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

Page 1 of 2 12 LastLast

Posting Permissions

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