Exclude certain cells from min calculation if criteria not met

Hello all,

I am working a project to compare the lowest price of three companies on multiple products.

I want to add a column to show if the item is in stock. I have applied a formula to show if there is a sufficient number of items in stock producing either "Y" or "N". I need excel to check this column before telling me the lowest priced item. I have therefore worked out this formula for if all items are in stock

Formula:

Please Login or Register  to view this content.

.

( Please note the following part of the formula is to prevent errors

Formula:

Please Login or Register  to view this content.

)

My issue is that they are 11 possible outcomes in how the items could be in stock, I.E. all in stock, all out of stock, one in stock and the other two out of stock in various combinations.

Whilst the "false" part of the above formula could be substituted for each different possible solution, is there a simplified way of doing this?

I have attached a copy of my workbook for your ease.

Many thanks,

PER11

A simple array formula will work.

Formula:

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Thank you for your answer.

The problem I am having is that all parts of this formula are non consecutive. So excell needs to check G2, K2 and O2 and the min can be in either E2, I2 and M2.

The other problem there are a lot combinations of how this can play out so I don't want to write out each possible formula due to potential errors happening. So in other words, I need excel to check G2, K2 and 02 for any items in stock then work out which one is the cheapest.

i hope this clearer.

Thank you for your answer.

The problem I am having is that all parts of this formula are non consecutive. So excell needs to check G2, K2 and O2 and the min can be in either E2, I2 and M2.

The other problem there are a lot combinations of how this can play out so I don't want to write out each possible formula due to potential errors happening. So in other words, I need excel to check G2, K2 and 02 for any items in stock then work out which one is the cheapest.

i hope this clearer.

Originally Posted by per11

The problem I am having is that all parts of this formula are non consecutive.

It would appear that the problem you have is dismissing the suggestion that has been provided without actually trying it.

If you can find any scenario where it doesn't do what you need then please post a sample showing this, and highlight what should be the correct result.

The only thing that I will point out is that the formula will show 0 if no supplier can meet your requirement, given that you didn't specify what should be shown if this happens, I left it as default.

Sorry Jason, I didn't mean to appear dismissive, I was having a pretty bad day yesterday.

Your formula does work, thank you very much.

Just a quick question, how would I have the formula return "out of stock" if the condition is not met. I have been trying to figure this out but to no avail.

Use this array formula :

Please Login or Register  to view this content.

This should do the trick

=IFERROR(1/(1/IF(ISBLANK(B2),"",MIN(IF(G2:O2="Y",E2:M2)))),"Out of stock")

Array confirmed as before.

If you have any FOC / 0.00 price items then they could trip the formula up, a slightly longer formula can cope with that if needed (not as long as sanram's )

It does do the trick!

FOC and 0.00 costs items aren't an issue in this instance.

There is a slight issue as the

Formula:

Please Login or Register  to view this content.

part of the error handler is not included.

I have come up with this to handle a price not being entered immediately.

Formula:

Please Login or Register  to view this content.

but for whatever excel cannot deal with the "out of stock" issue and returns a zero instead. Is there a way to combine both error handlers in the formula before the min calculation?

Sorry I will try to be clearer, the "Out of stock message" shows for items in a row with no cell contents. It does however work for all instances (that I can see). Is there someway to resolve this issue?