medianif -- unsure of how to specify multiple criteria within this formula

[Jennifre]

Hi, I'm using this formula to get a medianif. So I'm successfully using it for finding one criteria match (a match between one column of data and one row). I need to expand it to be able to match several options within its column HE matching.

Is it possible to do without majorly re-doing the formula? I love it when I get things working... I'm hoping it can simply be expanded to also match HE5, HE7, for example.

Thanks so much.... I really appreciate your help.

{=MEDIAN(IF(($DG$2:$DG$1364=$HE3),DT$2:DT$1364))}

 

[shg]

{=median(if( (cond1)*(cond2)*(cond3), range))}

 

[Jennifre]

Hey, thank you shg! I tried this though (below), and it gave me a #NUM! error.... I'm not sure what that means, nor how to correct it?

Thank you

=MEDIAN(IF(($DG$2:$DG$1364=$HE5)*($DG$2:$DG$1364=$HE6)*($DG$2:$DG$1364=$HE7)*($DG$2:$DG$1364=$HE8),DT$2:DT$1364))

 

[Aladin Akyurek]

Hi, I'm using this formula to get a medianif. So I'm successfully using it for finding one criteria match (a match between one column of data and one row). I need to expand it to be able to match several options within its column HE matching.

Is it possible to do without majorly re-doing the formula? I love it when I get things working... I'm hoping it can simply be expanded to also match HE5, HE7, for example.

Thanks so much.... I really appreciate your help.

Do not put the formulas between QUOTE's, use CODE tags instead...

You can remove extra parens from...

=MEDIAN(IF(($DG$2:$DG$1364=$HE3),DT$2:DT$1364))

so that it becomes:

=MEDIAN(IF($DG$2:$DG$1364=$HE3,DT$2:DT$1364))

Expanding this 'to also match HE5, HE7'...

Two approches:
Control+shift+enter, not just enter...
1]

=MEDIAN(
   IF(($DG$2:$DG$1364=$HE3)+($DG$2:$DG$1364=$HE5)+($DG$2:$DG$1364=$HE7),
     DT$2:DT$1364))

2]

=MEDIAN(
  IF(ISNUMBER(MATCH($DG$2:$DG$1364,CHOOSE({1,2,3},$HE3,$HE5,$HE7),0)),
    DT$2:DT$1364))

If the criteria range (the range which houses criterion values) is contiguous like in HE5:HE8...

Control+shift+enter, not just enter:

=MEDIAN(IF(ISNUMBER(MATCH($DG$2:$DG$1364,$HE5:$HE8,0)),DT$2:DT$1364))

will suffice.

 

[Jennifre]

Oh my, great + SO helpful, Aladin -- thank you!!
I have a bunch of additional questions, unfortunately.

A)
I understand your suggestions 1 + unnumbered 3. But... I'm not sure what this part of #2 means -- what does the CHOOSE and the 1,2,3 apply to?

CHOOSE({1,2,3}</pre>
B)
Also, THANK YOU so much for your suggestions about how to properly show code here, I appreciate that.

Ca)
After I get this, I will need to weight the rows I'm median-ing by how many survey responses there are in each row. I guess that will need to happen in another cell altogether.

Cb)
Or is there a way to make these rows weighted by how many responses they each contain so all of their end-median values will be weighted from the get go so I only need to total them, and the total will already be weighted?

 

[Jennifre]

And too, thank you so much for the #1 answer which worked perfectly for me, and I even understand it. Thank you!!

 

[Jennifre]

Also, when I posted that, the mouseover for the code tags wasn't appearing at mouseover, so I didn't see it though [oddly?] pasting in the code bit appeared in a box on my screen... until I hit "post". But thank you again for that tip. e

 

[Jennifre]

Cc) I'll also need to weight averages. Not sure if that matters, but I have a similar thing set up in other columns averaging these responses, which I will need to weight similarly, per the #s of responses per row (the rows are collecting responses based on other criteria). I know... confusing.

Thank you for any help that comes in once I've confused everything as I have.

 

[Aladin Akyurek]

Oh my, great + SO helpful, Aladin -- thank you!!

You are welcome.

I have a bunch of additional questions, unfortunately.

A)
I understand your suggestions 1 + unnumbered 3. But... I'm not sure what this part of #2 means -- what does the CHOOSE and the 1,2,3 apply to?


CHOOSE({1,2,3}

{1,2,3} means that CHOOSE must evaluate all of the three terms it's fed with.
Here I'm calling upon a technique I introduced (some time ago) to union cells/arrays. With single cell arguments, CHOOSE is indeed capable of creating a "range"...

HE3,HE5,HE7 are collected in such a way that they become an array that can be fed to MATCH which expects a contiguous cell range or an array (vector). Recall that

MATCH(X2,(A2,A7,H5),0)

won't work for A2,A7,H5 together is not a contiguous cell range.

CHOOSE({1,2,3},A2,A7,H5)

makes an array MATCH would understand.
</PRE>

[...]

Ca)
After I get this, I will need to weight the rows I'm median-ing by how many survey responses there are in each row. I guess that will need to happen in another cell altogether.

Cb)
Or is there a way to make these rows weighted by how many responses they each contain so all of their end-median values will be weighted from the get go so I only need to total them, and the total will already be weighted?

Cc) I'll also need to weight averages. Not sure if that matters, but I have a similar thing set up in other columns averaging these responses, which I will need to weight similarly, per the #s of responses per row (the rows are collecting responses based on other criteria). I know... confusing.

Thank you for any help that comes in once I've confused everything as I have.

I think we need a small example here in order to understand the nature of the calculation you need.