Subtracting two values in degrees, based on a tolerace

Ruddles

Well-known Member

Joined

Aug 24, 2010

Messages

5,845

Office Version

1. 365

Platform

1. Windows

• Feb 7, 2011

• #2

Isn't it just:-

Code:

=IF(ABS(AE51-S51)>10,"OutwithTolerance","")

or maybe:-

Code:

=IF(OR(AE51-S51)<-10,AE51-S51>10),"OutwithTolerance","")

?

 

Upvote 0

M

macdca

Board Regular

Joined

Sep 28, 2010

Messages

170

• Feb 7, 2011

• #3

The first one seems to work, does that take into account the fact that the cells are degrees??

 

Upvote 0

Ruddles

Well-known Member

Joined

Aug 24, 2010

Messages

5,845

Office Version

1. 365

Platform

1. Windows

• Feb 7, 2011

• #4

macdca said:

The first one seems to work, does that take into account the fact that the cells are degrees??

Click to expand...

I've just realised you mean degrees of arc rather than degrees of temperature!

Will the numbers always be around the zero mark? Could they go anywhere near 180?

 

Last edited: Feb 7, 2011

Upvote 0

M

macdca

Board Regular

Joined

Sep 28, 2010

Messages

170

• Feb 7, 2011

• #5

Yes degrees or arc, srry i should have said.

 

Upvote 0

Ruddles

Well-known Member

Joined

Aug 24, 2010

Messages

5,845

Office Version

1. 365

Platform

1. Windows

• Feb 7, 2011

• #6

And will the numbers always be around the zero mark? Could they go anywhere near 180?

 

Upvote 0

M

macdca

Board Regular

Joined

Sep 28, 2010

Messages

170

• Feb 7, 2011

• #7

They are any value, a lot are around the 360 mark (358 and 359.9 etc)

 

Upvote 0

Ruddles

Well-known Member

Joined

Aug 24, 2010

Messages

5,845

Office Version

1. 365

Platform

1. Windows

• Feb 7, 2011

• #8

As you say, the main problem is that the numbers can lie either side of 360 degrees, so just subtracting them doesn't work. The second problem is that AE51 could be greater than S51 or it could be the other way around.

So what my formula does is find the ABSolute difference between the two values and then in case it's close to 360 it rotates the calculation by 180 degrees and does the calculation at that point of the circle as well. If the calculations fall outside the tolerance at both positions, then "Outwith" is returned. Everything's done MODulo 360 to cope with values which cross the 360-0 boundary.

Try this:-

Code:

=IF(AND([COLOR=red][B]ABS[/B][/COLOR]([COLOR=magenta][B]MOD[/B][/COLOR](AE51,[COLOR=magenta][B]360[/B][/COLOR])-[COLOR=magenta][B]MOD[/B][/COLOR](S51,[COLOR=magenta][B]360[/B][/COLOR]))>10,[COLOR=red][B]ABS[/B][/COLOR]([COLOR=magenta][B]MOD[/B][/COLOR](AE51+[COLOR=blue][B]180[/B][/COLOR],[COLOR=magenta][B]360[/B][/COLOR])-[COLOR=magenta][B]MOD[/B][/COLOR](S51+[COLOR=blue][B]180[/B][/COLOR],[COLOR=magenta][B]360[/B][/COLOR]))>10),"OutwithTolerance",[B]"OK"[/B])

You can change "OK" to "" when you've tested it and are happy it works okay.

 

Upvote 0

M

macdca

Board Regular

Joined

Sep 28, 2010

Messages

170

• Feb 7, 2011

• #9

Wow that seems to work, thank you so much!

 

Upvote 0

M

macdca

Board Regular

Joined

Sep 28, 2010

Messages

170

• Feb 7, 2011

• #10

One last thing. where the tolerance might be 0.2 for example and the planned position was 0 and it ended up being 359.8, I dont want that to flag as outwith as the allowance is 0.2, and its technically inside that, how can I amend the formuls for this??

 

Upvote 0