converting an array formula to a regular formula

[bluefish44]

Hi All - I recently inheruted a spreadsheet that contains the following array formula

{MIN(IF('Raw Data'!$H:$H=$A2,'Raw Data'!$C:$C))}

Does anyone know of a way to convert this to a formula thats not an array?
Thanks in advance

 

[DonnyF]

=MIN(IF('Raw Data'!$H:$H=$A2,'Raw Data'!$C:$C))

 

[Marcelo Branco]

If you are using Excel 2016 try MINIFS
https://support.office.com/en-us/article/MINIFS-function-6ca1ddaa-079b-4e74-80cc-72eef32e6599

Otherwise, try to avoid references to entire columns in array formulas - use definite ranges. Something like
=MIN(IF('Raw Data'!$H$2:$H$100=$A2,'Raw Data'!$C$2:$C$100))
Ctrl+Shift+Enter

Adjust the range to the max expected (likely) number of rows

M.

 

[DonnyF]

why limit the range?

 

[inactiveuserps07]

@bluefish44, do you have the option of using a helper column? If so, let me know which column in the Raw Data tab can be used as a helper column (you can even hide it if you don't want to see it), and the name of the tab where this formula is going.

 

[Marcelo Branco]

why limit the range?

Because array formulas including SUMPRODUCT, by design, evaluate all rows in the range. Using H:H=A2 the formula evaluates (does the comparisons) in 1,048,576 rows causing detriment to the spreadsheet performance.
Another functions like COUNTIF(s), SUMIF(s) and similar are smart enough to evaluate only rows with data. So there is no problem in using references to entire columns in these functions.

M.

 

[Dave Patton]

Excel 2010
	A	B	C	D	E	F	G	H
1
2	Bob	5	5					Bob
3		5	2					Jill
4		5	6					Bob
5			7					Bob
6			10					Bob
7			12					Jill
8			14					Bob
9			16					Bob
10			1					Andy
Raw Data

Cell Formulas
Range	Formula
B3	=LARGE(INDEX(('Raw Data'!H2:H10=A2)*'Raw Data'!C2:C10,),COUNTIF('Raw Data'!H2:H10,A2))
B4	=AGGREGATE(15,6,1/('Raw Data'!$H$2:$H$10=A2)*'Raw Data'!$C$2:$C$10,1)
B2	{=MIN(IF('Raw Data'!$H:$H=$A2,'Raw Data'!$C:$C))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Marcelo Branco]

@Dave

You are right, all of the formulas you've suggested return correct results, but, in fact, they are array formulas in the sense they deal with arrays, spite they don't need to be confirmed with CSE.

Another possible formula without CSE would be
=SUMPRODUCT(MIN((H2:H10=A2)*C2:C10+(H2:H10<>A2)*9.99E+307))

I 'm trying to, mostly, warn about the use of references to entire column in array formulas.

M.