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, 2022 in Others by Kithuzzz
• 38,000 points
906 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, 2022 by narikkadan
• 63,600 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, 2022 in Others by gaurav
• 23,260 points
1,362 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, 2022 in Others by Edureka
• 13,690 points
987 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

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

answered Feb 18, 2022 in Others by gaurav
• 23,260 points
3,710 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, 2022 in Others by narikkadan
• 63,600 points
1,113 views
0 votes
1 answer

EXCEL-Function to find how many people does the oldest female live with

You can try something like this: =ROWS(FILTER(B11:B9291, T11:T9291=MAXIFS(T11:T9291,G11:G9291,2))) It ...READ MORE

answered Jan 13, 2023 in Others by narikkadan
• 63,600 points
627 views
0 votes
1 answer

Excel feet and inches to millimeters

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

answered Feb 23, 2022 in Database by gaurav
• 23,260 points
1,849 views
0 votes
1 answer

Convert image (jpg, png, jpeg) to base64

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

answered Sep 24, 2022 in Others by narikkadan
• 63,600 points
1,283 views
0 votes
1 answer

Excel If field with rounding

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

answered Sep 27, 2022 in Others by narikkadan
• 63,600 points
831 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, 2022 in Others by narikkadan
• 63,600 points
3,597 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, 2022 in Others by narikkadan
• 63,600 points
1,393 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