[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Organization
[/TD]
[TD]month
[/TD]
[TD]KPI1
[/TD]
[TD]KPI2
[/TD]
[TD]KPI3
[/TD]
[TD]KPI4
[/TD]
[/TR]
[TR]
[TD]Org1
[/TD]
[TD]Jan-18
[/TD]
[TD]100%
[/TD]
[TD]0
[/TD]
[TD]3
[/TD]
[TD]4%
[/TD]
[/TR]
[TR]
[TD]Org1
[/TD]
[TD]Feb-18
[/TD]
[TD]99%
[/TD]
[TD]0
[/TD]
[TD]2
[/TD]
[TD]2%
[/TD]
[/TR]
[TR]
[TD]Org1
[/TD]
[TD]Mar-18
[/TD]
[TD]100%
[/TD]
[TD]0
[/TD]
[TD]3
[/TD]
[TD]1%
[/TD]
[/TR]
[TR]
[TD]Org2
[/TD]
[TD]Jan-18
[/TD]
[TD]86%
[/TD]
[TD]3
[/TD]
[TD]1
[/TD]
[TD]1%
[/TD]
[/TR]
[TR]
[TD]Org2
[/TD]
[TD]Feb-18
[/TD]
[TD]82%
[/TD]
[TD]0
[/TD]
[TD]1
[/TD]
[TD]6%
[/TD]
[/TR]
[TR]
[TD]Org2
[/TD]
[TD]Mar-18
[/TD]
[TD]90%
[/TD]
[TD]1
[/TD]
[TD]3
[/TD]
[TD]1%
[/TD]
[/TR]
[TR]
[TD]Org3
[/TD]
[TD]Jan-18
[/TD]
[TD]97%
[/TD]
[TD]9
[/TD]
[TD]4
[/TD]
[TD]2%
[/TD]
[/TR]
[TR]
[TD]Org3
[/TD]
[TD]Feb-18
[/TD]
[TD]99%
[/TD]
[TD]0
[/TD]
[TD]2
[/TD]
[TD]5%
[/TD]
[/TR]
[TR]
[TD]Org3
[/TD]
[TD]Mar-18
[/TD]
[TD]98%
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]3%
[/TD]
[/TR]
</tbody>[/TABLE]
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!
<tbody>[TR]
[TD]Organization
[/TD]
[TD]month
[/TD]
[TD]KPI1
[/TD]
[TD]KPI2
[/TD]
[TD]KPI3
[/TD]
[TD]KPI4
[/TD]
[/TR]
[TR]
[TD]Org1
[/TD]
[TD]Jan-18
[/TD]
[TD]100%
[/TD]
[TD]0
[/TD]
[TD]3
[/TD]
[TD]4%
[/TD]
[/TR]
[TR]
[TD]Org1
[/TD]
[TD]Feb-18
[/TD]
[TD]99%
[/TD]
[TD]0
[/TD]
[TD]2
[/TD]
[TD]2%
[/TD]
[/TR]
[TR]
[TD]Org1
[/TD]
[TD]Mar-18
[/TD]
[TD]100%
[/TD]
[TD]0
[/TD]
[TD]3
[/TD]
[TD]1%
[/TD]
[/TR]
[TR]
[TD]Org2
[/TD]
[TD]Jan-18
[/TD]
[TD]86%
[/TD]
[TD]3
[/TD]
[TD]1
[/TD]
[TD]1%
[/TD]
[/TR]
[TR]
[TD]Org2
[/TD]
[TD]Feb-18
[/TD]
[TD]82%
[/TD]
[TD]0
[/TD]
[TD]1
[/TD]
[TD]6%
[/TD]
[/TR]
[TR]
[TD]Org2
[/TD]
[TD]Mar-18
[/TD]
[TD]90%
[/TD]
[TD]1
[/TD]
[TD]3
[/TD]
[TD]1%
[/TD]
[/TR]
[TR]
[TD]Org3
[/TD]
[TD]Jan-18
[/TD]
[TD]97%
[/TD]
[TD]9
[/TD]
[TD]4
[/TD]
[TD]2%
[/TD]
[/TR]
[TR]
[TD]Org3
[/TD]
[TD]Feb-18
[/TD]
[TD]99%
[/TD]
[TD]0
[/TD]
[TD]2
[/TD]
[TD]5%
[/TD]
[/TR]
[TR]
[TD]Org3
[/TD]
[TD]Mar-18
[/TD]
[TD]98%
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]3%
[/TD]
[/TR]
</tbody>[/TABLE]
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!