Results 1 to 11 of 11
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Wrong ISSEROR (Excell 2002)

    Hi

    Can somebody tell me whats wrong with this formula please I have been trying to correct it for ages.

    =IF(ISERROR(IF(OR(B7="",$N$2<>0.45))),0,IF(ISERROR (VLOOKUP(B7,Chem_Table,PT_PDPrice,FALSE)),1,0))

    Thanks in advance

    Braddy
    If you are a fool at forty, you will always be a fool

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Wrong ISSEROR (Excell 2002)

    Perhaps we can help if you tell us what you want the formula to do.

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Wrong ISSEROR (Excell 2002)

    Hi Hans

    Sorry, at the moment it is as below, if B7 is blank it shows #N/A I would like it to be blank

    =IF(OR(B7="",$N$2<>0.45),0,IF(ISERROR(VLOOKUP(B7,C hem_Table,PT_PDPrice,FALSE)),1,0))

    Braddy

    Thanks for your response
    If you are a fool at forty, you will always be a fool

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Wrong ISSEROR (Excell 2002)

    That formula returns 0 if B7 is blank, not #N/A, so I still don't understand.

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Wrong ISSEROR (Excell 2002)

    Very wide screenshot reduced in size by HansV - please keep images within 640 x 480

    Hi Hans

    it shows #N/A in mine see screen shot

    Regards

    Braddy
    If you are a fool at forty, you will always be a fool

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Wrong ISSEROR (Excell 2002)

    Hans is right, it does not show #N/A. Then again we do not have the Vlookup table that you refer to.
    You have covered for the #N/A in you IF by using the ISERROR function! So when I test it it never shows an error!

    PS: if you want to return a blank anyways should the function not look something like this...
    <hr>=IF(OR(B7="",$N$2<>0.45),"",IF(ISERROR(VLOOKUP(B7,Chem_Table,PT_PDPrice,FALSE )),1,""))<hr>

    Could you sent a scale down version of your workbook if the problem persists. (If there is not confidentiality of course!)
    Regards,
    Rudi

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Wrong ISSEROR (Excell 2002)

    The reason that N7 is #N/A is not that B7 is blank but that N2 is #N/A. Does this do what you want?

    =IF(ISNA($N$2),0,IF(OR(B7="",$N$2<>0.45),0,IF(ISER ROR(VLOOKUP(B7,Chem_Table,PT_PDPrice,FALSE)),1,0)) )

    If you want a blank if N2 is #N/A, replace the first 0 in the formula by "".

  8. #8
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Wrong ISSEROR (Excell 2002)

    In your VLookup, what is PT_PDP Price?
    Remember, this argument must return a number. It asks for a column index number!
    Regards,
    Rudi

  9. #9
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Wrong ISSEROR (Excell 2002)

    Hi Hans

    That works fine, Thanks to all contributors.

    Braddy
    If you are a fool at forty, you will always be a fool

  10. #10
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Wrong ISSEROR (Excell 2002)

    Hi Rudi

    PT_PDP is a named cell used a variable, so if I add column to the sheet I don't have to copy down all the formulas again.

    Regards

    Braddy
    If you are a fool at forty, you will always be a fool

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Wrong ISSEROR (Excell 2002)

    A way around this problem of expanding lookup tables is to use a combination of index and match (see <post#= 244408>post 244408</post#>). With this scheme inserting new columns does not require the need to list the "offset" since you actually define the range. The range (lookup and output) may even be on separate sheets.

    Steve

Posting Permissions

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