Determine if heading is within range of +-15 of another heading

**magnus.blomquist** · 06-06-2016, 11:23 AM

Hi,
I'm struggling to make a formula that will perform the following True/False scenarios
assumptions:
Yc = current heading
Yh = historic heading
Ymin = Yc -15 degrees
Ymax = Yc + 15 degrees

If Yh is within the range Ymin to Ymax, then TRUE, else False.

Sounds simple enough, but when the value of Yc is within the range 345 to 15, then I can't get the formula to work.

It has to be as a formula, NOT in VBA as the spreadsheet is updated through PI whenever a value (not included in this example) is changed.

I have attached an example file. The formula is in column 'E'.

Any help would be loooooooovely!!!

**shg** · 06-06-2016, 11:40 AM

In E2 and copy down,

=ABS((0.5 - MOD( ($A$6 - D3)/360 + 0.5, 1)) * 360) < 15

**magnus.blomquist** · 06-06-2016, 11:48 AM

Shg,
Thank you for your reply!! How does it work?

**shg** · 06-06-2016, 11:57 AM

MOD( ($A$6 - D3)/360 + 0.5, 1) calculates a difference between 0 and 1

(0.5 - MOD( ($A$6 - D3)/360 + 0.5, 1)) translates that to a difference between -0.5 and 0.5

(0.5 - MOD( ($A$6 - D3)/360 + 0.5, 1)) * 360 scales that to a difference between -180 and 180

ABS takes the absolute value.

**magnus.blomquist** · 06-06-2016, 12:05 PM

Thanks again!! huge help!

**shg** · 06-06-2016, 12:16 PM

You're welcome.

It could be made a little more intuitive:

=ABS(180 - MOD((180 + $A$6 - D3), 360)) < 15

Determine if heading is within range of +-15 of another heading

LinkBack

Thread Tools

Rate This Thread

Display

Determine if heading is within range of +-15 of another heading

Re: Determine if heading is within range of +-15 of another heading

Re: Determine if heading is within range of +-15 of another heading

Re: Determine if heading is within range of +-15 of another heading

Re: Determine if heading is within range of +-15 of another heading

Re: Determine if heading is within range of +-15 of another heading

Thread Information

Users Browsing this Thread

Similar Threads

Not finding 1st row after heading in autofilter if 1st row is only row after heading

[SOLVED] Look for a certain heading but if not found then move to next heading you are after.

[SOLVED] Sub-heading stuck at same heading number

Return the heading value of MIN Range

Returning Row Heading for Top 'X' values in a range

[SOLVED] In a table produce an value by column heading and row heading

[SOLVED] how to determine column heading from lookup

Bookmarks

Bookmarks

Posting Permissions