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!

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

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