Quantity computed based on a drop down list & a numerical value greater than or equal to

[cwby44]

I sure hope you guys can help me. I am working on a new spreadsheet for a job where I have numerous line items of a product. For each line item there is one product type (ex door, window, glass). However, if the width of the product is greater than a given dimension than I actually will need two pieces. To compound this, there is one type of product that is actually 2 different pieces and if that one product is greater than say 36, I would actually need 4 pieces. This is where I run into the problem.

I have attached a sample of the worksheet.

Thanks in advance.

[sktneer]

May be this......

In D3

Please Login or Register  to view this content.

and copy down.

[Tsjallie]

The vlookup in D4 finds "1" and - as C4 <> "2L" (it's "NL"- the conditional multiply factor also is 1.
So either your formula has an error or the table Data contains incorrect data.

[cwby44]

sktneer
I tried that formula and when I use I for row 4 which is the "NL" it still only gives me a quantity of1. But is should be 2 since the width is greater than 37. That is where I always get hung up.

[cwby44]

Tsjallie
I think I understand what you are saying and agree with those items. However, since the width is greater than 37 in E4 the factor should increase to 2 right? Sorry, I'm better at construction than computers.

[sktneer]

I think you only need this...

Please Login or Register  to view this content.

If this is not what you are trying to achieve. Fill the correct desired output manually in the workbook and upload it.

[cwby44]

Spreadsheet help2.xlsxEntered the results that I am looking for manually.

[sktneer]

In the attached sheet E5 is less than 37 so D5 is 1 which is OK as per your logic. But E6 is greater than 37 so why D6 is 1? Are you applying any additional criteria to achieve this?

[cwby44]

Yes. Since the C6 = "W1" it doesn't need an additional piece. If C6 = 2L and the width is less than 37 the quantity would be 2 - if the width was greater than 37 it would require 4. If any other call out in the "Mark" column - width less than 37 then 1 is required, if the width is greater than 37, 2 would be required.

[cwby44]

Can anyone help me with what I uploaded yesterday? I would really appreciate it.
Mike

[Tsjallie]

since the width is greater than 37 in E4 the factor should increase to 2 right?

Yes, but your formula only evaluates cell E4 when C4 contains "2L". So there's your problem.

[sktneer]

Try this....

Please Login or Register  to view this content.

With the formula above, while C6=W1 and E6=72, the formula returns 2. Is it right as per your logic?

[cwby44]

That seems to work perfectly. I appreciate it.

[sktneer]

Glad it helped.
If that takes care of your question, please mark your thread as Solved by selecting Thread Tools (just above your first post) --> Mark thread as solved.
Moreover you may also click on * (star) to Add Reputation to those who have put their time and efforts to help you in this forum. This is another way to say thanks to them.