A
Andy Morton
Hi Everybody,
I have recently launched a new enterprise and I want to analyse the data on
my year end results. I'm stuck with two Pivot table problem. AFAIK.
Here is the situation.
I have raised a large flock of magpies and I have trained them to fly around
and scrounge around in people's homes while they are watching TV with the
windows wide open during the warm summer evenings.
Actually these magpies work in families (it's a bird thing) and they pool
their loot as a family when they return to my office at night.
This works quite well but I am trying to optimise performance per raid and I
need to do some stats to see which of my magpie families are performing the
best.
Stated simply, here is the data:
-------------------------
Magpie Raids Jewels
Families
-------------------------
MF 1 Raid A 1
MF 2 Raid A 2
MF 3 Raid A 3
MF 1 Raid B 4
MF 2 Raid B 5
MF 3 Raid B 6
MF 1 Raid C 7
MF 2 Raid C 8
MF 3 Raid C 9
-------------------------
And here is my table:
---------------------------------------
Jewels Raids
---------------------------------------
Magpie Raid Raid Raid Total
Families A B C
---------------------------------------
MF 1 1 4 7 12
MF 2 2 5 8 15
MF 3 3 6 9 18
---------------------------------------
Total 6 15 24 45
---------------------------------------
The main thing I need to see in the table is which families of magpies are
performing better vis-à-vis the others over a number of raids. In fact, my
idea is to identify those that are underperforming and glibly sell them on
to an unwitting neighbour.
I must bear in mind that all the families do not participate in all raids.
This may give rise to ambiguous data as a family which did not participate
in a particular raid because it was a legitimate rest day would score zero
as would those who were supposed to be working normally but came home with
nothing because they spent the evening perched on a window ledge watching
Prison Break.
So my first problem is that I don't know how to handle that ambiguous zero
in a Pivot Table.
I think I need to work out the average family performance per raid and then
compare each family to the average. Then I want to see which ones are
consistently or repeatedly above or below average. That's my second problem.
But it's slightly more complicated than that. Some families are larger than
others. So it's normal that they should bring back more jewels than smaller
families. So if I need to divide the number of jewels by the family size.
The family size for each family can vary from raid to raid due to various
factors: strikes, illnesses, hatching, matching and dispatching.
So here is the data with the family size and the Loot ratio,
----------------------------------------------
Magpie Family Raid Jewels Loot
Families size ratio
----------------------------------------------
MF 1 2 Raid A 1 50.00%
MF 2 4 Raid A 2 50.00%
MF 3 7 Raid A 3 42.86%
MF 1 2 Raid B 4 200.00%
MF 2 4 Raid B 5 125.00%
MF 3 8 Raid B 6 75.00%
MF 1 2 Raid C 7 350.00%
MF 2 4 Raid C 8 200.00%
MF 3 6 Raid C 9 150.00%
----------------------------------------------
which can look like this when in a Pivot Table.
Loot ratio Raid
----------------------------------------------
Magpie Raid Raid Raid Total
Families A B C
----------------------------------------------
MF 1 50.00% 200.00% 350.00% 200.00%
MF 2 50.00% 125.00% 200.00% 125.00%
MF 3 42.86% 75.00% 150.00% 89.29%
----------------------------------------------
Total 47.62% 133.33% 233.33% 138.10%
----------------------------------------------
As were looking at performance as a ratio rather than the volume of jewels,
I think we can get rid of the number of jewels and concentrate of the ratio:
How do I get from here (i.e. measuring family performance per raid) to
measuring a family's performance compared to the average performance of all
the falilies over a number of raids?
Please note that there are two specific problems (at least!) that require
attention.
Any help appreciated.
TIA
Andy
I have recently launched a new enterprise and I want to analyse the data on
my year end results. I'm stuck with two Pivot table problem. AFAIK.
Here is the situation.
I have raised a large flock of magpies and I have trained them to fly around
and scrounge around in people's homes while they are watching TV with the
windows wide open during the warm summer evenings.
Actually these magpies work in families (it's a bird thing) and they pool
their loot as a family when they return to my office at night.
This works quite well but I am trying to optimise performance per raid and I
need to do some stats to see which of my magpie families are performing the
best.
Stated simply, here is the data:
-------------------------
Magpie Raids Jewels
Families
-------------------------
MF 1 Raid A 1
MF 2 Raid A 2
MF 3 Raid A 3
MF 1 Raid B 4
MF 2 Raid B 5
MF 3 Raid B 6
MF 1 Raid C 7
MF 2 Raid C 8
MF 3 Raid C 9
-------------------------
And here is my table:
---------------------------------------
Jewels Raids
---------------------------------------
Magpie Raid Raid Raid Total
Families A B C
---------------------------------------
MF 1 1 4 7 12
MF 2 2 5 8 15
MF 3 3 6 9 18
---------------------------------------
Total 6 15 24 45
---------------------------------------
The main thing I need to see in the table is which families of magpies are
performing better vis-à-vis the others over a number of raids. In fact, my
idea is to identify those that are underperforming and glibly sell them on
to an unwitting neighbour.
I must bear in mind that all the families do not participate in all raids.
This may give rise to ambiguous data as a family which did not participate
in a particular raid because it was a legitimate rest day would score zero
as would those who were supposed to be working normally but came home with
nothing because they spent the evening perched on a window ledge watching
Prison Break.
So my first problem is that I don't know how to handle that ambiguous zero
in a Pivot Table.
I think I need to work out the average family performance per raid and then
compare each family to the average. Then I want to see which ones are
consistently or repeatedly above or below average. That's my second problem.
But it's slightly more complicated than that. Some families are larger than
others. So it's normal that they should bring back more jewels than smaller
families. So if I need to divide the number of jewels by the family size.
The family size for each family can vary from raid to raid due to various
factors: strikes, illnesses, hatching, matching and dispatching.
So here is the data with the family size and the Loot ratio,
----------------------------------------------
Magpie Family Raid Jewels Loot
Families size ratio
----------------------------------------------
MF 1 2 Raid A 1 50.00%
MF 2 4 Raid A 2 50.00%
MF 3 7 Raid A 3 42.86%
MF 1 2 Raid B 4 200.00%
MF 2 4 Raid B 5 125.00%
MF 3 8 Raid B 6 75.00%
MF 1 2 Raid C 7 350.00%
MF 2 4 Raid C 8 200.00%
MF 3 6 Raid C 9 150.00%
----------------------------------------------
which can look like this when in a Pivot Table.
Loot ratio Raid
----------------------------------------------
Magpie Raid Raid Raid Total
Families A B C
----------------------------------------------
MF 1 50.00% 200.00% 350.00% 200.00%
MF 2 50.00% 125.00% 200.00% 125.00%
MF 3 42.86% 75.00% 150.00% 89.29%
----------------------------------------------
Total 47.62% 133.33% 233.33% 138.10%
----------------------------------------------
As were looking at performance as a ratio rather than the volume of jewels,
I think we can get rid of the number of jewels and concentrate of the ratio:
How do I get from here (i.e. measuring family performance per raid) to
measuring a family's performance compared to the average performance of all
the falilies over a number of raids?
Please note that there are two specific problems (at least!) that require
attention.
Any help appreciated.
TIA
Andy