Need Excel help (2000 SP1)

Topic

New Reply

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!

Reply | Quote

Replies

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(D5:D7) to =SUM(D5:D8). And I removed the links to an external worksheet, because they caused error messages.

Reply | Quote

Hans,

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

Reply | Quote

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(D29:D32)
This just sums the cost of all options in D29:D32, which doesn’t make sense.
I think it should be
=B13*SUMPRODUCT(B5:B8,D29:D32)
to take into account whether the option is taken (B13), the quantity (B5:B8) and the unit cost (D29:D32).

I have attached the modified spreadsheet.

Reply | Quote

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!

Reply | Quote