Average rows and place that value in a new column

Casey · May 16th, 2002, 09:32 AM

I'm trying to take two numbers in cells B4 & B5 average them and then place this average in C4. The catch is that I also have numbers in B6 & B7 that need to get averaged and put in B6. This goes on for a few hundred rows. How do I output to C4 and how do I get it to count up two cells, average them, then move to the next two cells and do the same? Thanks for any help.

sann0638 · May 16th, 2002, 09:59 AM

if all the averages are of only two cells, you should be able to put the AVERAGE function in B4, leave B5 blank, and copy these two cells over the whole column. this should put the AVERAGE function into every second row of the column.

rgds,
mike

Adiv · May 16th, 2002, 10:04 AM

" I also have numbers in B6 & B7 that need to get averaged and put in B6. "

Wouldn't that create a circular reference?

bergy · May 16th, 2002, 10:14 AM

Try placing this formula in cell C4 and then drag into all other cells in column C.

=AVERAGE(OFFSET(B4,ROW()-ROW($B$4),0),OFFSET(B4,ROW()-ROW($B$4)+1,0))

bergy · May 16th, 2002, 10:38 AM

I have re-read your original post and realized I didn't quite grasp what you were trying to accomplish. Please ignore my first reply. Sorry

bergy · May 16th, 2002, 10:39 AM

I have re-read your original post and realized I didn't quite grasp what you were trying to accomplish. Please ignore my first reply. Sorry

Chris Davison · May 16th, 2002, 01:41 PM

Bergy,

I'd go with Mike's suggestion

however, you may be able to simplify your logic using the ROW attributes rather than OFFSETTING :

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

May 16th, 2002, 01:41 PM	#7
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	Bergy, I'd go with Mike's suggestion however, you may be able to simplify your logic using the ROW attributes rather than OFFSETTING : =IF(MOD(ROW(),2)=0,AVERAGE(B4:B5),"") __________________ :: Pharma Z - Family drugstore ::

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 16th, 2002, 09:32 AM	#1
Casey Board Regular Join Date: Feb 2002 Posts: 82	I'm trying to take two numbers in cells B4 & B5 average them and then place this average in C4. The catch is that I also have numbers in B6 & B7 that need to get averaged and put in B6. This goes on for a few hundred rows. How do I output to C4 and how do I get it to count up two cells, average them, then move to the next two cells and do the same? Thanks for any help.

May 16th, 2002, 09:59 AM	#2
sann0638 Board Regular Join Date: Mar 2002 Posts: 175	if all the averages are of only two cells, you should be able to put the AVERAGE function in B4, leave B5 blank, and copy these two cells over the whole column. this should put the AVERAGE function into every second row of the column. rgds, mike

May 16th, 2002, 10:04 AM	#3
Adiv New Member Join Date: May 2002 Posts: 7	" I also have numbers in B6 & B7 that need to get averaged and put in B6. " Wouldn't that create a circular reference?

May 16th, 2002, 10:14 AM	#4
bergy Board Regular Join Date: Apr 2002 Posts: 112	Try placing this formula in cell C4 and then drag into all other cells in column C. =AVERAGE(OFFSET(B4,ROW()-ROW($B$4),0),OFFSET(B4,ROW()-ROW($B$4)+1,0))

May 16th, 2002, 10:38 AM	#5
bergy Board Regular Join Date: Apr 2002 Posts: 112	I have re-read your original post and realized I didn't quite grasp what you were trying to accomplish. Please ignore my first reply. Sorry

May 16th, 2002, 10:39 AM	#6
bergy Board Regular Join Date: Apr 2002 Posts: 112	I have re-read your original post and realized I didn't quite grasp what you were trying to accomplish. Please ignore my first reply. Sorry