Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie
Hi there,
There is an issue with role permissions that is being worked on at the moment.
If you are having trouble with access or permissions on regional forums please post here to get access: https://www.boards.ie/discussion/2058365403/you-do-not-have-permission-for-that#latest

Help with Excel Average Function

  • 23-04-2010 12:55pm
    #1
    Registered Users, Registered Users 2 Posts: 14,404 ✭✭✭✭


    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!


Comments

  • Closed Accounts Posts: 10,808 ✭✭✭✭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


  • Registered Users, Registered Users 2 Posts: 255 ✭✭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


  • Registered Users, Registered Users 2 Posts: 14,404 ✭✭✭✭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


  • Registered Users, Registered Users 2 Posts: 255 ✭✭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


  • Registered Users, Registered Users 2 Posts: 14,404 ✭✭✭✭Pembily


    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


  • Advertisement
  • Registered Users, Registered Users 2 Posts: 173 ✭✭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.


  • Registered Users, Registered Users 2 Posts: 6,344 ✭✭✭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.


  • Registered Users, Registered Users 2 Posts: 14,404 ✭✭✭✭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!!! :D


Advertisement