Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
OK, cheers :-)
-- Regards Ken....................... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with VBA returning the contents of a long formula. | Excel Discussion (Misc queries) | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
revert formula insertion to old method | Setting up and Configuration of Excel | |||
Help with macro formula and variable | Excel Worksheet Functions | |||
Cell doesn't show formula result - it shows formula (CTRL + ' doe. | Excel Worksheet Functions |