IF Statement question with offset function. HELP Please! :)

[brandonrandall]

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
[TABLE="width: 500"]
<TBODY>[TR]
[TD][TABLE="width: 80"]
<TBODY>[TR]
[TD][/TD]
[/TR]

 [TR]
[TD]</SPAN>1.74[/TD]
[/TR]
</TBODY><COLGROUP><COL></COLGROUP>[/TABLE]
[/TD]
[TD]53.01[/TD]
[TD]2765.18[/TD]
[TD][/TD]
[TD]53.01[/TD]
[TD]2765.18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1.74[/TD]
[TD]58.59[/TD]
[TD]2766.95[/TD]
[TD][/TD]
[TD]58.59[/TD]
[TD]2766.95[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1.74[/TD]
[TD]81.91[/TD]
[TD]2763.14[/TD]
[TD][/TD]
[TD]81.91[/TD]
[TD]2763.14[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0.91[/TD]
[TD]99.23[/TD]
[TD]2753.40[/TD]
[TD][/TD]
[TD]102.18[/TD]
[TD]2755.31[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1.74[/TD]
[TD]105.13[/TD]
[TD]2757.22[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0.91[/TD]
[TD]109.74[/TD]
[TD]2751.01[/TD]
[TD][/TD]
[TD]116.11[/TD]
[TD]2751.91[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1.74[/TD]
[TD]122.48[/TD]
[TD]2752.87[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1.74[/TD]
[TD]139.82[/TD]
[TD]2749.25[/TD]
[TD][/TD]
[TD]146.97[/TD]
[TD]2746.69[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0.91[/TD]
[TD]154.11[/TD]
[TD]2744.13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1.74[/TD]
[TD]157.18[/TD]
[TD]2747.44[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1.74[/TD]
[TD]174.54[/TD]
[TD]2745.30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1.74[/TD]
[TD]192.23[/TD]
[TD]2744.45[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0.91[/TD]
[TD]197.92[/TD]
[TD]2742.14[/TD]
[TD][/TD]
[TD]203.66[/TD]
[TD]2742.42[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1.74[/TD]
[TD]209.40[/TD]
[TD]2742.69[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1.74[/TD]
[TD]226.73[/TD]
[TD]2741.20[/TD]
[TD][/TD]
[TD]229.02[/TD]
[TD]2740.06[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0.91[/TD]
[TD]231.31[/TD]
[TD]2738.91[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1.74[/TD]
[TD]244.60[/TD]
[TD]2740.95[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1.74[/TD]
[TD]261.88[/TD]
[TD]2740.83[/TD]
[TD][/TD]
[TD]263.21[/TD]
[TD]2739.57[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0.91[/TD]
[TD]264.54[/TD]
[TD]2738.32[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0.91[/TD]
[TD]297.78[/TD]
[TD]2737.98[/TD]
[TD][/TD]
[TD]305.87[/TD]
[TD]2738.50[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1.74[/TD]
[TD]313.96[/TD]
[TD]2739.01[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0.91[/TD]
[TD]331.28[/TD]
[TD]2737.95[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0.91[/TD]
[TD]365.56[/TD]
[TD]2738.12[/TD]
[TD][/TD]
[TD]365.90[/TD]
[TD]2737.97[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1.74[/TD]
[TD]366.24[/TD]
[TD]2737.83[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0.91[/TD]
[TD]400.15[/TD]
[TD]2738.03[/TD]
[TD][/TD]
[TD]409.42[/TD]
[TD]2738.10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1.74[/TD]
[TD]418.68[/TD]
[TD]2738.17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0.91[/TD]
[TD]433.67[/TD]
[TD]2738.27[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0.91[/TD]
[TD]468.36[/TD]
[TD]2738.34[/TD]
[TD][/TD]
[TD]469.82[/TD]
[TD]2738.32[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1.74[/TD]
[TD]471.27[/TD]
[TD]2738.31[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0.91[/TD]
[TD]501.26[/TD]
[TD]2738.71[/TD]
[TD][/TD]
[TD]512.52[/TD]
[TD]2738.37[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1.74[/TD]
[TD]523.77[/TD]
[TD]2738.04[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1.74[/TD]
[TD]578.66[/TD]
[TD]2737.04[/TD]
[TD][/TD]
[TD]590.55[/TD]
[TD]2737.72[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0.91[/TD]
[TD]602.43[/TD]
[TD]2738.40[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1.74[/TD]
[TD]633.54[/TD]
[TD]2737.67[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1.74[/TD]
[TD]686.27[/TD]
[TD]2737.18[/TD]
[TD][/TD]
[TD]695.04[/TD]
[TD]2737.50[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0.91[/TD]
[TD]703.82[/TD]
[TD]2737.81[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1.74[/TD]
[TD]710.74[/TD]
[TD]2737.62[/TD]
[TD][/TD]
[TD]758.18[/TD]
[TD]2737.79[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0.91[/TD]
[TD]805.62[/TD]
[TD]2737.97[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0.91[/TD]
[TD]906.96[/TD]
[TD]2737.16[/TD]
[TD][/TD]
[TD]906.96[/TD]
[TD]2737.16[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0.91[/TD]
[TD]1008.92[/TD]
[TD]2736.99[/TD]
[TD][/TD]
[TD]1008.92[/TD]
[TD]2736.99[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0.91[/TD]
[TD]1068.82[/TD]
[TD]2737.67[/TD]
[TD][/TD]
[TD]1068.82[/TD]
[TD]2737.67[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0.91[/TD]
[TD]1223.47[/TD]
[TD]2737.89[/TD]
[TD][/TD]
[TD]1223.47[/TD]
[TD]2737.89[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0.91[/TD]
[TD]1328.82[/TD]
[TD]2737.35[/TD]
[TD][/TD]
[TD]1328.82[/TD]
[TD]2737.35[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0.91[/TD]
[TD]1380.71[/TD]
[TD]2738.07[/TD]
[TD][/TD]
[TD]1380.71[/TD]
[TD]2738.07[/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]



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

 

[Kamolga]

I am sorry I do not understand the links between the row and the columns and what you want to offset. Where is your formula supposed to go. Maybe you could click show formula on your sheet, take a screen shot and post it?

Anyway, you have twice the same arguments IF(A56<>A57, which makes no sense to me

We could use calculation columns as steps, so 1could be

=if(A57=A56,D56,average(D56:D57)

I want it to offset one row down and then rerun the logical expression.

Do you mean its value?

 

[brandonrandall]

I'm not sure how to put in a screen shot, but the two columns on the far right is where the formula would go (the numbers in blue below). As far as offsetting, maybe this is not the proper way to do it or maybe there is a better way to do it. What I want it to do is after it averages a matched pair, in the case shown below (0.91 and 1.74), I want the formula to skip the 1.74 line because the value has already been taken into consideration in the average. (This is where I was trying to skip a line or offset the equation). The formula would then pick back up and search for the next matched pair or two numbers with the same value in a row, for example 1.74 and 1.74. Values without a match pair in the middle of the data set will be thrown out, these are the black numbers seen above. This is also why you don't see any values listed in the blue calculation columns.
[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]0.91[/TD]
[TD]99.23[/TD]
[TD]2753.40[/TD]
[TD][/TD]
[TD]102.18[/TD]
[TD]2755.31 <--- Averages[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1.74[/TD]
[TD]105.13[/TD]
[TD]2757.22




[/TD]
[/TR]
</tbody>[/TABLE]