+ Reply to Thread
Results 1 to 5 of 5

Challenging issue: Detect Outliers in a Column with multiple groups of data

  1. #1
    Registered User
    Join Date
    10-02-2005
    Posts
    8

    Challenging issue: Detect Outliers in a Column with multiple groups of data

    Hello,

    I have a table with two columns. In one column there are the countries and in the other column some values (as below).

    COLUMN A COLUMN B
    Afghanistan 1209
    Afghanistan 300
    Afghanistan 214
    Afghanistan 134
    Afghanistan 95
    Afghanistan 64
    Afghanistan 50
    Afghanistan 48
    Afghanistan 48
    Afghanistan 44
    Afghanistan 44
    Afghanistan 38
    Afghanistan 36
    Afghanistan 31
    Afghanistan 29
    Afghanistan 29
    Afghanistan 29
    Afghanistan 28
    Afghanistan 25
    Afghanistan 24
    Afghanistan 22
    Afghanistan 22
    Afghanistan 21
    Afghanistan 20
    Afghanistan 20
    Afghanistan 16
    Afghanistan 15
    Afghanistan 14
    Afghanistan 13
    Afghanistan 13
    Afghanistan 13
    Afghanistan 13
    Afghanistan 13
    Afghanistan 12
    Afghanistan 12
    Afghanistan 12
    Afghanistan 11
    Afghanistan 9
    Afghanistan 9
    Afghanistan 8
    Afghanistan 7
    Afghanistan 7
    Afghanistan 6
    Afghanistan 4
    Afghanistan 4
    Afghanistan 3
    Afghanistan 2
    Afghanistan 1
    Afghanistan 1
    Afghanistan 1
    Albania 352
    Albania 351
    Albania 245
    Albania 240
    Albania 168
    Albania 166
    Albania 161
    Albania 147
    Albania 141
    Albania 138
    Albania 133
    Albania 127
    Albania 101
    Albania 94
    Albania 83
    Albania 78
    Albania 71
    Albania 61
    Albania 54
    Albania 44
    Albania 39
    Albania 16
    Algeria 1882
    Algeria 726
    Algeria 646
    Algeria 528
    Algeria 524
    Algeria 517
    Algeria 498
    Algeria 427
    Algeria 422
    Algeria 372
    Algeria 361
    Algeria 319
    Algeria 303
    Algeria 298
    Algeria 290
    Algeria 288
    Algeria 268
    Algeria 267
    Algeria 239
    Algeria 238
    Algeria 238
    Algeria 231
    Algeria 231
    Algeria 222
    Algeria 217
    Algeria 216
    Algeria 209
    Algeria 191
    Algeria 176
    Algeria 174
    Algeria 166
    Algeria 164
    Algeria 163
    Algeria 162
    Algeria 147
    Algeria 139
    Algeria 131
    Algeria 122
    Algeria 121
    Algeria 120
    Algeria 119
    Algeria 119
    Algeria 115
    Algeria 111
    Algeria 106
    Algeria 105
    Algeria 103
    Algeria 98
    Algeria 94
    Algeria 93
    Algeria 88
    Algeria 87
    Algeria 85
    Algeria 82
    Algeria 78
    Algeria 78
    Algeria 69
    Algeria 64
    Algeria 63
    Algeria 62
    Algeria 60
    Algeria 59
    Algeria 59
    Algeria 56
    Algeria 40
    Algeria 9
    Andorra 264
    Angola 234
    Angola 100
    Angola 58
    Angola 52
    Angola 49
    Angola 44
    Angola 37
    Angola 28
    Angola 28
    Angola 27
    Angola 27
    Angola 20
    Angola 10
    Angola 10
    Angola 9
    Angola 3
    Anguilla 191
    Antigua and Barbuda 557
    Antigua and Barbuda 268
    Argentina 9495
    Argentina 7807
    Argentina 4206
    Argentina 2875
    Argentina 2812
    Argentina 2753
    Argentina 2645
    Argentina 1798
    Argentina 1780
    Argentina 1768
    Argentina 1750
    Argentina 1711
    Argentina 1618
    Argentina 1538
    Argentina 1475
    Argentina 1246
    Argentina 1178
    Argentina 1178
    Argentina 1168
    Argentina 1111
    Argentina 1032
    Argentina 1030
    Argentina 1028
    Argentina 1017
    Argentina 941
    Argentina 922
    Argentina 920
    Argentina 903
    Argentina 892
    Argentina 825
    Argentina 768
    Argentina 767
    Argentina 764
    Argentina 759
    Argentina 742
    Argentina 697
    Argentina 679
    Argentina 648
    Argentina 628
    Argentina 613
    Argentina 596
    Argentina 595
    Argentina 515
    Argentina 512
    Argentina 507
    Argentina 506
    Argentina 487
    Argentina 465
    Argentina 460
    Argentina 446
    Argentina 438
    Argentina 432
    Argentina 406


    The columns are sorted by country first (column A) and by descending value (Column B) secondly. The above dataset is just a subset.

    I need to detect the upper outlier(s) for the dataset of each country. So for example in Afghanistan the values 1209 and possibly also 300 and 214 might qualify as outliers in the country dataset. I would like to apply a formula that detects those upper outliers for each country maybe by showing in a third column the string OUTLIER if the condition is true as below.
    I could create a formula for each country but there are 200 countries and thousands of value and finding a formula that can be just copied and pasted in the third column would be much easier.

    COLUMN A COLUMN B COLUMN C (formula to be copied and pasted)
    Afghanistan 1209 OUTLIER
    Afghanistan 300 OUTLIER
    Afghanistan 214 OUTLIER
    Afghanistan 134
    Afghanistan 95
    Afghanistan 64
    Afghanistan 50
    .
    .
    Albania 352 OUTLIER
    Albania 351 OUTLIER
    Albania 245
    Albania 240
    Albania 168
    .
    etc.


    I guess the problems are two:
    1) find the right formula to detect the outliers (first quartile and third quartile ...)
    2) apply the formula to each country subgroups not to the entire column

    I searched on google for "excel apply formula by group" but I could not find a suitable answer to my problem. Mostly the results show how to use pivot tables or create simple Sum formulas by group. Nothing to show how to use a complex formula able to detect outliers.
    Can anybody please help me with this challenging problem?
    I would really appreciate it.

    World 33

    PS: I would have attached an excel file with the dataset but unfortunately I do not seem to be able to by the system.

  2. #2
    Valued Forum Contributor
    Join Date
    03-16-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    371

    Re: Challenging issue: Detect Outliers in a Column with multiple groups of data

    Relatively simple statistical analysis, and you can use array formulas to use functions by groups. (when applying the formula, instead of just pressing ENTER, you press CTRL + SHIFT + ENTER, confirmed by the automatic addition of {curly braces} around your formula)

    Using a simple "outside 1.5 * IQR" as your outlier criteron, see attached sheet. (tip: you have to go to Go Advanced > (Scroll down) > Manage Attachments to attach a file. Unfortunately the attachments button in the message window doesn't work.)
    Attached Files Attached Files
    Last edited by Stormin'; 01-12-2018 at 06:50 AM.
    Design everything to be as simple as possible, but no simpler.

  3. #3
    Registered User
    Join Date
    10-02-2005
    Posts
    8

    Re: Challenging issue: Detect Outliers in a Column with multiple groups of data

    Hi Stormin!

    I am so thankful for the big help you gave me. That is exactly what I have been trying to do for a long time.

    Thank you very very much.

    World 33

  4. #4
    Valued Forum Contributor
    Join Date
    03-16-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    371

    Re: Challenging issue: Detect Outliers in a Column with multiple groups of data

    Happy to help
    If that takes care of your original question, please select Thread Tools from the menu link above to mark this thread as SOLVED.
    To say thanks to the user(s) who contributed towards the solution, you can use the "Add Reputation" button on their helpful post(s).
    Thanks!

  5. #5
    Registered User
    Join Date
    06-01-2022
    Location
    Jakarta
    MS-Off Ver
    365
    Posts
    1

    Re: Challenging issue: Detect Outliers in a Column with multiple groups of data

    Hi Stormin

    Big thanks for your help

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Stacked Column Chart with multiple data groups
    By dspblues in forum Excel Charting & Pivots
    Replies: 13
    Last Post: 04-21-2015, 04:35 PM
  2. Formatting multiple sets of Data within Excel (Very Challenging )
    By Taj48 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-10-2015, 08:21 AM
  3. [SOLVED] Using VBA to detect separate groups of rows.
    By nt85 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-05-2014, 11:00 AM
  4. Challenging issue - references
    By farishattar in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 07-24-2014, 02:09 PM
  5. A Challenging VBA - Copy data from multiple workbooks into one workbook
    By speed88bump in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-26-2013, 12:29 PM
  6. Macro to Segregate the data based on Multiple Criteria's in Multiple Columns -Challenging!
    By Novice_To_Excel in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-08-2012, 07:31 AM
  7. Replies: 3
    Last Post: 03-07-2012, 08:52 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1