Express array formula as non-array formula in Excel

Question

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

have you just tried?

PA.
– PA.

2015-07-22 11:52:44 +00:00
Commented Jul 22, 2015 at 11:52 — PA.
– PA., Commented Jul 22, 2015 at 11:52

XOR LX · Accepted Answer · 2015-07-22 12:10:15Z

3

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

answered Jul 22, 2015 at 12:10

XOR LX

7,7821 gold badge19 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Rik Over a year ago

Any idea how to do modify the aggregate function to be the mode vs median?

XOR LX Over a year ago

Unfortunately that cannot be done so readily with the AGGREGATE set-up. Though the MMULT set-up is easily adapted for the MODE.

Grade 'Eh' Bacon · Accepted Answer · 2015-07-22 12:13:46Z

1

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)

answered Jul 22, 2015 at 12:13

Grade 'Eh' Bacon

3,8534 gold badges26 silver badges47 bronze badges

Collectives™ on Stack Overflow

Express array formula as non-array formula in Excel

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related