"I would like to know how I work out the average percentage out of tolerance, either above or below."
EDIT: After going through the calculations on the first part here, I'm not sure my first section is calculating the values you are looking for. Can you provide a more precise description of what you consider the "average percentage out of tolerance" to mean?
Meantime, a quick look at the formulas in the first part, using values from your table above, may be useful.
B.
Part 1:
This would be the average of the 'distance' from the specified values.
To calculate that average, you'll need to sum the absolute values, then divide be the count of those values
Here's an example:
The first formula, entered in H3 and filled down to the row (28) above which the averages are to be reported. The formula strips the signs from the values in column E and places the signless value in the same row of column H.
Rows 29 and 30 are the bottom two rows of the table, and have been converted to Footer Rows. A table may have up to 5 Footer rows, but they have to be contiguous and the five (or fewer) at the bottom of the table.
Using a footer row allows clicking the column reference tab to choose all of column H without including the Header or Footer rows.
The difference between the two formulas is that AVERAGE will ignore cells containing containing text, but will include cells containing zero, while AVERAGEIF (as written) will include only cells containing a value greater than zero in its Average calculation.
Both formulas were entered in the same rows of column E and column H. The results in column E are the results calculated by each of the formulas; the displays of the same results in column H have been rounded to two places after the decimal for easier reading. Rounding the display does not change the actual value placed in the cell.
Part 2
Following that formula I would like to say "if tolerance outside 10%, the garment is not acceptable."
Not sure what your message is here, or what "outside 10%" refers to.
"Tolerance," to my understanding is a statement of the maximum difference from the specified quantity that is acceptable (ie. 'that will be tolerated').
"10%" is a fraction (n/100) presented as the numerator with the denominator set to 100.
10% is equivalent to 10 hundredths or 1 tenth.
My questions here is 'one tenth of what?'
Does this apply to a single measurement, or to the average of all measurements?
Four of the points on your list show a tolerance of (plus or minus) 1.25 units.
The fifth point shows a tolerance of 0.6 units.
The sixth point shows a tolerance of 0.3 units.
How is the "10%" to be calculated for these three cases?
Regards,
Barry