Formula to Identify Col Data-Pairs that Differ by 2*pi

[Guest]

Hello;
I would very much appreciate your help.
1) The total angular range is specified in cell F1, and the angular interval
"s" in cell G1
2) The angular ordinates are tabulated in cells B1:B14 at the equal angular
intervals "s", except for the last cell (for mathematical convenience). Will
use degrees here for clarity.
3) Some of the data in col B differ by 2*pi or by multiples of (2*pi)
4) I would like to insert an appropriate formula in column D that:

identifies the pairs (or triplets or quadruplets) of cells in col B with values differ by 2*pi (or by multiples of 2*pi)
compare their corresponding tabulated values in C
returns the numerical value 0.0 if C(2nd) value <= C(1st) value for the pair
returns the numerical value 1.0 if C(2nd) > C(1st)
returns N/A() if there's no value equal to "current B value"+ 2*pi in the remainder of the B data

5) In case of 3 or 4 B values with each consecutive pair differing by 2*pi,
repeat 4. above for the 1st pair, then for the next pair and so on.
6) Example:
total range: cell F1:: 760.0
interval "s": cell G1:: 60.0
max index: cell H1:: 14
index: cells A1:A14
independent variable: cells B1:B14
dependent variable: cells C1:C14
intended formula??: D114
col.....A.......B.........C...........D
..........1......0.0.......0.0........1.0
..........2......60.0.....1.213.....0.0
..........3.....120.0....1.076.....0.0
..........4.....180.0....0.993.....1.0
..........5.....240.0....0.952.....1.0
..........6.....300.0....0.900.....0.0
..........7.....360.0....0.974.....0.0
..........8.....420.0....0.955....#N/A
..........9.....480.0....0.894....#N/A
.........10....540.0....1.082....#N/A
.........11....600.0....1.121....#N/A
.........12....660.0....0.890....#N/A
.........13....720.0....0.933....#N/A
.........14....760.0....0.998....#N/A

What formula in column D would you suggest?? (Excel 2003 SP2, Windows XP)
Thank you kindly.

 

[Bernie Deitrick]

Monir,

Use the MOD function to figure out the angular basis.

HTH,
Bernie
MS Excel MVP

 

[Guest]

Bernie;
Here's one way of doing it. It may not be the most efficient way, but it
works fine.
insert col after B
in C, insert and copy down:
=MOD(B1,360)
in E, insert and copy down:
=VLOOKUP(C1,C2:$D$14,2,FALSE) - D1
in E15, insert:
=IF(COUNTIF(E1:E14,"<=0")=0,1,0)