garyrobbins
asked on
How do I calculate average Cycletime?
Hello experts:
See attached sample file. I used a good formula to calculate average cycletime based on one condition. The results suggest that the furmula I'm using for overall cycletime is inaccurate.
Help?
Gary
Cycletime-Avg-per-Zone-EE-2.xls
See attached sample file. I used a good formula to calculate average cycletime based on one condition. The results suggest that the furmula I'm using for overall cycletime is inaccurate.
Help?
Gary
Cycletime-Avg-per-Zone-EE-2.xls
Disregard my last comment. :P
I will look more later, unless somebody else posts before I do, which is fine.
I will look more later, unless somebody else posts before I do, which is fine.
I think there's something funky with the original averaging ...
Here I used AVERAGEIF and AVERAGE instead, and it seems reasonable to me.
Cycletime-Avg-per-Zone-EE-2-1-.xls
Here I used AVERAGEIF and AVERAGE instead, and it seems reasonable to me.
Cycletime-Avg-per-Zone-EE-2-1-.xls
ASKER
Hi, BusyMama.
This looks like the right answer. I like AVERAGEIF (never used it before). What is the prefix (_xlfn.)??
Gary
This looks like the right answer. I like AVERAGEIF (never used it before). What is the prefix (_xlfn.)??
Gary
ASKER
Experts:
This AVERAGEIF doesn't work in Excel 2003. Creates #NAME? errors.
What would?
Gary
This AVERAGEIF doesn't work in Excel 2003. Creates #NAME? errors.
What would?
Gary
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
AVERAGEIF debuted in Excel 2007. You need an array formula for earlier versions of Excel. Assuming this goes into B10045:
{=AVERAGE(IF($A$3:$A$10037 =A10045,$B $3:$B$1003 7,""))}
To enter an array formula, do not enter the curly braces, and hit Ctrl+Shift+Enter instead of Enter to finish it off. Excel will then display those braces to indicate that it's an array formula.
Copy that down through B10051.
For "All", you can simply use:
=AVERAGE(B3:B10037)
{=AVERAGE(IF($A$3:$A$10037
To enter an array formula, do not enter the curly braces, and hit Ctrl+Shift+Enter instead of Enter to finish it off. Excel will then display those braces to indicate that it's an array formula.
Copy that down through B10051.
For "All", you can simply use:
=AVERAGE(B3:B10037)
ASKER
Barry - thank you again for the "ordinary" formula and your thorough explanation, as usual. I'm sorry my first example was missleading.
I'm always learning new formulas. I like how EE saves previous questions for reference.
Thank you to others who attempted a solution.
Have a great weekend, wherever you are.
Gary
I'm always learning new formulas. I like how EE saves previous questions for reference.
Thank you to others who attempted a solution.
Have a great weekend, wherever you are.
Gary
You are adding up the cycle time for each zone in the formulas below (with the SUMIF).