Median on non contiguous cells excluding zero

[MoldyBread]

Hello,

I would like to compute the median based on non contiguous cells excluding zero (because i want to use a boolean to select cells I want to include in the median)

A | D | F
0 | 1 | 1
1 | 2 | 3

Something like MEDIAN(IF({A1;D1;F1}*{A2;D2;F2}<>0;{A2;D2;F2})) or MEDIAN(IF({A1*A2;D1*D2;F1*F2}<>0;{A2;D2;F2}))

Could you please help me ?

Many thanks

[FlameRetired]

Since we do not know if A, D, and F share something in common and distinct from B, C, and E try array entering this formula. It is pretty close to what you already have.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Please Login or Register  to view this content.

Or this one you don't have to array enter.

Please Login or Register  to view this content.

[Pepe Le Mokko]

Administrative note
Please update your profile as necessary to properly reflect the exact version(s) of Excel your question relates to. Members tailor answers based on your Excel version. Your profile does not indicate your version.
Thanks

[MoldyBread]

Thank you Flame, to be clear, there are other things in columns B, C and E, that's why I cannot use any range like A1:F1.

Figures and boolean are located in non contiguous cells.

Thank you for your help

[AliGW]

As always, it would be so much easier if you provided a representative sample workbook ...

[MoldyBread]

Here it is, I would like to create a kind of conditional median function on non contiguous cells with the boolan used as the condition

Many thanks

[XLent]

edit: below composed prior to sample being provided.

so, perhaps, modifying earlier AGGREGATE suggestion to embed a CHOOSE

=AGGREGATE(16,6,CHOOSE({1,2,3},A2/(A2<>0)/A1,D2/(D2<>0)/D1,F2/(F2<>0)/F1),0.5)

[MoldyBread]

Works perfectly, thanks a lot

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.