Return lowest price, based on multiple stock info

[Brian.Romer]

Hi all,
I am trying to make a formula in Excel but it's really causing lots of headache.

The case:
I have 3 "sets" of values with price and stock respectively.
Column A: Price, supplier 1
Column B: Stock, supplier 1

Column C: Price, supplier 1
Column D: Stock, supplier 1

Column E: Price, supplier 1
Column F: Stock, supplier 1

So far I fixed the general minimum price (=min(A2,C2,E2) and it's working fine.

What I need now is a formula that will look at the 3 stock values (B, D and F) and return the lowest price of the "set" that has more than 0,5 in stock (ie. B is 0, D is 5 and F is 0 - return C as price / B is 2, D is 5, F is 0 - return lowest price of B or D).
My guess is that the part of the first formula, if using IF should be: =IF(AND(B2>0,5,D2>0,5,F2>0,5);G2) - Right?

I hope it makes sense.

(By the way, this formula didn't work: =IF(AND(B2>0,5,D2>0,5,F2>0,5),G2,IF(B2>0,5,A2,C2))

[Sam Capricci]

maybe this... {=MIN(IF(B2:F2>0,B2:F2))}
Notice it is an array formula so you paste =MIN(IF(B2:F2>0,B2:F2)) into a cell then while in the function bar activate it by clicking on ctrl alt enter at the same time so the {} appear on either end of it. You cannot put those around it.

Oh, and not sure but you may need to replace commas with semicolons depending on your settings.

[mehmetcik]

Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Brian.Romer]

maybe this... {=MIN(IF(B2:F2>0,B2:F2))}
Notice it is an array formula so you paste =MIN(IF(B2:F2>0,B2:F2)) into a cell then while in the function bar activate it by clicking on ctrl alt enter at the same time so the {} appear on either end of it. You cannot put those around it.

Oh, and not sure but you may need to replace commas with semicolons depending on your settings.

Hi Sambo. Thanks for the tip but it didn't give the result I wanted. It showed the value of the stock and not the price.
I have attached my sheet.
*By the way, CTRL ALT Enter did not work for me. However, CTRL SHIFT Enter did...

[Brian.Romer]

Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Hi Mehmet. File attached now.

[Sam Capricci]

sorry about the alt, yes it is ctrl shift enter.
But there is no attachment. Did you follow the banner at the top's instructions?

[Brian.Romer]

Here it is.

[Sam Capricci]

how many of these price/stock will you have? Your example shows 3 but how many realistically can there be?

[Brian.Romer]

how many of these price/stock will you have? Your example shows 3 but how many realistically can there be?

Probably just the 3...might be 4 or 5 but that is not in the loop at the moment.

[JeteMc]

Very similar to Sam's formula, however since you are using the 365 version you only need to confirm by pressing the enter key: =MINIFS(A2:E2,A1:E1,"Price*",B2:F2,">=0.5")
Let us know if you have any questions.

[Brian.Romer]

Very similar to Sam's formula, however since you are using the 365 version you only need to confirm by pressing the enter key: =MINIFS(A2:E2,A1:E1,"Price*",B2:F2,">=0.5")
Let us know if you have any questions.

Hi JeTeMc,
Thanks. It worked.
I suppose it is possible to use "*" in front of Price as well to wild card search if there is text in front of the "price" text?


Edit:
When using this on our remote desktop I realize that our remote server is using Excel 2016 Any ways to use the same formula in Excel 2016? It works even with "*" in front of "Price" in my Excel 36´5, but I get a False error on the server with Excel 2016

[Brian.Romer]

Hi again,
In addition, I have another problem with the formula:
When I copy/Paste (as values) from a data sheet and in to the Price stock fields, I get only "0" in value in the entire Min. Stock price column.
Also, another issue: I need blank cells to be ignored. So if a Price and/or stock cell is blank (no values), it need to be counted as Zero or ignored if possible.

And as stated earlier, it doesn't work on our remote desktop using Excel 2016.

[Sam Capricci]

I can get there with this array if I use max...
=MAX(IF((LEFT(A1:F1,5)="Price")*(A2:F2>0),A2:F2))
(ctrl/shift/enter)

Can't address the copy/paste issue as it works for me no matter which formula I use.

[Brian.Romer]

I can get there with this array if I use max...
=MAX(IF((LEFT(A1:F1,5)="Price")*(A2:F2>0),A2:F2))
(ctrl/shift/enter)

Can't address the copy/paste issue as it works for me no matter which formula I use.

That didn't work at all. Even with price 1 being 100 and no stock it was selected.
But thanks for the reply

[JeteMc]

MINIFS is supported by the 2019 and 365 versions, so we will not be able to use it.
Wildcards can only be used with certain functions, not including MIN or IF.
One solution might be to use the following array entered formula*: =MIN(IF(ISNUMBER(SEARCH("Price",A1:E1)),IF(B2:F2>0,A2:E2)))
* See post #6.
Let us know if you have any questions.

[Brian.Romer]

MINIFS is supported by the 2019 and 365 versions, so we will not be able to use it.
Wildcards can only be used with certain functions, not including MIN or IF.
One solution might be to use the following array entered formula*: =MIN(IF(ISNUMBER(SEARCH("Price",A1:E1)),IF(B2:F2>0,A2:E2)))
* See post #6.
Let us know if you have any questions.

Hi there,
It worked like a charm as soon as I locked top row ($A$1:$E$1). Now working on both platforms.
Thanks a lot for the help

[JeteMc]

You're Welcome. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

[dannyleroux]

Hi guys, really hoping I don't get shamed for replying on this thread 2 years later. This post helped me not lose my mind as I could finally sort out an issue I had.
I do however sit with a problem where, referencing to the example sheet Brian attached, when all suppliers are out of stock the formula outputs 0. Is there a way I can alter the formula that should no supplier have stock that it still outputs the MIN of all prices, but should one supplier suddenly have stock that it goes through the initial formula? I see in the example sheet that there is a separate column for that but I would like to keep it in the same column.

Any advice would be appreciated and sorry again for re-opening this thread.

[Sam Capricci]

danny, welcome to the forum. Please check the rules, particularly #4... 4. Do not post a new help request in an existing thread.
please start your own post, you can copy the link to this thread if you think the information in this post is important.
AND it is always helpful to upload a workbook per the instructions in the yellow banner at the top of the post to get the best help.
Just remember, to open a new post doesn't cost anything AND any resolutions offered will be specific to your needs.