+ Reply to Thread
Results 1 to 4 of 4

Find a blank cell and calculate the average of all cells above till the next blank

  1. #1
    Forum Contributor
    Join Date
    06-25-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2003
    Posts
    179

    Find a blank cell and calculate the average of all cells above till the next blank

    Hello
    I have a list of 26k rows and 3 cols
    col A: Actual day ColB: type of contract Col 3: price


    Col A: repeats data so it could say the date for 10 rows and then it will have a row saying total.

    I need a code that goes from the bottom of the list up and looks for a blank then inserts the average of all the cells above it untill it hits the next blank.

    For example

    1: 10
    2: 20
    3: 40
    4: 34
    5: 12
    6: BLANK
    7: 45
    8: 14
    9: 76
    10: 25
    11: 99
    12: blank

    What i need it to do is look at the blank in 12 and calc the average from 7:11 and then move to 6 and calc the blank from 1:5
    Obviously this would need to be for 26k pieces of data.

    I've got a basic understanding of VBA however, i have no idea how to go about this.

  2. #2
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,164

    Re: Find a blank cell and calculate the average of all cells above till the next blank

    Do you have a header at row 1? Or does your data start at 1?
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  3. #3
    Forum Contributor
    Join Date
    06-25-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2003
    Posts
    179

    Re: Find a blank cell and calculate the average of all cells above till the next blank

    I do have a header.
    just in row 1 though.

  4. #4
    Valued Forum Contributor MaczaQ's Avatar
    Join Date
    06-03-2011
    Location
    Poland
    MS-Off Ver
    Excel 2003 / XP
    Posts
    510

    Re: Find a blank cell and calculate the average of all cells above till the next blank

    Hi,

    Try this:
    Please Login or Register  to view this content.
    Best Regards
    MaczaQ
    ---------------------------------------------------------------------------------------------------------------------------
    If you are satisfied with the solution(s) provided, please mark your thread as Solved
    If you are pleased with my answer consider to rate it. To thank someone who has helped you, click on the star icon below their name.
    - This way you will add him some reputation points ... thanks in advance.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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