Calculating the moving error/dfference.

[birkenhair]

I have an issue in calculating the moving error/difference between two values. My data is generated from geometric deviations from nominal of a feature on a manufactured component through two processes, so both values could be either positive or negative around zero and either greater or smaller than the other value. I need to calculate the difference between these values relative to the first. My problem comes from the possible combinations of data and how to apply the correct formula for each. I have worked through my sample data using IF statements to deliver the correct answer but will end up with 16 columns of data to get to the answer I'm sure there must be an easier way.

Any help on this issue is greatly appreciated in advance.

 

[Jerry Sullivan]

Hello and welcome,

Could you post a few examples showing your 3 input values (Nominal, 1st Measure, 2nd Measure) and the resulting correct 2 output values?

I would have guessed that the calculation of each of the two output values would be straight-forward formula, but if that were the case you probably wouldn't be trying to model this using 16 columns.

You don't need to list all premutations, but if you post 3 or 4 examples of positive and negative deviations it will help clarify your question.

 

[birkenhair]

Hi and thanks for responding, the actual data I'm trying to calculate is in microns so I have substituted the values with round numbers for ease.

Please remember that I'm trying to calculate the moving difference of the second value to the first so here are the possible combinations (and formula to calculate for reference) of the values 13 & 18.......
13 to 18 = 5 =ABS(13-18)
18 to 13 = -5 =13-18
-13 to 18 = 31 =ABS((-13)-18)
18 to -13 = -31 =(-13)-18
-13 to -18 = -5 =(-18)-(-13)

To best explain the requirement of this calculation.....
After manufacturing a component, digital measurements are taken using a touch probe, these measurements relate to the axis system of the machine taking the measurements. Upon converting the measurements so they relate to the axis system of the component (to qualify the component as geometrically correct) a deviation from zero (zero being the as designed nominal) is shown for each measure position. If a measured feature on the component was exactly positioned to nominal the value would be zero, if it was out of position it would show either a positive or negative value of the deviation from zero. If after taking the first set of measurements I then subject the component to another manufacturing process (say drill some holes in it) potentially the component features may have moved. If I was to re-measure the component features, I could compare the 2 sets of measurements to each other to analyse how much each component feature had positionally changed from it's original measurements, obviously component features could improve or worsen in relation to zero (nominal) but I want to calculate the difference from its original measurement (could be a positive or negative value).

The method I have used to produce the correct answer is as follows:

1. Test the first value - is it less than zero
2. Test the second value - is it less than zero
3. Test the first value - is it greater than the second
4. Then I use a series of 5 IF AND statements to identify the resultant combination of tests 1 to 3. The possible combinations for the 5 examples above are....
TRUE TRUE FALSE
TRUE TRUE TRUE
FALSE TRUE FALSE
TRUE FALSE TRUE
FALSE FALSE TRUE
5. I then apply 5 IF statements to ensure the right formula is used based on the combination test results.
6. The final step is to test the results of the 5 IF statements for a value.

So my spreadsheet formula looks like this assuming that B2 (13) and C2 (18) are the two values being tested
13
18
=IF(B2>0,TRUE)
=IF(C2>0,TRUE)
=IF(B2>C2,TRUE)
=IF(AND(D2=TRUE,E2=TRUE,F2=FALSE),TRUE) =IF(AND(D2=TRUE,E2=TRUE,F2=TRUE),TRUE) =IF(AND(D2=FALSE,E2=TRUE,F2=FALSE),TRUE) =IF(AND(D2=TRUE,E2=FALSE,F2=TRUE),TRUE) =IF(AND(D2=FALSE,E2=FALSE,F2=TRUE),TRUE)
=IF(G$2=TRUE,ABS(B$2-C$2),M$2)
=IF(H$2=TRUE,C$2-B$2,N$2)
=IF(I$2=TRUE,ABS(B$2-C$2),O$2)
=IF(J2=TRUE,C2-B2,P2)
=IF(K2=TRUE,C2-B2)

I think I need to take a deep breath after that, please have a read and see if you can understand my thought process.
Thanks again for taking the time to reply and please fire more questions back if I havn't explained well enough.

 

[birkenhair]

Just noticed that I have missed off one of the combinations

1. Test the first value - is it less than zero
2. Test the second value - is it less than zero
3. Test the first value - is it greater than the second

FALSE FALSE FALSE

To calculate the values below I need to add more formula

<table width="136" border="0" cellpadding="0" cellspacing="0"><col width="64"><col width="72"><tr height="17"> <td class="xl27" style="height: 12.75pt; width: 48pt;" width="64" height="17">-0.0034</td> <td class="xl28" style="border-left: medium none; width: 54pt;" width="72" align="right">-0.0007</td> </tr></table>

 

[birkenhair]

I now have to rely on 16 cells of formula to return the difference between 2 values

13
18
=IF(B2>0,TRUE)
=IF(C2>0,TRUE)
=IF(B2>C2,TRUE)
=IF(AND(D2=TRUE,E2=TRUE,F2=FALSE),TRUE)
=IF(AND(D2=TRUE,E2=TRUE,F2=TRUE),TRUE)
=IF(AND(D2=FALSE,E2=TRUE,F2=FALSE),TRUE)
=IF(AND(D2=TRUE,E2=FALSE,F2=TRUE),TRUE)
=IF(AND(D2=FALSE,E2=FALSE,F2=TRUE),TRUE)
=IF(AND(D2=FALSE,E2=FALSE,F2=FALSE),TRUE)
=IF(G$2=TRUE,ABS(B$2-C$2),N$2)
=IF(H$2=TRUE,C$2-B$2,O$2)
=IF(I$2=TRUE,ABS(B$2-C$2),P$2)
=IF(J2=TRUE,C2-B2,Q2)
=IF(K2=TRUE,C2-B2)
=IF(L2=TRUE,ABS(B2-C2))
=MAX(M2:Q2)

Maybe it is what it is and what I should really be concentrating on is nesting my formula, either way there is definetly good reason for a new engineering function.

 

[Jerry Sullivan]

I haven't studied your 16 cells of formula yet, but just looking at your examples...

Please remember that I'm trying to calculate the moving difference of the second value to the first so here are the possible combinations (and formula to calculate for reference) of the values 13 & 18.......
13 to 18 = 5 =ABS(13-18)
18 to 13 = -5 =13-18
-13 to 18 = 31 =ABS((-13)-18)
18 to -13 = -31 =(-13)-18
-13 to -18 = -5 =(-18)-(-13)

...it looks like the desired result can be calculated as the difference of second value minus the first.

Excel Workbook
	B	C	D
1	Value1	Value2	Difference
2	13	18	5
3	18	13	-5
4	-13	18	31
5	18	-13	-31
6	-13	-18	-5
Sheet6

Excel 2007

Cell Formulas
Range	Formula
D2	=C2-B2
D3	=C3-B3
D4	=C4-B4
D5	=C5-B5
D6	=C6-B6

Am I misunderstanding something?

 

[birkenhair]

OMG! I must need to take a break or something, I have just populated the most cells ever to reverse engineer a simple sum! Quite proud of myself having taken the crown for dumbest post in the world. Think I'll stick to VBA in future sums just don't seem to be my forte.

Do me a favour and dont tell anyone..... lets just keep it between you, me and the hundreds of people who read this forum!

Thanks so much for your help and maybe next week you can help me add 2 + 2.

 

[Jerry Sullivan]

LOL I completely understand. Happens to me all the time.
It will be our secret!