rjbinney
Active Member
- Joined
- Dec 20, 2010
- Messages
- 279
- Office Version
- 365
- Platform
- Windows
I know I can do this in Pivot Tables. I want to do this with formulas if possible.
I have a list of products (about 30) (named "SKU") and how long it took to run each (named "Actual") (some ran two or three times, some ran up to 60 times). I need some information about how "in control" the process is. I have a table of the standard runtime for each, and inserted a MATCH column ("Should").
I calculated a column for the delta between "Actual" and "Should" for each run (named "Delta").
So to get the average difference between standard and actual, I can build a table:
<tbody>
</tbody>
I would love to look at the volatility - which means I need to be able to compare the Delta to the Mean. Is there a clever formula that will tell Excel to ONLY consider based on, say, SKU criteria.
It's that last bit - basing it on criteria that's important. Because in addition to SKUs, I also have columns (and need to cut data) based on corresponding equipment, region produced, end product, customer, etc. So it's super-easy to build tables to calculate the mean (based on above formula). It's the standard deviation I'm stuck on!
Again, I know I can pull it together with Pivot Tables, but I have my reasons for not using 'em.
Thanks,
Thanks,
I have a list of products (about 30) (named "SKU") and how long it took to run each (named "Actual") (some ran two or three times, some ran up to 60 times). I need some information about how "in control" the process is. I have a table of the standard runtime for each, and inserted a MATCH column ("Should").
I calculated a column for the delta between "Actual" and "Should" for each run (named "Delta").
So to get the average difference between standard and actual, I can build a table:
PRODUCT | MEAN RUN DIFFERENCE |
Product 1 | =SUMIF(Delta, SKU, $A2) / COUNTIF(SKU,$A2) |
Product n |
<tbody>
</tbody>
I would love to look at the volatility - which means I need to be able to compare the Delta to the Mean. Is there a clever formula that will tell Excel to ONLY consider based on, say, SKU criteria.
It's that last bit - basing it on criteria that's important. Because in addition to SKUs, I also have columns (and need to cut data) based on corresponding equipment, region produced, end product, customer, etc. So it's super-easy to build tables to calculate the mean (based on above formula). It's the standard deviation I'm stuck on!
Again, I know I can pull it together with Pivot Tables, but I have my reasons for not using 'em.
Thanks,
Thanks,