1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Excel Formula

Discussion in 'Microsoft Office Specialist (MOS)' started by ThomasMc, Jul 20, 2007.

  1. ThomasMc

    ThomasMc Gigabyte Poster

    1,507
    49
    111
    Been a long day today but for the life of me i cant work out the old version of this formula (i.e one that would yeild the same result in office xp, 2003)

    Code:
    =IFERROR(VLOOKUP(J9,'Control sheet'!$D$8:'Control sheet'!$F$10,3,FALSE),"0")
    
    Anyone?

    [Edit]
    I tryed

    Code:
    =IF(ISERROR(VLOOKUP(J9,'Control sheet'!$D$8:'Control sheet'!$F$10,3,FALSE)),"0")
    
    But this just returns a FALSE value so i've deffo missed something
     
    Certifications: MCDST|FtOCC
    WIP: MCSA(70-270|70-290|70-291)
  2. Mr.Cheeks

    Mr.Cheeks 1st ever Gold Member! Gold Member

    5,369
    85
    190
    can you upload the spreadsheet?
     
  3. ThomasMc

    ThomasMc Gigabyte Poster

    1,507
    49
    111
    sorry bud its kind of populated with sensitive data(it's a pritty big file to), but i could provide any information on what i'm looking up and what type of value i want to return
     
    Certifications: MCDST|FtOCC
    WIP: MCSA(70-270|70-290|70-291)
  4. ThomasMc

    ThomasMc Gigabyte Poster

    1,507
    49
    111
    heres a mock version if you open this with 2003 or lower you will notice that the "New Formula" will hit an error and prefix the formula (indicating that its not supported, the compat sheet will confim this) and the old one that am trying to get to work will say FALSE
     

    Attached Files:

    Certifications: MCDST|FtOCC
    WIP: MCSA(70-270|70-290|70-291)
  5. UCHEEKYMONKEY
    Honorary Member

    UCHEEKYMONKEY R.I.P - gone but never forgotten. Gold Member

    4,140
    58
    214
    q) Which version of excel are you using?

    Is this a formula from a cell or written inside a macro :blink

    Vlookup is only used if you are comparing two excel spreadsheets, but you've only enclosed one?
     
    Certifications: Comptia A+
    WIP: Comptia N+
  6. ThomasMc

    ThomasMc Gigabyte Poster

    1,507
    49
    111
    A)I was writing in 2007 but client will be using 2003 didn't pick up on the error till i saved and got my functionality report

    and it's in a cell

    [Edit]
    sorry i was doing it as per instructions from http://office.microsoft.com/en-us/excel/HP052093351033.aspx just mentions looking up within a range on a single sheet, the vlookup part seems to be working fine both return the correct value but i what to return a value of "0" if there nothing there
     
    Certifications: MCDST|FtOCC
    WIP: MCSA(70-270|70-290|70-291)
  7. UCHEEKYMONKEY
    Honorary Member

    UCHEEKYMONKEY R.I.P - gone but never forgotten. Gold Member

    4,140
    58
    214
    So are you trying to to find an error function in $D$8?
    =IF(ISERROR(B4/C4),0,B4/C4) - trying to see what the equation above does.

    Hmmm:hhhmmm

    VLOOKUP searches for a value in the column of the mention cell/table and returns the value in a different column of the table in the same row where the searched value is found. However, if the value is not found in the mention column of the table, the VLOOKUP command will returns an error code.

    :blink
     
    Certifications: Comptia A+
    WIP: Comptia N+
  8. UCHEEKYMONKEY
    Honorary Member

    UCHEEKYMONKEY R.I.P - gone but never forgotten. Gold Member

    4,140
    58
    214

    Ahh right now I see,

    I take it 2007 office doesn't have a conversion yet??
     
    Certifications: Comptia A+
    WIP: Comptia N+
  9. ThomasMc

    ThomasMc Gigabyte Poster

    1,507
    49
    111
    not that could see
    the only alternative to the IFERROR function was ISERROR but the only 2 examples that i could find where =IF(ISERROR(VALUE)) and
    =IF(ISERROR(Formula, "", Formula))
     
    Certifications: MCDST|FtOCC
    WIP: MCSA(70-270|70-290|70-291)
  10. ThomasMc

    ThomasMc Gigabyte Poster

    1,507
    49
    111
    thanks for the replies but i think we may have cracked it
    Code:
    =IF(ISERROR(VLOOKUP(J9,'Control Sheet'!$D$8:'Control Sheet'!$F$10,3,FALSE)),"0",VLOOKUP(J9,'Control Sheet'!$D$8:'Control Sheet'!$F$10,3,FALSE))
    
    still dont understand why it needs the formula twice
     
    Certifications: MCDST|FtOCC
    WIP: MCSA(70-270|70-290|70-291)
  11. UCHEEKYMONKEY
    Honorary Member

    UCHEEKYMONKEY R.I.P - gone but never forgotten. Gold Member

    4,140
    58
    214
    Excellent news glad you got it sorted in the end!:biggrin

    I must admit you've made me think twice about not getting Office 2007 if doesn't have backward compatibility for excel spreadsheets. :ohmy

    I am working on some projects for work at the moment on excel (office 2003) and was thinking of upgrading to office 2007.

    I guess I will have to wait a while until there are some reviews on the web about it8)
     
    Certifications: Comptia A+
    WIP: Comptia N+

Share This Page

Loading...