A simple question about applying a formula

[Peggy1974]

Probably missing something incredibly obvious but time pressure and being tired are not a winning combination.

I have a column of series of numbers like this:

76;28;29
53;23
77;20;98;37
etc

What is the quickest way to calculate the average of each individual serie of numbers (so 44,333 - 38 - 58 etc in this example) in one single action?

 

[Beard]

Probably missing something incredibly obvious but time pressure and being tired are not a winning combination.

I have a column of series of numbers like this:

76;28;29
53;23
77;20;98;37
etc

What is the quickest way to calculate the average of each individual serie of numbers (so 44,333 - 38 - 58 etc in this example) in one single action?

Would the below suffice?

=AVERAGE(N15:P15)

This would take the values from N15, O15 and P15 and calculate the average placing the result in the cell where you type the formula.

 

[RudiS]

Assuming the values run down the A column, here is a macro that will write the averages to column B...

Sub GetAVG()
Dim rC As Range
Dim WrdArray() As String
Dim sData As String
Dim i As Integer
Dim strg As String
    For Each rC In Range("A1").CurrentRegion.Columns(1).Cells
        sData = Cells(rC.Row, 1)
        WrdArray() = Split(sData, ";")
        For i = LBound(WrdArray) To UBound(WrdArray)
            strg = strg & "+" & WrdArray(i)
        Next i
        rC.Offset(0, 1).Formula = "=(" & strg & ")/" & UBound(WrdArray) + 1
        strg = ""
    Next rC
End Sub

 

[Peggy1974]

QUOTE=Beard;4170979]Would the below suffice?

=AVERAGE(N15:P15)

This would take the values from N15, O15 and P15 and calculate the average placing the result in the cell where you type the formula.[/QUOTE]



But don't you get one average (of everything) then? While I need the average of each indivual serie of numbers.

 

[MarcelBeug]

If series of numbers start in A2, enter in B2 the following array formula (confirm with CTRL-SHIFT-ENTER, not just ENTER) and copy down:

=AVERAGE(IFERROR(1*MID($A2,(MID(";"&$A2,ROW(INDIRECT("1:"&1+LEN($A2))),1)=";")*ROW(INDIRECT("1:"&1+LEN($A2))),IFERROR(FIND(";",RIGHT($A2,1+LEN($A2)-ROW(INDIRECT("1:"&1+LEN($A2)))))-1,LEN($A2))),""))

 

[Peggy1974]

Thanks for the help guys!

Another question: assume I have a date formatted like this dd/mm/yy, so e.g. 20/03/1963

How can I get the "63" in this example out of this date (with =mid I assume) in a way it is converted to that digit 63 and you don't get the whole date is a serial number in Excel thing?

 

[MarcelBeug]

If date is in A1:

=MOD(YEAR(A1),100)