Trouble with formulae? Lookups not going so well? The Wizard of Excel can help. Dan Harrison offers practical tips and examples, as well as Excel training and consulting.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?
I need some advice. I am trying to track performance metrics for my team of case managers. Part of our performance is based on the median earnings during the program year. I can manage an equation that counts the median earnings of the entire case load (thanks to your equation above) but I am having trouble constraining the results to a time frame. This is what I have so far:
=(countifs(Dashboard!$B:$B,"CaseManager",Dashboard!$T:$T,">0",Dashboard!$J:$J,">="&$M$1,Dashboard!$J:$J,"<="&$N$1)) B is the case manager name T is the median earnings J is their date of program exit M is the beginning of the program year and N is the end The results are dumped onto a "summary" page The problem is that, no matter the earnings, the equation returns 1 for each entry that it counts instead of the median. I'm doing this through Google Sheets. I'm willing to accept that it is not robust enough to handle this operation. Thank you for any help
I apologize, I grabbed the wrong equation from my spreadsheet and I cannot see a way to delete my prior post.
This is the formula that is giving me trouble:
=ArrayFormula(median(COUNTIFS(Dashboard!$B:$B,"CaseManager",Dashboard!$V:$V,">0",Dashboard!$J:$J,">="&$M$1,Dashboard!$J:$J,"<="&$N$1)))
I don't think you'd want a COUNTIFS here, simply an IF(AND(...
But I can't get this to work with multiple arguments. Sorry I can't be more helpful.
Thank you for looking into it for me. I have a fairly quick method I can use to calculate the medians I need manually so it's not a big loss. I just can't generate the information real-time. It's all part of the learning process.
No worries. I'm wondering whether you may be able to use some form of RANK function to get the rankings of the values that can then be used to find the halfway point.
Hello Dan,
I am having trouble figuring out how to do a conditional median.
Specifically, I have three cells of which I need the median (say, A, B, and C), but if any one of those cells are empty because of lack of data, I need to pull a median from a fourth cell (say, D). Cell D becomes a consideration, if and only if one of cells A, B, or C are missing.
Typically cell B is the missing cell, so even if a formula only considered B as the "if," I'd be grateful.
My goal is either to have the cell containing the median automatically highlight, or for it to calculate in a 5th cell, (say, E).
Any help or direction would be appreciated.
Thank you,
JC
Hi, I need some help here. I'm trying to find the mode of one column, but only if a condition in a different column is met. Here's an example:
x 3
x 6
y 5
x 3
y 3
x 5
y 6
x 3
y 3
x 5
x 6
x 3
y 6
y 3
x 5
I need to find the mode of values in the second column, but only if the corresponding cell in the first column has an x in it. Please help!
You need the following forumula:
=MODE(B:B*IF(A:A="x",1,RAND()))
When you've typed it, hit CTRL+Shift+Enter to make it an array formula.
This basically creates a temporary value of the value multiplied by 1 (which equals the value) if there's a x in column A. Or creates a random number if not. And takes the mode of those values.
The reason I've used a random number is to make sure that the numbers are all different. If I used a zero, then you would have lots of zeroes, and the mode would therefore be zero. By making it a random number for all non-x values, it means that they'll only appear once (hopefully) and so it will create a mode of only the whole numbers associated with the x entries.
Hope that helps.
Dan.
Hi there,
I need advice on how to pull the median along with other data elements. So for example, I want to median salary along with location, is that possible to do?
Hi There,
Struggling with an excel formula...
How would you extract the mode of a range based on meeting an identifying criteria within a large range?
The simplest way to do this is to create a secondary column that contains the value if meets the criteria, or blank otherwise. And do a mode of that.
So the secondary column is =IF(A2>5,A2,"")
And then do a mode of that.
Hi Dan
I have a 2 columns. 1 with stockcodes and the other with a length. The code are remain the same and the length keep changing. What formula can we used to pull out the most common used lenght. Please advise
It's not clear what you're needing, Shiraz. If you want to know the most common length, then it's just MODE(B:B) if your lengths are in column B.
Hi Dan,
I want my formula to calculate the mode but if there is no mode to return the max value. Below is the formula I'm using.
=if(iferror(MODE(H27,J27,M27,O27),MAX(H27,J27,M27,O27))=0,"",iferror(MODE(H27,J27,M27,O27),MAX(H27,J27,M27,O27)))
The data in the 4 cells is 2, 2, 3, 3. Since there is no mode, or that there are 2 modes, I want 3 to be returned since it is the max.
Dan,
In sheets , in column A will be a cost of between 0-100, 101- 200, 201-300, 301-500, 501-1000 and 1001 - ? I will add to that in order 50%,40%,30%,25%, 15%,and10% in column B I want the total,
Can you help please with a formula.
I'm looking for a way to find the median of a mid-range of values. I want to leave out a set of lower values, say <200, and also leave out higher values, >600. In other words, median of values between 200 and 600, for example.
I am attempting to identify the mode of column C, if column Y is >= 775 and column Y is <=825. =MODE('Raw Data'!C2:C10000, IF( 'Raw Data'!$Y$2:$Y$10000 >="775", 'Raw Data'!$Y$2:$Y$10000 <="825")) and =MODE(IF('Raw Data'!F2:F10000, 'Raw Data'!$Y$2:$Y$10000 >="775", 'Raw Data'!$Y$2:$Y$10000 <="825")) I know this is not correct, but am struggling to identify the solution.
Hi there,
I am using this formula and it works great! However, I am having one minor issue that is skewing the results of the formula. I have several 0 values in my data that are accurate, but I also have several that are null values and this is also valid. This formula seems to count the null values as zeros and this results in the formula not producing the correct results. Any ideas?
Thank you!
Hello, Dan. I have used Excel to find the mode within entries in several columns of data. However, I am trying to find a way to utilize Excel to calculate the 2nd most frequently occurring data entries, 3rd most frequently occurring data entries, 4th most frequently occurring data entries, and so forth. I have attempted to use the mode function while excluding the mode value to find the 2nd most frequently occurring value but failed. HELPPPPP!!! Thank you.