Median of differences of columns, with embedded "if"-Frank?

  • Thread starter Thread starter ModelerGirl
  • Start date Start date
M

ModelerGirl

Yesterday, I receieved a great answer to my question about taking
median of differences between two columns. (Thanks so much, Frank!)
However, I need the median of the differences, only if another colum
is equal to a certain number.

So for example, I have two columns and am taking the difference betwee
the neighboring cells in each row. I'd like to take a median of thes
differences of each row where cell C=1. For example, if I have

Row A B C
1 10 17 1
2 100 3 0
3 25 31 1
4 10 20 0

Then I only want to take the median of B1-A1 and B3-C3, since only C
and C3 are equal to 1.

All help would be appreciated-this forum has been enormously helpful.

Thanks,

Kat
 
Sounds like you got from Frank what you want, except now you wish to place
"a condition" on whether you want it to apply or not, right? Use an If
function that does the following:
=If(TheConditionIsTrue,ApplyMyFormula,"") << the "" (two quotes represent
that you wish the cell to appear blank if the Condition is False)..

so in cell D1 enter:

=If(C1=1,B1/A1,"")
HopeThisHelps,
 
Something like this in D1:
If(C1=1,Your median formula,"")
This produces a blank cell if C <>1.
Drag this down like you did the original median formula. HTH Otto
 
Hi
try the following array entered formula (not fully tested though)
=MEDIAN(IF(C1:C15=1,ABS(B1:B15-A1:A15)))
 
Back
Top