is there a more efficient formula than...
 

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(),C OLUMN()-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

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

"Wazooli" wrote:

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(),C OLUMN()-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

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.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------
<snip

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

"Ken Wright" wrote:

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.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------
<snip

But which did you plump for?

--
Regards
Ken.......................

<snip

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

works great, and is less filling. thanks again.

"Ken Wright" wrote:

But which did you plump for?

--
Regards
Ken.......................

<snip

OK, cheers :-)

--
Regards
Ken.......................