Formula based on lowest value of A and maximum value of B

[KnightedHawk]

Hey guys,

First of all, let me say thankyou for having such a large database of past q&a's, it has been very helpful to me in the past when googling how to do something specific. Alas, google has now failed me, so I turn to you.

A-Desired Quantity
B-Supplier 1 Price
C-Supplier 1 Qty
D-Supplier 2 Price
E-Supplier 2 Qty

So in row F, I want to find the Min value of B and D and LIMIT it to the quantity associated with it. So if Supplier 1 has the lowest price but 10 units, then I want F to represent 10 (if A > 10). I can (easily) find the minimum price, but I can't figure out how to restrict the quantity based on the corresponding maximum value in C and E and my own desired quantity in A. And then of course I'd like to have the second supplier fill my remaining quantity into G or something, but that's another task.

I've tried inserting a custom data validation setting restricting the limit into F based on C but if it went over I just got an error message because it was too high a result (the result didn't cap itself). I think I can get around it by doing something like =MIN(IF(B2<D2, MIN(A2,C2))) which would give me a result of either the lowest number of units I want to order from supplier A if he was the lowest based supplier but then I'd have to do repeating formulas for if Supplier 2 had the lowest price etc and more formulas for second-cheapest supplier (ie, if supplier 1 has the lowest price but a lower quantity then I want to order, I have to order from 2 suppliers) and it grows exponentially (2 suppliers=4 formulas, 3 suppliers=9)... I think that makes sense? If it doesn't just ignore me, I'm slightly delirious from trying to work this one out. Thanks

[FDibbins]

Hi, welcome to the forum

1st, thanks for the kind words

2nd, Im not really sure I understand what you want here, perhaps it would help if you could upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

I have a feeling you are looking at using the AND() function, something like...
AND(min(proce1,price2,max(QTY1,QTY2))
or something like that

[KnightedHawk]

Ok, here is a basic csv file with an example (I am using excel, but the attachments won't open a window to choose a file from)

https://docs.google.com/spreadsheets...it?usp=sharing

[FDibbins]

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[canapone]

Hi All,

just for fin and for sharing some ideas, in the attachment 5 suppliers and some formulae (orange background)

Please Login or Register  to view this content.

to create a couple of helper row.

I get units to order (green background) from

Please Login or Register  to view this content.

Hope it could be of some help.


--------------


Thanks for sharing kind feedback