is there a more efficient formula than...

[Guest]

If I have the following data:
0.630301621
0.735538614
1.112178958
1.075829421
1.794418509
1.661534827
0.723790191
0.736495518
0.808701582
0.9371253
-0.085016535
0.052665338
0.063743977
0.064828284
1.051556997
1.155830979
0.621846568
0.70646412
0.862235718
1.07312007
2.212618417
2.021491808,

and want to average every pair of values...B3 & B4, B5 & B6, etc..., is
there a more efficent formula than:

=IF(MOD(ROW(),2)=1,AVEDEV(INDIRECT(ADDRESS(ROW(),COLUMN()-2)),INDIRECT(ADDRESS(ROW()+1,COLUMN()-2))),"")


This data set can get quite large, so having a formula that can be filled in
a series is a big help.

Curiously,

wazooli

 

[Guest]

OOPS - change AVEDEV to AVERAGE, and you see where I am coming from. Also,
this data is located in column B in my example.

 

[Ken Wright]

Do you mean you want the average for each pair, eg in C4 say you want the
average of B3:B4, in C6 you want the average of B5:B6 etc? if so then why
not just use a Pivot table as this will potentially handle a large data set
far better than thousands of formulas. Assuming your data in B3:B10000,
Ensure you have a header, say 'Value' in B2, a header say 'Pair' in A2, and
in cell A3 put the following and copy down:-

=INT(ROW()/2-0.5)

When done simply copy and paste special as values as you no longer need the
formulas. Select all data, hit pivot table and Chart report, hit Next /
Next / Finish. Drag Pair to the ROW fields, Value into the DATA field,
right click on any of the values, choose field settings / Summarize by and
select Average

If you want a formula instead as outlined at the top then how about in cell
C4

=IF(MOD(ROW(),2)=0,AVERAGE(B3:B4),"")

and copy down.

 

[Guest]

See? I knew there was a better way. Thanks.

 

[Ken Wright]

But which did you plump for?

 

[Guest]

the " =if(mod(row(),2)=1,average(b3:b4),"") "

works great, and is less filling. thanks again.

 

[Ken Wright]

OK, cheers