Help with Excel Average Function

Pembily

I have lots of weather data that I need to analyse, I want to get every the average of every 11 cells, is there a quick formula I could use??

Thanks!

chin_grin

I'm pretty sure if you select the cells, then click on functions there should be one in there called Average that'll do it for you?

Or this: http://www.techonthenet.com/excel/formulas/average.php

djrlittleton

=average(b2:b12) as a e.g. then just fill to the right if you are running the equation across and job then

the fx wizard will guide you through it also

Pembily

Sorry I should have been more specific, yes I am aware how to use the average function as in =average(B12:B23) and then =average(B13:B24) and so on but all I know is to type this in over 250 times as I have nearly 3000 cells:eek:

Thanks

djrlittleton

all you have to do is 1, if you have colloumns and are average the total of each of those, you just do the 1st highlight the cells where the eq is needed,

then edit, fill to right, this place the equation in each of the cells so for e.g. if the 1st was b1:b10 next cell to the right will be c1:b10

Pembily

djrlittleton wrote: »

all you have to do is 1, if you have colloumns and are average the total of each of those, you just do the 1st highlight the cells where the eq is needed,

then edit, fill to right, this place the equation in each of the cells so for e.g. if the 1st was b1:b10 next cell to the right will be c1:b10

Sadly the information is all in column B, it would be alot easier if it was broken up like that but I have data in B1:B37453! Dragging down doesn't work.

What I need is an automatic formula that does average of B1:B12, then B13:B24, B25:B36 all the way down to B37453!

Any insight?

Thanks

denachoman

Pembily wrote: »

Sadly the information is all in column B, it would be alot easier if it was broken up like that but I have data in B1:B37453! Dragging down doesn't work.

What I need is an automatic formula that does average of B1:B12, then B13:B24, B25:B36 all the way down to B37453!

Any insight?

Thanks

If you don't have an index already - Insert a new column (e.g. Column A) that has an index for each row entry (e.g. a numeric index from 1 to 37453). Then in a separate column use this formula and copy it down for all rows

=if(mod(A1,12)=1,average(B1:B12),"")

This will work if your index is in column A and your data is in Column B.

It will return a value for rows 1,13,25,37 etc and blanks for all other rows.

Thoie

The easiest way is on cell 11 (of whatever column you're putting the average in, let's say it's C11) put =average(B1:B12)

Now, highlight cells C1 to C11 THEN double click the little black square in the bottom right of your highlighted cells.

It will copy down the range to the end of 37xxx rows, with the formula in every 11th box.

If you want to make it a bit more useful, in the cell to the right of the average, type in 1. Then, when highlighting to copy down, select C1 : D11, then double click the little black box.

This will give you an "index" of sorts beside the average, so if you want to pull those away to somewhere else you can refer back to the originals easily.

Pembily

denachoman wrote: »

If you don't have an index already - Insert a new column (e.g. Column A) that has an index for each row entry (e.g. a numeric index from 1 to 37453). Then in a separate column use this formula and copy it down for all rows

=if(mod(A1,12)=1,average(B1:B12),"")

This will work if your index is in column A and your data is in Column B.

It will return a value for rows 1,13,25,37 etc and blanks for all other rows.

Thoie wrote: »

The easiest way is on cell 11 (of whatever column you're putting the average in, let's say it's C11) put =average(B1:B12)

Now, highlight cells C1 to C11 THEN double click the little black square in the bottom right of your highlighted cells.

It will copy down the range to the end of 37xxx rows, with the formula in every 11th box.

If you want to make it a bit more useful, in the cell to the right of the average, type in 1. Then, when highlighting to copy down, select C1:D11, then double click the little black box.

This will give you an "index" of sorts beside the average, so if you want to pull those away to somewhere else you can refer back to the originals easily.

Thanks so so much guys - both total life savers!!!!

Legends!!!