Help needed with Median If in Excel

0 votes

I need to return a median of only a certain category on a spreadsheet. Example Below:

Airline    5
Auto       20
Auto       3
Bike       12
Airline    12
Airline    39

etc.

How can I create a formula that just returns the airline categories' median value? like Average if, but only for median. I am unable to change the values.

Nov 10 in Others by Kithuzzz
• 20,660 points
27 views

1 answer to this question.

0 votes
Try entering the formula =MEDIAN(IF($A$1:$A$6="Airline",$B$1:$B$6,"")) in another cell and clicking CTRL+SHIFT+ENTER if your categories are in cells A1:A6 and the associated values are in cells B1:B6.

Excel is instructed to treat the formula as an "array formula" when CTRL+SHIFT+ENTER is used. This means that instead of returning a single value in this case, the IF statement instead produces an array of 6 values (one for each cell in the range $A$1:$A$6). The median of these numbers is then returned by the MEDIAN function.
answered Nov 10 by narikkadan
• 37,660 points

Related Questions In Others

0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

If you need to test a condition, ...READ MORE

answered Feb 17 in Others by gaurav
• 22,040 points
72 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17 in Others by Edureka
• 13,640 points
109 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

In this case, you can use nested ...READ MORE

answered Feb 18 in Others by gaurav
• 22,040 points
71 views
0 votes
1 answer

How to create a drop-down in excel with custom values

You can accomplish that using code rather ...READ MORE

answered Sep 25 in Others by narikkadan
• 37,660 points
67 views
0 votes
1 answer

Excel feet and inches to millimeters

There are certain direct formulas to convert ...READ MORE

answered Feb 23 in Database by gaurav
• 22,040 points
558 views
0 votes
1 answer

Convert image (jpg, png, jpeg) to base64

Try this - it will perform the ...READ MORE

answered Sep 24 in Others by narikkadan
• 37,660 points
92 views
0 votes
1 answer

Excel If field with rounding

Here's your formula. Assumes your cell containing ...READ MORE

answered Sep 27 in Others by narikkadan
• 37,660 points
52 views
0 votes
1 answer

IF formula to compare a date with current date and return result

You can enter the following formula in ...READ MORE

answered Sep 27 in Others by narikkadan
• 37,660 points
60 views
0 votes
1 answer

IF function in combination with an Round function Excel

I'm not sure if it is a ...READ MORE

answered Sep 25 in Others by narikkadan
• 37,660 points
59 views
0 votes
1 answer

How to print an Excel Sheet using VBA with Nitro PDF Creator in Excel 2016

you can use the built-in excel facilities ...READ MORE

answered Sep 24 in Others by narikkadan
• 37,660 points
119 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP