How to condense several rows into fewer rows?

[gavin_machine]

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!

[Admin]

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

[Excel Fox]

Or a UDF to do the trick....



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