Try this, entered as an array formula by holding down ctrl+shift while you hit Enter
=MEDIAN(IF($B$1:$B$10={"A","C"},$A$1:$A$10))
The IF function returns an array of either the appropriate value in A1:A10, or FALSE. And the MEDIAN function will ignore the logical values, since they are produced by a function, and not manually entered.
Here is a screen shot showing the data and the result. You can see the formula in the formula bar: (Note that the braces {...} around the formula are NOT added manually. They will be added by Excel when you confirm the formula with ctrl+shift+enter. With regard to the array constant, however, those braces you do type in manually.
Try this, entered as an array formula by holding down ctrl+shift while you hit Enter
=MEDIAN(IF($B$1:$B$10={"A","C"},$A$1:$A$10))
The IF function returns an array of either the appropriate value in A1:A10, or FALSE. And the MEDIAN function will ignore the logical values, since they are produced by a function, and not manually entered.
Here is a screen shot showing the data and the result. You can see the formula in the formula bar: (Note that the braces {...} around the formula are NOT added manually. They will be added by Excel when you confirm the formula with ctrl+shift+enter. With regard to the array constant, however, those braces you do type in manually.
Try this aggregate Function:
=AGGREGATE(17,6,$A$1:$A$500/(($B$1:$B$500 = "A")+($B$1:$B$500 = "C")),2)
This is entered as normal formula. It is a non CSE array formula.
I’m using a dataset with thousands of information on it. After filtering the dataset, only 8 rows of data is shown. I used the simple formula "=MEDIAN(i300:i1400)" but it did not give me the median of the 8 visible cells. I calculated it myself to be certain. Is it possible that instead of just using the 8 visible rows, it calculated the entire original data set between those cell numbers (i300 and i1400)?
ETA: I decided to try =ROW(i300:i1400) to see if it would reference the correct amount of rows and it didn't. It gave me 1100 when I want it to give me 8 because thats how many visible rows there are. Hopefully that makes more sense.
Between I300 and I400 there are 8 visible cells (after filtering it) that I want the median of. I do not want the median of 1100 cell numbers from the unfiltered data.
Assuming your categories are in cells A1:A6 and the corresponding values are in B1:B6, you might try typing the formula =MEDIAN(IF($A$1:$A$6="Airline",$B$1:$B$6,"")) in another cell and then pressing CTRL+SHIFT+ENTER.
Using CTRL+SHIFT+ENTER tells Excel to treat the formula as an "array formula". In this example, that means that the IF statement returns an array of 6 values (one of each of the cells in the range $A$1:$A$6) instead of a single value. The MEDIAN function then returns the median of these values. See http://www.cpearson.com/excel/arrayformulas.aspx for a similar example using AVERAGE instead of MEDIAN.
Make a third column that has values like:
=IF(A1="Airline",B1)
=IF(A2="Airline",B2) etc
Then just perform a median on the new column.
Here's what I'm trying to do. I have two columns in Sheet 1: 1) whether they are First-Gen or not (#N/A) based on an XLOOKUP to a hidden sheet, and 2) a column that displays salary.
In Sheet 2, I'm trying to calculate =MEDIAN(FILTER('Sheet 1'!$Y:$Y,'Sheet 1'!$O:$O="First-Gen")) but it calculates #N/A even though there are salaries for some First-Gen individuals. When I use this same written format for other columns with other categories, it works just fine. I don't get it.
For example, I have something similar for "Male" in column F on Sheet 1, and the formula calculates fine in Sheet 2 when it is written: =MEDIAN(FILTER('Sheet 1'!$Y:$Y,'Sheet 1'!$F:$F="Male"))
Any assistance would be greatly appreciated.
I've used AVERAGEIF to work out the mean age of women (as opposed to men) in a dataset. I'd like to do something similar with the median.
There is no MEDIANIF function in Excel and I'd like to work out the median age of females ("F" in the gender column) in my sample. It was easy to work out the mean with AVERAGEIF but there is no MEDIANIF.
Is there a way of doing that?