Need help with function in Excel...

Discussion in 'OT Technology' started by DatacomGuy, Apr 14, 2006.

  1. DatacomGuy

    DatacomGuy is moving to Canada

    Joined:
    Oct 14, 2002
    Messages:
    16,546
    Likes Received:
    0
    Location:
    Tampa, FL
    I want to create a function in a quote form..

    In column C15 I have a qty. In column D15, I have a unit of measure (EA for each, C for hundred, M for thousand). In K15, I have sell price, and M15, I have extended price.

    In M15, I want to make a statement that says something to the effect of "If D15 = EA, then K15*C15=X/1", and then same for C and M unit of measures.

    Anyone know what formula would be proper for this?

    I tried =IF(D15=EA,"K15*C15=/1", but didn't work. I'm missing something here.

    Anyone?
     
  2. Zak8022

    Zak8022 New Member

    Joined:
    Apr 16, 2004
    Messages:
    4,012
    Likes Received:
    0
    Location:
    Maryland
    The reason you function is failing is because of what i bolded and underlined.

    I'm not sure i quite understand all of the problem.... or i dont have enough info to help fully.

    Can you please explain what you are trying to do with the "=/1" part.
     
  3. Zak8022

    Zak8022 New Member

    Joined:
    Apr 16, 2004
    Messages:
    4,012
    Likes Received:
    0
    Location:
    Maryland
    On second thought... Why is there a need to have different math performed if you have a seperate price column? You could simply do
    Code:
    =K15*C15
    Unless i am not understanding the real meanings of Price and Extended Price.
     
  4. DatacomGuy

    DatacomGuy is moving to Canada

    Joined:
    Oct 14, 2002
    Messages:
    16,546
    Likes Received:
    0
    Location:
    Tampa, FL
    I'll explain.

    Unit of measure is [SIZE=-1]a standard of basic quantity or increment by which something is divided, counted, or described.

    In this case, "EA" would mean that the extended price would be divided by 1. "C" would mean that the extended price would be divided by 100. "M" would mean that the extended price would be divided by 1000.

    Examples.

    Line 1 of the quote could have 5000FT of fiber. This is priced per ft, so unit of measure would be "EA", which would mean that the extended price should be divided by 1. Say the selll price is 1.00/ft, so the extended price would be 5000.00.

    Line 2 of the quote could have 5000FT of Category 6 cable. This is priced per thousand feet, so unit of measure would be "M", which would mean that the extended price should be divided by 1000. Say the sell price is 100.00/m, the extended should read 500.00.

    And same would go for "C", but divisible by 100.

    I want to create a formula or function, that will recognize what is being inputted into the UOM column, and change the formula in the extended column to reflex the UOM.

    Let me know if I've confused this further.. I can post my quote form if necessary, although I'd prefer not to.
    [/SIZE]
     
  5. Zak8022

    Zak8022 New Member

    Joined:
    Apr 16, 2004
    Messages:
    4,012
    Likes Received:
    0
    Location:
    Maryland
    Ok... i think i get it... lemme work on it a little before i go asking for your form. That may not be necessary.
     
  6. Joe_Cool

    Joe_Cool Never trust a woman or a government. Moderator

    Joined:
    Jun 30, 2003
    Messages:
    299,278
    Likes Received:
    555
    Try putting this in M15:

    =if(D15="C",C15*K15/100,if(D15="M",C15*K15/1000,C15*K15))

    That way, if D15 is C, you'll get quantity * price /100.
    If not, it triggers the nested if.
    If D15 is M, you get quantity * price /1000.
    Otherwise, you get the default (EA), which is quantity * price.
     
  7. Joe_Cool

    Joe_Cool Never trust a woman or a government. Moderator

    Joined:
    Jun 30, 2003
    Messages:
    299,278
    Likes Received:
    555
    And you can extend that for other values of D15 by replacing "C15*K15" with other nested if statements, and leave "C15*K15" for the default value (the else in your innermost if statement).
     
  8. Zak8022

    Zak8022 New Member

    Joined:
    Apr 16, 2004
    Messages:
    4,012
    Likes Received:
    0
    Location:
    Maryland
    Ok... try this:

    Code:
    =IF(D15="EA",(K15*C15)/1,IF(D15="C",(K15*C15)/1000,IF(D15="M",(K15*C15)/100)))
    I tried it... and i think it works the way you want. Get back to me and let me know.
     
  9. Zak8022

    Zak8022 New Member

    Joined:
    Apr 16, 2004
    Messages:
    4,012
    Likes Received:
    0
    Location:
    Maryland
    Damn, Joe_Cool beat me to it.... but i think we came up with the same things.

    edit: we're actually exactly the same... he just didnt write in the part of the function for EA. And i think i switched around the C/M divisors.
     
  10. Joe_Cool

    Joe_Cool Never trust a woman or a government. Moderator

    Joined:
    Jun 30, 2003
    Messages:
    299,278
    Likes Received:
    555
    Yeah, I left out EA and just left /1 as the default value, as a catch-all for invalid entries in that column.
     
  11. DatacomGuy

    DatacomGuy is moving to Canada

    Joined:
    Oct 14, 2002
    Messages:
    16,546
    Likes Received:
    0
    Location:
    Tampa, FL
    This works..

    BADASS. Thanks, both of you. I appreciate it!!
     

Share This Page