HI Hayley,
You wrote:
"What I am trying to achieve is to 'flag' the cell under DIFF. if it is either plus or minus more than the TOL. For example, looking at row 3, if the difference was either more or less than 1.25 it could be highlighted in some way, not necessarily a colour, but some other clever way to say this measurement is not within tolerance and is not acceptable. This would be my ultimate solution."
Isn't that precisely what is done in this example, copied from my third post above?
The red fill highlight in cells E4 and E5 says "this measurement is not within tolerance and is not acceptable."
"If … you need two columns, one with negative tolerance, and one with positive…
The "diff" column (labeled +/- in this example) includes minus signs where the spec value is less than the measured value and shows an unsigned (positive) value where the spec value is greater than the measured value. Is that distinction necessary, or would is it sufficient to know that the measurements for points B and C are both off-spec by 2 units?
If you do not need to distinguish between 'too long' and 'too short' in the diff column, then reporting the negative values as absolute (non-signed) values would remove the need for a second column containing the -TOL values.
If you do need the minus signs in "diff", then the -TOL column is needed (but does not need to be visible to the user).
, how do you create a rule to put a negative sign in-front of the tolerance? The form that pull the tolerance from only has the number with the title -/+. It's not viable to manually add a negative sign in front of the negative tolerance column (F3)."
(Note: In my example above, column F contains the (positive and unsigned) TOL value retrieved from the 'spec data entry' table. Multiplying this value by -1 converts it to the negative equivalent.
In the example posted here, the formula in G3 (and filled down) would be F3*-1
As you enter the formula, Numbers will automatically convert the apostrophe (*) to a multiplication sign ( × ).
As the results are negative, Numbers will automatically place the - sign.
As noted above, the -TOL column is needed by the CH rule, but does not need to be seen by the user in the usual course of business. Hide the column, or move it to the far right side of the table (and hide it there) to keep it away from the active part of the table, where it might be damaged.
Regards,
Barry
