Identifying outliers?

datalyn

New Member
Joined
Nov 16, 2018
Messages
1
Organization
month
KPI1
KPI2
KPI3
KPI4
Org1
Jan-18
100%
0
3
4%
Org1
Feb-18
99%
0
2
2%
Org1
Mar-18
100%
0
3
1%
Org2
Jan-18
86%
3
1
1%
Org2
Feb-18
82%
0
1
6%
Org2
Mar-18
90%
1
3
1%
Org3
Jan-18
97%
9
4
2%
Org3
Feb-18
99%
0
2
5%
Org3
Mar-18
98%
1
1
3%

<tbody>
</tbody>
I need to produce a dashboard listing details of outliers in our data, falling 3 standard deviations above or below the mean. I can highlight them in the original data table using conditional formatting, but how can I extract their row and column headers to a different table? e.g. Org2, Feb-18, KPI1, 82%, or even just return the cell address of those cells falling as outliers for their column?

Data is in the format above, albeit a much larger table: more organisations, more months, and many more KPIs.


All suggestions welcome!
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Forum statistics

Threads
1,216,036
Messages
6,128,432
Members
449,452
Latest member
Chris87

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top