There's not an inbuilt function to cater for the conditional mode or median. So we need to use an array function.

=MODE(IF($A:$A=D1,$B:$B))

If you have supplier names in column A and their delivery lead times in column B, the above formula finds out what the mode of those delivery times is for the supplier referenced in cell D1. Change *MODE* to *MEDIAN* with obvious results.

Because the formula itself references an array, you'll need to hit CTRL+SHIFT+ENTER instead of just ENTER when you've finished writing the formula. Excel will put some lovely curly brackets around the formula for you, reminding itself that arrays are contained therein.

Happy days.

Hi Dan, found your website really helpful, great stuff. I'm trying to

construct a COUNTIF formula that looks at two columns & counts the nbr of duplicates. Problem is i'm trying to get the formula to only count the duplicates total in the column B if text in column A is "X".

Example:

X 12345

X 56789

X 12345

X 11111

X 22222

X 12345

Y 22222

Y 22222

Y 33333

Y 44444

Any ideas would be greatly apprecaited.

John

I have a sample table as below and want to calculate the Average, Mode, Min, Max, and Median.

I entered each of the formulas using CTRL+SHIFT+ENTER:

=AVERAGE(IF(Table2[Column1]= 2, Table2[Column2]))

=MIN(IF(Table2[Column1]= 2, Table2[Column2]))

=MAX(IF(Table2[Column1]= 2, Table2[Column2]))

=MEDIAN(IF(Table2[Column1]= 2, Table2[Column2]))

=MODE(IF(Table2[Column1]= 2, Table2[Column2]))

Using a C+S+E, a pair of braces were placed around the entire formula(s)

All of them except Mode return the correct value. Mode returns a #N/A. Removing the {} from the Mode returns a #VALUE.

What am I not seeing.

Excel version is 2007

Column1 Column2

1 5

2 10

3 25

1 50

4 125

4 75

3 150

2 175

2 200

Never mind, I found the problem. There is no Mode in this table. Duh!

Thank you, this was very helpful.

Hi Frank, I need some help on this one please.

I want to construct a formula to give me a value in a cell if a condition is met, the conditions are:-

If the value in cell C1 is greater than cell D2 then E2 should be a positive value and if C1 is less than D2 the E2 should also give a positive value. It has to be a positive value so that other cells on the sheet work correctly.

At the moment I get a - value or a + value, any Ideas?

You need ABS(C1-D2) I think, Bob.

Dan.

I work with a hospital system that retrieves multiple data (28 facilities). I have three columns - column A (28 different facilities), column B (various discharge dispositions), Column C (LOS in Minutes) I am trying to capture the median LOS in Minutes when I filter on a particular hospital showing all discharge dispositions

For example:

Col A Col B Col C

Hosp A Routine Discharge 150

Hosp A Admit to Inpatient 99

Hosp A Transferred to Beh Health 360

Hosp B Routine Discharge 75

Hosp B Routine Discharge 88

Hosp B Admit to Inpatient 80

Hosp B Admit to Inpatient 101

Hosp B Transferred 55

Hosp B Transferred 99

Hosp C Expired 75

Hosp C Admitted 66

Can you also use this same approach to create multiple conditions like the AVERAGEIFS function allows you to do? If I replace the IF with IFS in the example you gave when producing a MEDIAN will that work for me to your knowledge?

Good morning Dan

Do you know if Median works with the new IFS function in Excel 2016?

Look forward to hearing from you.

Kind regards

Richard

Hey is there a formula to calculate median if the mode formula returns #NA as value. Or selects the value from the neighboring cell?