Hello everyone... I've tried looking everywhere for a post that solves a similar problem without any luck, hopefully you will be able to help me out with this one
I have a spreadsheet with monthly data in each column (24 months) showing equipment performance for a series of 100+ equipment (one in each row). In a 25th and 26th column I have the equipment utilization and availability... it looks like this:
A B C D ... W X Y Z AA
Equip jan feb mar ..... oct nov dec util avail
1 50 60 50 .... 70 30 80 63 YES
2 60 50 40 .... 30 80 63 58 YES
3 50 40 30 .... /0 /0 /0 47 NO
. . . . . . . . .
. . . . . . . . .
. . . . . . . . .
n 30 40 35 .... 50 37 49 43 YES
To obtain the population's average I simply average the whole dataset (columns "B" to "Y" ), but I what I want is to exclude the entire row of data if the util for a particular equipment goes below a predefined threshold or if the equipment is not available
For example, exclude from the population equipment 3 because availability is "NO" and exclude equipment "n" because utilization is below 45
I need to calculate the population average and the population's Std. Dev. using these two conditions.
In the end, I need to identify all equipment which performance is below one std. dev. of the population's average.
Any ideas?
I have a spreadsheet with monthly data in each column (24 months) showing equipment performance for a series of 100+ equipment (one in each row). In a 25th and 26th column I have the equipment utilization and availability... it looks like this:
A B C D ... W X Y Z AA
Equip jan feb mar ..... oct nov dec util avail
1 50 60 50 .... 70 30 80 63 YES
2 60 50 40 .... 30 80 63 58 YES
3 50 40 30 .... /0 /0 /0 47 NO
. . . . . . . . .
. . . . . . . . .
. . . . . . . . .
n 30 40 35 .... 50 37 49 43 YES
To obtain the population's average I simply average the whole dataset (columns "B" to "Y" ), but I what I want is to exclude the entire row of data if the util for a particular equipment goes below a predefined threshold or if the equipment is not available
For example, exclude from the population equipment 3 because availability is "NO" and exclude equipment "n" because utilization is below 45
I need to calculate the population average and the population's Std. Dev. using these two conditions.
In the end, I need to identify all equipment which performance is below one std. dev. of the population's average.
Any ideas?
