brandonrandall
New Member
- Joined
- Apr 20, 2015
- Messages
- 2
I am trying to write a function with some built in logic to process a list of data. The list of data will be sorted from smallest to largest by FREQ/Vis column. This is done with a macro. What I think I need is an "If" function with an offset embedded into it. Below is the list of data that I am trying to write a logical function for. I have color coded the number to help clarity. The black numbers at the beginning and the end will simply = the Freq/Vis and the Deg's F (corrected K factor) in the second set of columns for FREQ/Vis and DEG F. The red and green numbers represent a matched pair in which we need to take the average value between the two. The black numbers in the middle of the data do not have a match pair and for that reason we throw them out. The two columns on the far right is the calculated data (done by hand). These are the values I want to be able to get with a logical function. When it finds a matched pair, I want it to offset one row down and then rerun the logical expression.
Vis FREQ/VIS DEG F FREQ/VIS DEG F
<TBODY>
</TBODY>
Here's the my attempt at the logical statement, but it is completely wrong.
=IF(A57=A56,D56,IF(A56<>A57,AVERAGE((D56:D57)),IF(A56<>A57,OFFSET(A56,2,0,0))))
Any help would be greatly appreciated! Thanks
Vis FREQ/VIS DEG F FREQ/VIS DEG F
<TBODY> </TBODY><COLGROUP><COL></COLGROUP> | 53.01 | 2765.18 | 53.01 | 2765.18 | ||||
1.74 | 58.59 | 2766.95 | 58.59 | 2766.95 | ||||
1.74 | 81.91 | 2763.14 | 81.91 | 2763.14 | ||||
0.91 | 99.23 | 2753.40 | 102.18 | 2755.31 | ||||
1.74 | 105.13 | 2757.22 | ||||||
0.91 | 109.74 | 2751.01 | 116.11 | 2751.91 | ||||
1.74 | 122.48 | 2752.87 | ||||||
1.74 | 139.82 | 2749.25 | 146.97 | 2746.69 | ||||
0.91 | 154.11 | 2744.13 | ||||||
1.74 | 157.18 | 2747.44 | ||||||
1.74 | 174.54 | 2745.30 | ||||||
1.74 | 192.23 | 2744.45 | ||||||
0.91 | 197.92 | 2742.14 | 203.66 | 2742.42 | ||||
1.74 | 209.40 | 2742.69 | ||||||
1.74 | 226.73 | 2741.20 | 229.02 | 2740.06 | ||||
0.91 | 231.31 | 2738.91 | ||||||
1.74 | 244.60 | 2740.95 | ||||||
1.74 | 261.88 | 2740.83 | 263.21 | 2739.57 | ||||
0.91 | 264.54 | 2738.32 | ||||||
0.91 | 297.78 | 2737.98 | 305.87 | 2738.50 | ||||
1.74 | 313.96 | 2739.01 | ||||||
0.91 | 331.28 | 2737.95 | ||||||
0.91 | 365.56 | 2738.12 | 365.90 | 2737.97 | ||||
1.74 | 366.24 | 2737.83 | ||||||
0.91 | 400.15 | 2738.03 | 409.42 | 2738.10 | ||||
1.74 | 418.68 | 2738.17 | ||||||
0.91 | 433.67 | 2738.27 | ||||||
0.91 | 468.36 | 2738.34 | 469.82 | 2738.32 | ||||
1.74 | 471.27 | 2738.31 | ||||||
0.91 | 501.26 | 2738.71 | 512.52 | 2738.37 | ||||
1.74 | 523.77 | 2738.04 | ||||||
1.74 | 578.66 | 2737.04 | 590.55 | 2737.72 | ||||
0.91 | 602.43 | 2738.40 | ||||||
1.74 | 633.54 | 2737.67 | ||||||
1.74 | 686.27 | 2737.18 | 695.04 | 2737.50 | ||||
0.91 | 703.82 | 2737.81 | ||||||
1.74 | 710.74 | 2737.62 | 758.18 | 2737.79 | ||||
0.91 | 805.62 | 2737.97 | ||||||
0.91 | 906.96 | 2737.16 | 906.96 | 2737.16 | ||||
0.91 | 1008.92 | 2736.99 | 1008.92 | 2736.99 | ||||
0.91 | 1068.82 | 2737.67 | 1068.82 | 2737.67 | ||||
0.91 | 1223.47 | 2737.89 | 1223.47 | 2737.89 | ||||
0.91 | 1328.82 | 2737.35 | 1328.82 | 2737.35 | ||||
0.91 | 1380.71 | 2738.07 | 1380.71 | 2738.07 |
<TBODY>
</TBODY>
Here's the my attempt at the logical statement, but it is completely wrong.
=IF(A57=A56,D56,IF(A56<>A57,AVERAGE((D56:D57)),IF(A56<>A57,OFFSET(A56,2,0,0))))
Any help would be greatly appreciated! Thanks