Results 1 to 3 of 3

Thread: How to condense several rows into fewer rows?

  1. #1
    Junior Member
    Join Date
    Oct 2011
    Posts
    1
    Rep Power
    0

    Question How to condense several rows into fewer rows?

    Hello,

    Is there a way to condense several rows of data into a smaller number of rows?

    For example: Say I have 10 rows of data:

    10
    8
    90
    7
    6
    78
    3
    4
    5
    55

    And I want to condense them in to five rows, perhaps based on an average of every "n" rows or etc... Is there a way to do this in excel, or any type of algorithm that anyone can suggest?

    If I average every two rows based on the above 10, then the condensed result would be:

    9
    48.5
    84
    3.5
    30

    In addition, is there a way to do this if I am dealing with numbers of rows that are not divisible by each other (for example if I want to condense the above 10 rows into 4, then I would have to average every 2.5 rows)?

    Thanks a lot!
    Last edited by gavin_machine; 10-03-2011 at 10:02 AM.

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi gavin_machine,

    Welcome to ExcelFox !!!

    I assume your data in A2:A11,

    In B2 and copied down,

    =AVERAGE(INDEX($A$2:$A$11,ROWS($B$2:B2)*$C$1-1):INDEX($A$2:$A$11,ROWS($B$2:B2)*$C$1))

    where C1 holds the interval

    HTH
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Or a UDF to do the trick....

    Code:
    Function Condensor(rngSourceRange As Variant, lngFragments As Long, lngIndex As Long) As Variant
    
        Dim lng As Long
        Dim sngSegment As Single
        Dim varSourceRange As Variant
        Dim sngFraction As Single
        Dim sngDivisions As Long
        Dim varIntermediate As Variant
        Dim sngCounter As Single
        Dim lngCounter As Long
        
        On Error GoTo ErrH
        varSourceRange = Application.Transpose(rngSourceRange)
        lng = UBound(varSourceRange)
        sngSegment = lng / lngFragments
        If Int(sngSegment) = sngSegment Then
            For lng = 1 + (sngSegment * (lngIndex - 1)) To sngSegment * lngIndex
                Condensor = Condensor + varSourceRange(lng)
            Next lng
        Else
            sngFraction = Abs(Int(sngSegment) - sngSegment)
            sngDivisions = 1 / sngFraction
            ReDim varIntermediate(1 To lng * sngDivisions)
            For lng = 1 To lng * sngDivisions
                If sngCounter < sngDivisions Then
                sngCounter = sngCounter + 1
                varIntermediate(lng) = varSourceRange(lngCounter + 1) / sngDivisions
                Else
                    lngCounter = lngCounter + 1
                    sngCounter = 1
                    varIntermediate(lng) = varSourceRange(lngCounter + 1) / sngDivisions
                End If
            Next lng
            For lng = 1 + ((sngSegment / Abs(Int(sngSegment) - sngSegment)) * (lngIndex - 1)) To Int((sngSegment / Abs(Int(sngSegment) - sngSegment)) * lngIndex)
                Condensor = Condensor + varIntermediate(lng)
            Next lng
        End If
        Exit Function
    ErrH: Condensor = ""
        
    End Function
    Check attached workbook for reference
    Attached Files Attached Files
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

Similar Threads

  1. Delete Rows
    By ayazgreat in forum Excel Help
    Replies: 2
    Last Post: 12-13-2012, 11:48 AM
  2. Transpose data into Rows
    By vikash200418 in forum Excel Help
    Replies: 2
    Last Post: 04-10-2012, 11:02 PM
  3. Replies: 2
    Last Post: 05-06-2011, 02:59 AM
  4. Delete Empty Rows
    By Rasm in forum Excel Help
    Replies: 4
    Last Post: 04-28-2011, 02:13 AM
  5. Deleting blank rows
    By Rasm in forum Excel and VBA Tips and Tricks
    Replies: 0
    Last Post: 04-14-2011, 03:14 AM

Tags for this Thread

Posting Permissions

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