CODEWITHSUNDEEP
arraysexcelformula
Rik
Rik

Reputation: 1987

Express array formula as non-array formula in Excel

All,

I have the following equation: {=MEDIAN(IF(INDIRECT($F$80)=A87, INDIRECT($E$80))}

Where F80 = "input!Q2:Q545" and where E80 = "input!B2:B545"

Basically this formula finds the median of one column where the other column is a set value (found in cell A87).

Can I write this as a non-array formula (without curly brackets)

Thanks, -Rik

Upvotes: 0

Views: 226

Answers (2)

Grade 'Eh' Bacon
Grade 'Eh' Bacon

Reputation: 3843

This formula will not work in 1 cell if it is not an array formula. This is because the IF statement is used against multiple cells. To write this without an array formula you would need to put an individual formula in each cell from R2:R545 which says:

=if(Q2=$A$47,B2)

And then a cell at the bottom that says: =median(R2:R545)

Upvotes: 1

XOR LX
XOR LX

Reputation: 7762

If you have Excel 2010 or later:

=AGGREGATE(16,6,INDIRECT($E$80)/(INDIRECT($F$80)=A87),1/2)

If not:

=MEDIAN(IF(MMULT(0+(INDIRECT($F$80)=A87),1),INDIRECT($E$80)))

Regards

Upvotes: 3

Related Questions