0

I would like to calculate Median of values in a column F, if the value in Column I contains one of the NUMBERS in a range (N5:N10).

Currently I am calculating using the following formula :

=MEDIAN(
      IF(
         OR(I4:I466 = N5, I4:I466 = N6,  I4:I466 = N7 ),
         F4:F466)
   )

Can anyone help me to replace the OR statement in the formula I am using ?

Thanks

Rahul

1 Answer 1

0

enter image description here

How it works:

You need to use an array (CSE) formula to compare two data range.

  • Formula in cell E58:

    {=IFERROR(IF(MATCH(TRUE,COUNTIF(E$51:E$55,F$51:F$55)>0,0),MEDIAN(D51:D55),0),"")}
    

N.B. Finish formula with Ctrl+Shift+Enter.


  • Cell E57 has your formula:

    {=MEDIAN(IF(OR(E51:E55=F51,E51:E55=F52,E51:E55=F53,E51:E55=F54,E51:E55=F55),D51:D55,0))}
    

N.B.

  • It must be an array (CSE) formula, and need to finish with Ctrl+Shift+Enter.

  • Adjust cell references in the formula as needed.

6
  • Thanks Rajesh for such an illustrative example. You saved my day😊
    – Rahul
    Apr 27, 2020 at 12:59
  • Glad to help U @Rahul,,, since now the suggested method is useful to you then, You may accept it as answer & vote too Apr 28, 2020 at 5:44
  • @Rajesh...I have voted but its not showing as my reputation is less than 15. Hope it will be shown soon. Thanks again for your efforts
    – Rahul
    May 1, 2020 at 14:46
  • @Rahul,, no issue but you may accept it as an Answer,, just click on grey tick mark on left top of my post,, will turn to green ! ☺ May 2, 2020 at 6:02
  • That's already done 😉👍
    – Rahul
    May 2, 2020 at 15:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.