+ Reply to Thread
Results 1 to 6 of 6

Average of every two cells

  1. #1
    Registered User
    Join Date
    10-05-2017
    Location
    usa
    MS-Off Ver
    2013
    Posts
    11

    Average of every two cells

    Hello,
    I've read a lot but somehow I can't figure out why this supposedly simple task is making me so much head ache.
    I have two ptoblems:
    1. I need to average every two cells from one sheet into a cells of another sheet
    e.g.
    sheet 1
    13 1.2
    14 2.1
    15 5.7
    16 6.5
    17 4.2
    18 8.1
    19 empty
    20 45.2


    From sheet A row C13 and C14 need to be averaged, then C15+C16, the C17+C18 etc and written in cells c13 in sheet B. Sometime there might be a gap with no values in.

    2. I need to have the names written in column B13 from sheet A also written into sheet B in front of the calculated average
    What I found is this formula:
    =INDEX('Sheet B'!$B$12:$B$1001,ROWS(B13:B$13)*2) for B13
    =INDEX('Sheet B'!$B$12:$B$1001,ROWS(B$13:B88)*2) for B88

    Is there an easier version of that and a way to fill gaps in case no name is written in it?
    I mean if I see an error I could fix it, but if it is automatically it would be wonderful.

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,002

    Re: Average of every two cells

    With data starting in Sheet B C13:

    =AVERAGE(INDEX('Sheet B'!$C$13:$C$22, 2*ROWS('Sheet B'!$C$13:C14) - 1):INDEX('Sheet B'!$C$13:$C$22,2*ROWS('Sheet B'!$C$13:C14)))

    copied down.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Registered User
    Join Date
    10-05-2017
    Location
    usa
    MS-Off Ver
    2013
    Posts
    11

    Re: Average of every two cells

    Heck, that simple task got quite complicated.
    Thank you a lot! It works!

    Now, if I like to apply the same to columns D, E, F... and have the row going to infinite I changed the formula to following:
    =AVERAGE(INDEX('Sheet B'!C$13:C$10000, 2*ROWS('Sheet B'!C$13:C13) - 1):INDEX('Sheet B'!C$13:C$10000,2*ROWS('Sheet B'!C$13:C13)))
    I got rid off some $ and changed value 22 to 10000 (which is not infinite but a high number). I also changed C14 to C13 so the first cell is written in and not the 2nd.

    I wonder if something is off so that I won't get surprises in the middle of the sheet.

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,002

    Re: Average of every two cells

    formula looks fine to me....

  5. #5
    Registered User
    Join Date
    10-05-2017
    Location
    usa
    MS-Off Ver
    2013
    Posts
    11

    Re: Average of every two cells

    thank you so much

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,002

    Re: Average of every two cells

    You're welcome.



    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 7
    Last Post: 09-24-2016, 06:48 PM
  2. Replies: 3
    Last Post: 10-31-2013, 04:50 AM
  3. [SOLVED] need formula to average cells but if one cell has N/A then dont average and input N/A
    By CityInspector in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-08-2013, 04:16 PM
  4. Replies: 14
    Last Post: 06-14-2013, 09:00 PM
  5. Replies: 10
    Last Post: 04-25-2013, 06:12 PM
  6. The rank of average ranks excluding empty cells but including their average.
    By Terminal45 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-21-2012, 03:44 AM
  7. [SOLVED] average cells, show 0 if nothing to average
    By Kycajun in forum Excel General
    Replies: 8
    Last Post: 06-21-2006, 02:40 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1