Array Formula?

[normpam]

E F G H I

Company A Comp B Comp C
$1.00 $.30 $.24 =min(E2:G2) Comp C
$.53 $.62 $.51
$.40 $0.00 $.46

The simple MIN formula will work fine, but what if one of the companies has a 'zero' price for an item (cell F4), and the MIN formula must ignore that cell. So, in words..... return the minimum value of the three cells, but ignore any cell with a zero value. Also, would it be possible to have Excel populate the cell in Column I with the name of the company with the lowest price (except for the zero prices). Not sure if a VLookup can do this...

Thanks for any ideas.

 

[Eric W]

One option:

Book1
	E	F	G	H	I
1	Company A	Comp B	Comp C	Min	Name
2	$ 1.00	$ 0.30	$ 0.24	$ 0.24	Comp C
3	$ 0.53	$ 0.62	$ 0.51	$ 0.51	Comp C
4	$ 0.40	$ -	$ 0.46	$ 0.40	Company A
Sheet9

Cell Formulas
Range	Formula
H2	=AGGREGATE(15,6,1/(1/(E2:G2)),1)
I2	=INDEX(E$1:G$1,MATCH(H2,E2:G2,0))

 

[normpam]

Thanks for the Aggregate formula idea... what is the 1/1 have to do with it?

One option:

Book1
	E	F	G	H	I
1	Company A	Comp B	Comp C	Min	Name
2	$ 1.00	$ 0.30	$ 0.24	$ 0.24	Comp C
3	$ 0.53	$ 0.62	$ 0.51	$ 0.51	Comp C
4	$ 0.40	$ -	$ 0.46	$ 0.40	Company A
Sheet9

Cell Formulas
Range	Formula
H2	=AGGREGATE(15,6,1/(1/(E2:G2)),1)
I2	=INDEX(E$1:G$1,MATCH(H2,E2:G2,0))

 

[kweaver]

For H4 (as well as the others): the first element of AGGREGATE is 15 = small; the 2nd is 6 = ignore errors.
Notice that (1/(E2:G2)) is ={2.5,#DIV/0!,2.17391304347826}. If you then take the reciprocal (by dividing into 1) you get: {0.4,#DIV/0!,0.46} which are the original decimal values.
The last 1 finds the 1st smallest value.

Hope this helps.

 

[normpam]

Thanks again.. I've been playing with the Aggregate formula and note that the number 6 refers to 'ignore error values'. But, are we not trying to ignore zero values? Why would ignoring error values work?

I still haven't any clue as to what the 1/1/ is doing....

 

[kweaver]

You want to ignore errors because, as you can see from my example, a divide by 0 error is created with the first division.
This is the first divide for H4: (1/(E2:G2)) is ={2.5,#DIV/0!,2.17391304347826}
That first division is 1 divided by each value from column E to G, which errors in the 2nd division (by 0),
then Eric then took the reciprocal of those (still ignoring errors), which is the second 1/, to get the original values.

 

[kweaver]

 

[Peter_SSs]

In H2 below is another option for the minimum non-zero value, but my main reason for posting is that I suspect with your real data it may be possible for two or more companies that have the equal lowest non-zero price.
Unless you only want the first of those 'equal companies' returned I have provided two options for returning the full list.

1. If you have the TEXTJOIN function in your version of Excel then you can use the formula in I2. It is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.

Excel Workbook
	E	F	G	H	I
1	Company A	Comp B	Comp C	Min	Name
2	1.00	0.30	0.30	0.30	Comp B, Comp C
3	0.53	0.62	0.51	0.51	Comp C
4	0.40	0.00	0.40	0.40	Company A, Comp C
Min Exclude Zero (1)

2. If you do not have TEXTJOIN then you can use this I2 formula (again employing the AGGREGATE, ignore errors concept) copied across and down.

Excel Workbook
	E	F	G	H	I	J	K
1	Company A	Comp B	Comp C	Min	Name
2	1.00	0.30	0.30	0.30	Comp B	Comp C
3	0.53	0.62	0.51	0.51	Comp C
4	0.40	0.00	0.40	0.40	Company A	Comp C
Min Exclude Zero (2)