+ Reply to Thread
Results 1 to 16 of 16

Nested If Formulae

Hybrid View

  1. 07-15-2009, 11:54 AM #1
    stanleds
    stanleds is offline
    Registered User
    Join Date
    07-15-2009
    Location
    Bristol, UK
    MS-Off Ver
    Excel 2010
    Posts
    10

    Unhappy Nested If Formulae

    Hi All,



    Please help if you can. I am sure that this can be solved. I am so close I can smell it!! This is the problem;

    I have a column E which contains the values "Old" or "New"

    I have column O which may be empty or contain an alpha numeric value that begins with "DBC", begin with "C" or begin with "B"

    I have three additional worksheets that contain different data these are;

    COMPSW-NS

    COMPSW-OS

    COMPSB

    I have a column Q that contains this formulae which does not work.

    =IF(O9="","",IF(O9>"DBC",150,IF(AND(O9>0,E9="NEW"),VLOOKUP(O9,'COMPSW-NS'!D:AP,35,FALSE),IF(AND(O9>0,E9="OLD"),VLOOKUP(O9,'COMPSW-OS'!D:AP,35,FALSE),IF(O9="B*",VLOOKUP(MID(O8,2,4),COMPSB!A:AP,42,FALSE))))))

    This bit of the formulae (=IF(O9="","",IF(O9>"DBC",150,) seems to look after the first condition OK.

    What I am trying to achieve is this;

    If column O contains a value and column E contains "New", then lookup a value in the "COMPSW-NS" worksheet.

    If column O contains a value and column E contains "Old", then lookup a value in the "COMPSW-OS" worksheet.

    If the result of either of these conditions returns a #N/A, or if the value in column O begins with B, ignore the preceeding "B" and lookup the remainder of the cell contents which is 4 characters (numbers) in the "COMPSB" worksheet.

    I think that I should be trying to incorporate 'ISNA' & Or to achieve this result but I can't work out the correct syntax.

    I would be really grateful if you can help. I have been trying to resolve this now for a number of hours I am really lost!!



    Hope somebody can help.



    Regards,

    Stan
    Last edited by NBVC; 07-15-2009 at 08:43 PM.
    Reply With QuoteRegister To Reply
  2. 07-15-2009, 12:04 PM #2
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    re: Nested If Formulae

    Based on this:

    If column O contains a value and column E contains "New", then lookup a value in the "COMPSW-NS" worksheet.

    If column O contains a value and column E contains "Old", then lookup a value in the "COMPSW-OS" worksheet.

    If the result of either of these conditions returns a #N/A, or if the value in column O begins with B, ignore the preceeding "B" and lookup the remainder of the cell contents which is 4 characters (numbers) in the "COMPSB" worksheet.

    I think that I should be trying to incorporate 'ISNA' & Or to achieve this result but I can't work out the correct syntax.

    I would be really grateful if you can help. I have been trying to resolve this now for a number of hours I am really lost!!
    I wonder if this would work:

    =IF(ISNA(IF(O9<>"",IF(LEFT(O9)="B",VLOOKUP(MID(O8,2,4),COMPSB!A:AP,42,FALSE),IF(E9<>"",IF(E9="New",VLOOKUP(O9,'COMPSW-NS'!D:AP,35,FALSE),IF(E9="OLD",VLOOKUP(O9,'COMPSW-OS'!D:AP,35,FALSE),"")),"")),"")),"",IF(O9<>"",IF(LEFT(O9)="B",VLOOKUP(MID(O8,2,4),COMPSB!A:AP,42,FALSE),IF(E9<>"",IF(E9="New",VLOOKUP(O9,'COMPSW-NS'!D:AP,35,FALSE),IF(E9="OLD",VLOOKUP(O9,'COMPSW-OS'!D:AP,35,FALSE),"")),"")),""))
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
    Reply With QuoteRegister To Reply
  3. 07-15-2009, 12:10 PM #3
    stanleds
    stanleds is offline
    Registered User
    Join Date
    07-15-2009
    Location
    Bristol, UK
    MS-Off Ver
    Excel 2010
    Posts
    10

    re: Nested If Formulae

    Hi there, I have pasted this in to the correct column and I'm afraid I just get a blank cell. Can you offer any further guidance please?

    Many thanks,

    Stan
    Reply With QuoteRegister To Reply
  4. 07-15-2009, 12:17 PM #4
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    re: Nested If Formulae

    What is O9 and E9?

    What should you expect...

    Also, noticed one reference to O8 in the formula should be O9

    =IF(ISNA(IF(O9<>"",IF(LEFT(O9)="B",VLOOKUP(MID(O9,2,4),COMPSB!A:AP,42,FALSE),IF(E9<>"",IF(E9="New",VLOOKUP(O9,'COMPSW-NS'!D:AP,35,FALSE),IF(E9="OLD",VLOOKUP(O9,'COMPSW-OS'!D:AP,35,FALSE),"")),"")),"")),"",IF(O9<>"",IF(LEFT(O9)="B",VLOOKUP(MID(O9,2,4),COMPSB!A:AP,42,FALSE),IF(E9<>"",IF(E9="New",VLOOKUP(O9,'COMPSW-NS'!D:AP,35,FALSE),IF(E9="OLD",VLOOKUP(O9,'COMPSW-OS'!D:AP,35,FALSE),"")),"")),""))
    otherwise post a workbook example...
    Last edited by NBVC; 07-15-2009 at 01:06 PM.
    Reply With QuoteRegister To Reply
  5. 07-15-2009, 12:30 PM #5
    stanleds
    stanleds is offline
    Registered User
    Join Date
    07-15-2009
    Location
    Bristol, UK
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Complicated Nested If Formulae

    Hi again,

    I noticed the reference to row 8, changed that and it made no difference i am afraid. I would expect to see a number which would be returned by the lookup formulae. It would be difficult to upload an example as I would need to include a big chunk of the spreadsheet which is 8 meg.

    I might be able to replicate a small amount of data on each worksheet to reduce the file size. How do you upload a file? Can you help me with this? Sorry!!
    Stan
    Reply With QuoteRegister To Reply
  6. 07-15-2009, 01:05 PM #6
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Nested If Formulae

    After you condensed it sufficiently (you can also zip it), then click Post Reply and then click the paperclip icon in the reply box.. click Browse to find the file and then click Upload to upload it.. then Close.
    Reply With QuoteRegister To Reply
  7. 07-15-2009, 04:18 PM #7
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Nested If Formulae

    What are you really trying to say with that, though.. you can't really have something be greater than a text string....

    IF(O8>"DBC",150"
    Reply With QuoteRegister To Reply
  8. 07-15-2009, 04:31 PM #8
    stanleds
    stanleds is offline
    Registered User
    Join Date
    07-15-2009
    Location
    Bristol, UK
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Nested If Formulae

    I'm afraid that comes down to my ignorance again. What I am trying to say is if cell O9 starts with "DBC" then make cell Q9 150. I didnt think that you ould use wildcards with if statements so I attempted this as an alternative. I understand that this may not be correct. Can you suggest an alternative? I do need to account for the possibility that O9 may be blank as well!!

    Many thanks again,

    Stan
    Reply With QuoteRegister To Reply
  9. 07-15-2009, 04:35 PM #9
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Nested If Formulae

    Then try:

    =IF(LEFT(O9,3)="DBC",150,IF(ISNA(IF(O9<>"",IF(LEFT(O9)="B",VLOOKUP(MID(O9,2,4)+0,COMPSB!A:AP,42,FALSE),IF(E9<>"",IF(E9="New",VLOOKUP(O9,'COMPSW-NS'!D:AP,35,FALSE),IF(E9="OLD",VLOOKUP(O9,'COMPSW-OS'!D:AP,35,FALSE),"")),"")),"")),"",IF(O9<>"",IF(LEFT(O9)="B",VLOOKUP(MID(O9,2,4)+0,COMPSB!A:AP,42,FALSE),IF(E9<>"",IF(E9="New",VLOOKUP(O9,'COMPSW-NS'!D:AP,35,FALSE),IF(E9="OLD",VLOOKUP(O9,'COMPSW-OS'!D:AP,35,FALSE),"")),"")),"")))
    Reply With QuoteRegister To Reply
  10. 07-15-2009, 04:45 PM #10
    stanleds
    stanleds is offline
    Registered User
    Join Date
    07-15-2009
    Location
    Bristol, UK
    MS-Off Ver
    Excel 2010
    Posts
    10

    Unhappy Re: Nested If Formulae

    For some reason. it does nnot like that formulae. Have you tried pasting this in the sample workbook that I uploaded?

    thanks again,

    Stan
    Reply With QuoteRegister To Reply
  11. 07-15-2009, 05:01 PM #11
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Nested If Formulae

    Okay try:

    =IF(LEFT(O9,3)="DBC",150,IF(ISNA(IF(O9<>"",IF(LEFT(O9)="B",VLOOKUP(MID(O9,2,4)+0,COMPSB!A:AP,42,FALSE),IF(E9<>"",IF(E9="New",VLOOKUP(O9,'COMPSW-NS'!D:AP,35,FALSE),VLOOKUP(O9,'COMPSW-OS'!D:AP,35,FALSE)),"")),"")),"",IF(O9<>"",IF(LEFT(O9)="B",VLOOKUP(MID(O9,2,4)+0,COMPSB!A:AP,42,FALSE),IF(E9<>"",IF(E9="New",VLOOKUP(O9,'COMPSW-NS'!D:AP,35,FALSE),VLOOKUP(O9,'COMPSW-OS'!D:AP,35,FALSE)),"")),"")))
    or shorter and probably more efficient:

    =IF(LEFT(O9,3)="DBC",150,LOOKUP(9.999999999E+307,CHOOSE({1,2},0,IF(O9<>"",IF(LEFT(O9)="B",VLOOKUP(MID(O9,2,4)+0,COMPSB!A:AP,42,FALSE),IF(E9<>"",IF(E9="New",VLOOKUP(O9,'COMPSW-NS'!D:AP,35,FALSE),VLOOKUP(O9,'COMPSW-OS'!D:AP,35,FALSE)),"")),""))))
    this last formula will return 0, if the Vlookup() results are #N/A... so you can format that cell as 0;-0;;@ to blank out the 0.
    Reply With QuoteRegister To Reply
  12. 07-15-2009, 04:48 PM #12
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Nested If Formulae

    I think it is the dreaded to many nested functions... just hold a few moments...
    Reply With QuoteRegister To Reply
  13. 07-15-2009, 05:19 PM #13
    stanleds
    stanleds is offline
    Registered User
    Join Date
    07-15-2009
    Location
    Bristol, UK
    MS-Off Ver
    Excel 2010
    Posts
    10

    Talking Re: Nested If Formulae

    What Can I say, That has resolved the problem completely

    You are a legend!!

    When I have more time, if you are in agreement, I need to understand the formulae that I have used thanks to yoyr help..... If I need to change in the future, I will have to understand how the results are achieved. This forum is brilliant. Thanks again for all your help.

    Stan
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1