A simple question about applying a formula

Peggy1974

New Member
Joined
Apr 19, 2015
Messages
12
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?
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Beard

New Member
Joined
May 24, 2015
Messages
14
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?

Code:
=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

Active Member
Joined
May 7, 2015
Messages
349
Assuming the values run down the A column, here is a macro that will write the averages to column B...

Code:
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
 
Last edited:

Peggy1974

New Member
Joined
Apr 19, 2015
Messages
12
QUOTE=Beard;4170979]Would the below suffice?

Code:
=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

Well-known Member
Joined
Apr 25, 2014
Messages
1,811

ADVERTISEMENT

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:
Code:
=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

New Member
Joined
Apr 19, 2015
Messages
12
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,132,802
Messages
5,655,383
Members
418,195
Latest member
LabraLime

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top