Results 1 to 5 of 5
  1. #1
    5 Star Lounger ibe98765's Avatar
    Join Date
    Aug 2001
    Location
    Bay Area, California, USA
    Posts
    968
    Thanks
    19
    Thanked 4 Times in 4 Posts

    Need Excel help (2000 SP1)

    I am trying to create a custom pricing sheet that can be used for pricing a commerical software offering for the company I work for. Due to non-disclosure, I have adjusted the sheet to make it generic, yet hopefully get the problem across corrrectly.

    In the attached sheet, a customer will choose one of the packages offered. The saleperson will put a quantity (usually 1) in one of cells B5:B8. In order to calculate the value in C11:C13, I need to know what package was chosen by the salesperson. With that number, I can get an index into the array at the bottom of the attached sheet to look up other values. I can work with functions like Match, VLookUp, Index, etc. But I can't seem to figure out a clear and easy way to get the row number of the B5:B8 cell that has the 1 in it. I played around with some nested IF's but it was getting kind of messy. And I don't know VBA and would prefer not to get invoved with that for this one problem. Any help much appreciated!
    Attached Files Attached Files

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

    Re: Need Excel help (2000 SP1)

    I don't think you need to use lookups. If I understand your question, you can use the SUMPRODUCT function to calculate what you want.

    See the attached worksheet. If I misinterpreted your problem, please explain in more detail what you want to calculate.

    BTW, I changed the formula in D10 from =SUM(D57) to =SUM(D58). And I removed the links to an external worksheet, because they caused error messages.
    Attached Files Attached Files

  3. #3
    5 Star Lounger ibe98765's Avatar
    Join Date
    Aug 2001
    Location
    Bay Area, California, USA
    Posts
    968
    Thanks
    19
    Thanked 4 Times in 4 Posts

    Re: Need Excel help (2000 SP1)

    Hans,

    WOW... That is exactly what I needed to do. Thanks!!!!

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

    Re: Need Excel help (2000 SP1)

    Uhhh - what's happening here? You asked an additional question, I started to reply to it, and now the question has disappeared.

    Anyway, upon looking back, I noticed that I made an assumption that may or may not have been justified, and that I made a mistake in the original attachment.

    So I'll post part of the reply I composed:

    Please note that I have assumed that if you enter 3 in cell B6 (quantity of package 2), you'll take 3*5 additional seats in Level 1. If you want 5 additional seats whatever the quantity in B6 is (as long as it's > 0), use
    =SUMIF(B5:B8,">0",E29:E32)
    instead of
    =SUMPRODUCT(B5:B8,E29:E32)

    And I'm afraid I that made a mistake in the formula in cell C13. In the original attachment, the formula was
    =SUMPRODUCT(D2932)
    This just sums the cost of all options in D2932, which doesn't make sense.
    I think it should be
    =B13*SUMPRODUCT(B5:B8,D2932)
    to take into account whether the option is taken (B13), the quantity (B5:B8) and the unit cost (D2932).

    I have attached the modified spreadsheet.
    Attached Files Attached Files

  5. #5
    5 Star Lounger ibe98765's Avatar
    Join Date
    Aug 2001
    Location
    Bay Area, California, USA
    Posts
    968
    Thanks
    19
    Thanked 4 Times in 4 Posts

    Re: Need Excel help (2000 SP1)

    Hans,

    Sorry - what happened was that I deleted the post you were trying to reply to when I realized I didn't look closely enough at what was happening. I like that SUMIF function. Again, thanks so much for your quick and accurate response. You've helped me a lot!

Posting Permissions

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