+ Reply to Thread
Results 1 to 7 of 7

If functions and possibly or

  1. #1
    Registered User
    Join Date
    11-03-2006
    Posts
    19

    If functions and possibly or

    Hi, I am trying to create a formula that will look for a value "BL" in a cell and if it is not found, return a number for that cell.

    If the cell is "BL" then the number is the lookup cell needs to be divided by a quantity (another cell) and then multiplied by a static number.

    If the cell is not "BL" then a vlookup is done to pull back a number from another spreadsheet that corresponds to what the value is looking up.

    so far, this is what I have in two cells and each work but I cannot figure out how to combine them into one function.

    =IF(LEFT(D2,2)<>"BL",VLOOKUP(D2,'Master Resource Generic Rates'!A:C,3)*'106979Sep06Actuals Extract_TO11'!F2)


    =IF(F2<>0,(G2/F2*2.44),IF(F2=0,G2))


    I have tried using OR but am not that familiar with it and am at a roadblock.

    Any help would be appreciate.

  2. #2
    Forum Contributor
    Join Date
    07-13-2006
    Posts
    400
    Quote Originally Posted by facmess1
    Hi, I am trying to create a formula that will look for a value "BL" in a cell and if it is not found, return a number for that cell.

    If the cell is "BL" then the number is the lookup cell needs to be divided by a quantity (another cell) and then multiplied by a static number.

    If the cell is not "BL" then a vlookup is done to pull back a number from another spreadsheet that corresponds to what the value is looking up.

    so far, this is what I have in two cells and each work but I cannot figure out how to combine them into one function.

    =IF(LEFT(D2,2)<>"BL",VLOOKUP(D2,'Master Resource Generic Rates'!A:C,3)*'106979Sep06Actuals Extract_TO11'!F2)


    =IF(F2<>0,(G2/F2*2.44),IF(F2=0,G2))


    I have tried using OR but am not that familiar with it and am at a roadblock.

    Any help would be appreciate.
    I'm assuming this:
    =IF(LEFT(D2,2)<>"BL",VLOOKUP(D2,'Master Resource Generic Rates'!A:C,3)*'106979Sep06Actuals Extract_TO11'!F2)
    is in G2?

    then simply replacing G2 with that formula will work. i.e.

    =IF(F2<>0,(IF(LEFT(D2,2)<>"BL",VLOOKUP(D2,'Master Resource Generic Rates'!A:C,3)*'106979Sep06Actuals Extract_TO11'!F2)/F2*2.44),IF(F2=0,IF(LEFT(D2,2)<>"BL",VLOOKUP(D2,'Master Resource Generic Rates'!A:C,3)*'106979Sep06Actuals Extract_TO11'!F2)))

    sorry if i misunderstood. If I did can you post a bit of your worksheet so I can see exactly what you mean.

  3. #3
    Registered User
    Join Date
    11-03-2006
    Posts
    19

    for MDubbelboer

    I have attached the instances that need to be considered. Please let me know if this is helpful.

    Thanks again
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    07-13-2006
    Posts
    400
    Quote Originally Posted by facmess1
    I have attached the instances that need to be considered. Please let me know if this is helpful.

    Thanks again
    alright. i'm still lost.
    you want columns L and M combined into one column? in which instances do you want L to be the answer and when do you want M?

  5. #5
    Registered User
    Join Date
    11-03-2006
    Posts
    19

    Columns L & M combined

    This is what I am trying to accomplish.

    I want to create a pivot table that provides the sum total costs for each WBS ID. In order to do this I need to create the formulas to populate the numbers in columns L & M and if possible, one column only. Here is the answer to your question.


    I want L to be the answer is the following conditions are met: Column D starts with "BL". If thatís the situation then I want column G divided by column F and I want that result to be multiplied by 2.44. The one condition I need to also add is that if Column F is equal to 0 then I need the value in column G to be returned.

    I want M to be the answer when Column D does not start with "BL" In addition, when it does not start with "BL" but one of the following ("IL", "BS". Or "IS") I need to do a vlookup that matches the string in another worksheet. Then, multiply that value by column F. This would result in the total cost. If Column D is ODC or TRV I need the value of Column G returned.

    Hopefully this will make more sense.

    If possible, I would like to get this all in one formula.

  6. #6
    Forum Contributor
    Join Date
    07-13-2006
    Posts
    400
    Quote Originally Posted by facmess1
    This is what I am trying to accomplish.

    I want to create a pivot table that provides the sum total costs for each WBS ID. In order to do this I need to create the formulas to populate the numbers in columns L & M and if possible, one column only. Here is the answer to your question.


    I want L to be the answer is the following conditions are met: Column D starts with "BL". If thatís the situation then I want column G divided by column F and I want that result to be multiplied by 2.44. The one condition I need to also add is that if Column F is equal to 0 then I need the value in column G to be returned.

    I want M to be the answer when Column D does not start with "BL" In addition, when it does not start with "BL" but one of the following ("IL", "BS". Or "IS") I need to do a vlookup that matches the string in another worksheet. Then, multiply that value by column F. This would result in the total cost. If Column D is ODC or TRV I need the value of Column G returned.

    Hopefully this will make more sense.

    If possible, I would like to get this all in one formula.
    think i got it now. thanks for the clarification

    I put in a column N doing what I think you're trying to do.
    N2 has the vlookup formula in it but I think it's going to have to be re-adjuted by you as it's defaulting to my winzip temp folder.
    n3,n4,n5 use columns L and M but i left them there as a simplified version of the formula in N2 that is more legible.

    You might want to look into using named ranges for your lookups, it cleans up the formula a bit and makes for easier troubleshooting

    hope this works
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    11-03-2006
    Posts
    19

    Thanks

    Thanks again for your help. I will definately look into using named ranges in the future. I am just beginning to figure these functions out so I am sure this will not be my last pos.

+ 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