+ Reply to Thread
Results 1 to 9 of 9

multiple IF

  1. #1
    Registered User
    Join Date
    07-23-2005
    Posts
    51

    multiple IF

    I have a spreadsheet as follows

    Col B can be either F or N

    Col J Numbers From 5 TO 36
    Col W Numbers from 1 to 100

    I want to create a new column based on some complicated frormulae.

    If col b is F and col j is less than 5.9, the formula is 2.193*((.99^w1)*(w1^.584))
    where w1 is the number in that cell.

    If col b is F and col j is between 5.9 and 6.9 the formula is 2.424*((.99^w1)*(w1^.668)) where w1 is the number in that cell

    If col b is F and col j is between 6.9 and 7.5, the formula is 2.79*((.99^w1)*(w1^.612))where w1 is the number in that cell

    If col b is F and col j is between 7.5 and 8.5, the formula is 3.052*((.99^w1)*(w1^.654))where w1 is the number in that cell

    If col b is F and col j is between 8.5 and 11.9, the formula is 3.445*((.99^w1)*(w1^.665)where w1 is the number in that cell

    If col b is F and col j is between 11.9 and 12.5 the formula is 4.942*((1.004^w1)*(w1^.494))where w1 is the number in that cell

    If col b is F and col j is between12.5 and 14.5 , the formula is 3.641*((.99^w1)*(w1^.772))where w1 is the number in that cell

    If col b is F and col j is greater than 14.5 , the formula is 3.853*((.984^w1)*(w1^.856))where w1 is the number in that cell

    If col b is N and col j is ANY VALUE , the formula is 8.017*((.9832^w1)*(w1^.7566))where w1 is the number in that cell


    i presume to do this I will need IF and AND functions,but I cant get to do it.

  2. #2
    adsfjkjafsd
    Guest

    Re: multiple IF

    I would build a lookup table to determine the values that vary based on the
    value in col J when the value in col B is "F" and then use a single If
    statement as follows:

    If (B1="N",
    8.017*((.9832^w1)*(w1^.7566)),Lookup(J1,LookupTable,2)*(((J1,LookupTable,3)^
    w1)*(w1^Lookup(J1,LookupTable,4)))

    Steve



  3. #3
    Registered User
    Join Date
    07-23-2005
    Posts
    51

    multiple IF

    Thanks Steve,unf I have never used this LOOKUP function before,I tried the help in Excel. I am getting a syntax error. I am not sure what to do,do I type a table of values somewhere else in the sheet,LOOKUP is in the help but not LOOKUPTABLE.do I type in LOOKUPTABLE or the cell where the table is?

  4. #4
    adsfjkjafsd
    Guest

    Re: multiple IF

    Pytelium,

    My apologies, I was working from a dusty memory. You will want to refer to
    help for the Vlookup functions, and yes, the LookupTable is a range of
    values that you will enter somewhere else in the sheet or book.

    Your lookup range will look like the following.

    5 2.193 0.99 0.584
    5.9 2.424 0.99 0.668
    6.9 2.79 0.99 0.612
    7.5 3.052 0.99 0.654
    8.5 3.445 0.99 0.665
    11.9 4.942 1.004 0.494
    12.5 3.641 0.99 0.772
    14.5 3.853 0.984 0.856

    After entering the range of values I will typically name that range
    "LookupTable" so that I can then refer to it by name.

    The formula I gave you earlier should now look like the following:

    If
    (B1="N",8.017*((.9832^w1)*(w1^.7566)),VLookup(J1,LookupTable,2,True)*((VLook
    up(J1,LookupTable,3,True)^w1)*(w1^VLookup(J1,LookupTable,4,True)))

    Because of the true statement, the VLookup function will read from the row
    in the LookupTable where the first column is less than or equal to the value
    in Col J. The value following LookupTable in the formula indicates which
    column contains the values you want. In this instance if the value in
    column J is less than 5 you will get an error message.

    Steve

    P.S. How do I get my response to post to the newsgroup? My first response
    to you does not appear.




  5. #5
    Registered User
    Join Date
    07-23-2005
    Posts
    51

    Re: multiple IF

    Thanks Steve, I am afraid still getting error,away for a few days,will try later in week.

  6. #6
    Forum Contributor
    Join Date
    06-23-2004
    Location
    Houston, TX
    Posts
    571
    I will just elaborate on what Steve has started. You can set up your table as follows:

    For example, in Cells AB1 to AB8, enter the following numbers:

    5
    5.9
    6.9
    7.5
    8.5
    11.9
    12.5
    14.5

    In Cells AC1 to AC8, enter the following numbers

    2.193
    2.424
    2.790
    3.052
    3.445
    4.942
    3.641
    3.854

    In Cells AD1 to AD8, enter the following numbers

    0.99
    0.99
    0.99
    0.99
    0.99
    1.004
    0.99
    0.99

    and, in cells AE1:AE8, enter the following numbers

    0.584
    0.668
    0.612
    0.654
    0.665
    0.494
    0.772
    0.856

    The formula to use is as follows:

    =IF(B1="N",8.017*((0.9832^W1)*(W1^0.7566)),VLOOKUP(J1,$AB$1:$AE$8,2)*(VLOOKUP(J1,$AB$1:$AE$8,3)^W1)*(W1^VLOOKUP(J1,$AB$1:$AE$8,4)))

    I hope this will help you.

    Regards.
    BenjieLop
    Houston, TX

  7. #7
    Registered User
    Join Date
    08-01-2005
    Posts
    2
    Pytellium,

    It might help if I could see the formula you are using. Can you copy and paste your formula into a reply?

    Thanks,
    Steve

  8. #8
    Registered User
    Join Date
    07-23-2005
    Posts
    51
    Steve and BenjiLop,thank you both,BenjiLop's solution works for me now.

  9. #9
    Forum Contributor
    Join Date
    06-23-2004
    Location
    Houston, TX
    Posts
    571
    Thank you for the feedback.

    Regards.

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1