Microsoft Excel Help! I have a list of prices?

Question

I have a list of prices for items from several dealers, the dealers do not have all the parts I need, I have therefore put a zero in their price for that item, i need to find the cheapest price from these so I am using the min function all is ok till I hit an item that is not stocked where I get a zero for the min calculation. I need to ignore the zeros any Ideas would be greatfully recieved!

I need to multiply these values If i leave blank and use the product function it treats that as a 1 not a zero! could i disable this??

Answer 1

I don't think the above answer will work if there are more than one 0's.

This formula will take the minimum of the non-zero numbers:
=MIN(IF(A1:A5<>0,A1:A5,""))

Input using Ctrl-Shift-Enter, rather than just Enter, since it's an array formula.

Good luck.
.

Answer 2

You can put a very large price number in the column so it automatically doesn't show those with a minimum calculation.

You could also try writing some vba code that adds a value to the cells with values = 0 and then search for the minimum.

I was playing around with conditional formating, but you need to remove the ones that don't have the pieces you're interested in first to make that use of that functionality.

Answer 3

you could also type the word unavailable in the list instead of leaving it blank...like the first answer suggets


you could also set up a FILTER, whereby you Do use the zero, and you can custom filter your price column to show only the prices greater than zero, and THEN use your min function...

Answer 4

Don't put in a zero for those that you don't have prices for. Put in a letter (x) or a character (-). Excel will ignore these in the min function.

Answer 5

omit the zero's leave it blank, excel recognizes the blank as zero

Answer 6

Here is one formula you can use

=IF (SMALL($A$1:$A$4,1)=0,SMALL ($A$1:$A$4,2),SMALL ($A$1:$A$4,1))

$A$1:$A$4 = Price range

It returns the smallest value that is not equal to zero.

============================================
Edit -
Thanks Aladou for showing the mistake.

I did another array formula for that.(Somewhat long)

=SMALL (((A1:A4)>0)* (A1:A4),COUNTIF ($A$1:$A$4,0)+1)