dimanche 19 avril 2015

Median/average does not return the right values

Picture for reference


I'm trying to achieve the following: if(cell A1 is found in list 1), for each row in which it's found and if(C4:C10 > B4:B10), then median(the subtraction between C and B values, for every row that has text1).


I've tried two 2 different formulas:


1 - {=MEDIAN(IF(AND((C4:C10>B4:B10);(B4:B10=A1));(C4:C10-B4:B10)))}


2 - {=MEDIAN((C4:C10>B4:B10)*(B4:B10=A1)*(C4:C10-B4:B10))}


For median it always returns 0 and for the average really small values that aren't accurate. I'm sure the median and the averages aren't correct.


What would the problem be?


Also, how would I use something like: {=MEDIAN((C4:C10>B4:B10)*(B4:B10=A1)*(C4:C10-B4:B10))}


If one the columns had text in some rows? (which isn't the case for the former problem, but it has arisen before).


Aucun commentaire:

Enregistrer un commentaire