Max/Min from 2 columns excluding outliers

[lucywhite]

Hi all, I am hoping someone has a good formula for me

I have data in 2 columns, labelled "value 1" and "value 2". Each row shows a range of data, however sometimes the maximum value of the two appears in the "value 1" column and sometimes in "value 2". Sometimes only one value is given (which may be either end of the expected range) therefore it appears in "value 1" and "value 2" is blank. It would usually be easy to determine the max and min, except that I want it to ignore outliers. I also can't use the "mode" function given that the missing data in the "value 2" column mean it is possible to end up with only the minimum value (for example) being the mode in both columns. I know that sounds a bit complicated so here is an example of data:

Value 1 Value 2
52 56
52 56
56 52
52
52
56 52
61 52
48
52 56
56

So I want it to show MIN=52 and MAX=56. I will have an unknown number of outliers. Is is possible to calculate more than one mode then take both as the min and max because that might actually work?

Thank you in advance for any suggestions

Lucy

[AliGW]

Welcome to the forum!

I am not sure I quite follow. Please explain exactly what you consider to be an outlier.

[PaulM100]

Two options depending on the Excevl version that you use:
2016 and up:

Please Login or Register  to view this content.

Or array entered(Ctrl+Shift+Enter instead of regular Enter):

Please Login or Register  to view this content.

[MrShorty]

I'm sure that some of the algorithm will depend on exactly how you are determining which ones are "outliers". Here's how I see it happening, but I could be misunderstanding something (note that this is not a single cell formula):

1) Enter a range with all of the expected values. Use a FREQUENCY() or COUNTIFS() function to count how many times a value occurs.
2) Use a MINIFS() and MAXIFS() (or equivalent) function to determine the min and max based on your "outlier" criteria. Since I am not sure what you outlier criteria are, I cannot be more specific.

[lucywhite]

Thank you! I'm a long time user of these but first time poster

In the above example, anything except for 52 and 56 is an outlier. I would expect that the mode and second most frequently occurring number are the two numbers I want, then I just need to sort them into min and max.

I just noticed it pushed the values together in the original post so it's not so easy to read. There are two columns of 2-digit numbers.

I don't have specific criteria for outliers, the closest I can say is as above: the top 2 most frequently occurring numbers are most likely the two that I want.

I don't use Excel all the time so am a little slow, I will have a play now with your suggestions and see if I can get them to work.

Thanks

[AliGW]

OK - let me rephrase my question! How will you determine what is NOT an outlier? In other words, how will Excel know which numbers it should work with and which ones it should disregard?

[63falcondude]

From post #5, it seems like you are defining an "outlier" as any number that does not show up a certain number of times. Based on that, you can try these:

=MIN(IF(COUNTIF(A2:B11,A2:B11)>2,A2:B11)) Ctrl Shift Enter

=MAX(IF(COUNTIF(A2:B11,A2:B11)>2,A2:B11)) Ctrl Shift Enter

These only consider numbers that show up more than twice.

[AliGW]

From post #5, ...

Which has now been edited to be more specific!!!

Lucy - thanks for the clarification, but it's best to add detail to the thread in chronological order, otherwise it can get confusing. My comment in post #6, for example, is now incongruous.

[63falcondude]

I see... These should do the trick for you then:

=MIN(MODE(IF(A2:B11<>MODE(A2:B11),A2:B11)),MODE(A2:B11)) Ctrl Shift Enter

=MAX(MODE(IF(A2:B11<>MODE(A2:B11),A2:B11)),MODE(A2:B11)) Ctrl Shift Enter

[lucywhite]

Two options depending on the Excevl version that you use:
2016 and up:

Please Login or Register  to view this content.

Or array entered(Ctrl+Shift+Enter instead of regular Enter):

Please Login or Register  to view this content.

Hi PaulM100, thanks for the response. I have tried this but it is still returning the actual minimum, even though it is an outlier? I'm not sure what "<>" means in the top suggestion. I am using Excel 2016. Thanks

[lucywhite]

I see... These should do the trick for you then:

=MIN(MODE(IF(A2:B11<>MODE(A2:B11),A2:B11)),MODE(A2:B11)) Ctrl Shift Enter

=MAX(MODE(IF(A2:B11<>MODE(A2:B11),A2:B11)),MODE(A2:B11)) Ctrl Shift Enter

Hi 63falcondude, I think this one might work in most of my situations, thanks! If it's not too much trouble, I would really appreciate it if you could explain what the formula is doing please?

[lucywhite]

Which has now been edited to be more specific!!!

Lucy - thanks for the clarification, but it's best to add detail to the thread in chronological order, otherwise it can get confusing. My comment in post #6, for example, is now incongruous.

Sorry AliGW, I think I was in the process of editing it while you posted the question, as I hadn't seen it until later, I was responding to the previous questions already! Otherwise it's my lack of experience with forums showing...

[AliGW]

Best thing really is NOT to edit to add detail - just add a new post instead.

[63falcondude]

Sure, I'd be happy to explain it.

=MODE(A2:B11) will return the most common number in A2:B11.

{=MODE(IF(A2:B11<>MODE(A2:B11),A2:B11))} will return the second most common number in A2:B11.

{=MIN(MODE(IF(A2:B11<>MODE(A2:B11),A2:B11)),MODE(A2:B11))} will return the smaller of the two. MAX will return the larger.

Going a little further into the explanation, the "2nd most common" formula goes through each number in A2:B11 and checks if it is not equal to the mode of A2:B11.
The formula will then take the mode of all of the numbers where that check resulted in TRUE.

It takes the most common value of the numbers that are not the most common value (in other words, the 2nd most common value).

[lucywhite]

From post #5, it seems like you are defining an "outlier" as any number that does not show up a certain number of times. Based on that, you can try these:

=MIN(IF(COUNTIF(A2:B11,A2:B11)>2,A2:B11)) Ctrl Shift Enter

=MAX(IF(COUNTIF(A2:B11,A2:B11)>2,A2:B11)) Ctrl Shift Enter

These only consider numbers that show up more than twice.

Hello again, I think this one will work. Now I need to integrate it into an existing array formula (my end goal is sadly more complicated than the original question).

Each row of the example is assigned a category, let's say A, B, C or D. I have a summary table of the complete dataset and I want it to tell me the most commonly occurring max and min value excluding outliers (as already covered) for each category. The current formula is

=MODE(IF($DX$4:$DX$387=DZ4,$CU$4:$CU$387,""))

but this one just looks in the "value 1" column, I need to tell it to look in
$DX$4:$DX$387 is the category name (A, B, C or D) for every row of data.
DZ4 is the category name in the row title in the summary table, "A" in this case.
$CU$4:$CU$387 is the data that it should look at to determine the min and max values, but only for the category described by DZ4. I need to expand CU to look at CV as well.

I hope I have explained it properly, please let me know if it is possible to put your clever formula into this one somehow?

Many thanks!

[63falcondude]

I recommend uploading a small representative sample workbook of your data along with the desired results (which you can enter manually) based on that data.

To upload an Excel workbook, follow these steps:
1) Click on "Go Advanced"
2) Click on "Manage Attachments"
3) Click on "Choose File"
4) Choose your file and click on "Open"
5) Click on "Upload"
6) Click on "Close this window"

[lucywhite]

Hi 63falcondude,

Hopefully you can see the attached data. I tried to make it the same as the original file with all the same steps. Please let me know if it is still not clear what I am trying to do, and thank you so much for your help and also the explanation

Actually as a side note, in the attached file, why isn't the ISBLANK working in the righthand columns?? I have this problem in my main template as well and can't figure it out!

[63falcondude]

Try these:

J5 =MIN(MODE(IF((B$4:B$130=G5)*(C$4:D$130<>MODE(IF(B$4:B$130=G5,C$4:D$130))),C$4:D$130)),MODE(IF(B$4:B$130=G5,C$4:D$130))) Ctrl Shift Enter

K5 =MAX(MODE(IF((B$4:B$130=G5)*(C$4:D$130<>MODE(IF(B$4:B$130=G5,C$4:D$130))),C$4:D$130)),MODE(IF(B$4:B$130=G5,C$4:D$130))) Ctrl Shift Enter

As for the ISBLANK question, try using C4="" instead of ISBLANK(C4).

[lucywhite]

Try these:

J5 =MIN(MODE(IF((B$4:B$130=G5)*(C$4:D$130<>MODE(IF(B$4:B$130=G5,C$4:D$130))),C$4:D$130)),MODE(IF(B$4:B$130=G5,C$4:D$130))) Ctrl Shift Enter

K5 =MAX(MODE(IF((B$4:B$130=G5)*(C$4:D$130<>MODE(IF(B$4:B$130=G5,C$4:D$130))),C$4:D$130)),MODE(IF(B$4:B$130=G5,C$4:D$130))) Ctrl Shift Enter

As for the ISBLANK question, try using C4="" instead of ISBLANK(C4).

That is amazing. Thank you so much! I don't know what the formula is doing, but whatever it is it works!

Also thanks for the workaround for that bug (I assume it is a bug causing the isblank to not work sometimes, as it usually works for me), so simple actually but it has been driving me crazy

[63falcondude]

You're welcome. Happy to help.

All that I did was add another criteria to the formulas in post #9. The * symbol acts as an AND statement.

The ISBLANK function checks whether a cell is empty (not just showing blank).
If that cell is showing blank as a result of a formula, there is still something in the cell (the formula) so ISBLANK will return FALSE.

If you then copy and paste the results as values, Excel will still not see those cells as empty until you clear them.

Thanks for the rep!