CODEWITHSUNDEEP
excelexcel-formulaexcel-2010excel-2007excel-2013
ichimok
ichimok

Reputation: 3077

Median/average does not return the right values

Image for reference

enter image description here

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).


text1                   

list 1      list 2      list 3
text2       1           5
text4       2           4
text1       4           6
text4       1           6
text1       4           5
text4       2           4
text1       3           3

Upvotes: 1

Views: 2832

Answers (2)

barry houdini
barry houdini

Reputation: 46371

You can't use AND function in these type of formulas because AND returns a single result (TRUE or FALSE) not an array as required.

Your second formula is closer but by multiplying all the conditions you will get zeroes for every row where the conditions are not met, hence skewing the results.

You can use either one of these similar versions:

=MEDIAN(IF((C4:C10>B4:B10)*(A4:A10=A1);C4:C10-B4:B10))

=MEDIAN(IF(C4:C10>B4:B10;IF(A4:A10=A1;C4:C10-B4:B10)))

both need to be confirmed with CTRL+SHIFT+ENTER

To handle text in columns B or C (and to make the formula ignore those rows but work otherwise) you can add an extra IF function like this

=MEDIAN(IF(C4:C10>B4:B10;IF(A4:A10=A1;IF(ISNUMBER(C4:C10-B4:B10);C4:C10-B4:B10))))

All formulas will work equally well with AVERAGE function in place of MEDIAN

Another way to get the MEDIAN while ignoring text is to use AGGREGATE function like this:

=AGGREGATE(17;6;C4:C10-B4:B10/(C4:C10>B4:B10)/(A4:A10=A1);2)

That doesn't need "array entry" but will only work in Excel 2010 or later versions. There's no simple equivalent for AVERAGE

17 denotes QUARTILE function - second quartile is the equivalent of median

See attached screenshot demonstrating the last two formulas with your sample data....and some added text

Excel screenshot

Upvotes: 3

Marcel
Marcel

Reputation: 2794

Supposing that the values in column C that is list 3 are bigger than those in column B that is list 2, then you can use the following formula:

=MEDIAN(IF((A4:A10=A1)*(C4:C10>B4:B10);C4:C10-B4:B10))



this is an array formula, so press ctrl+shift+enter to calculate the formula. tell me if it doesn't work.

Upvotes: 1

Related Questions