# A simple question about applying a formula

#### Peggy1974

##### New Member
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

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

#### Beard

##### New Member
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
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
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
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
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

##### Well-known Member
If date is in A1:

Code:
``=MOD(YEAR(A1),100)``

Replies
1
Views
5K
Replies
1
Views
936
Replies
20
Views
813
Replies
1
Views
426

### Forum statistics

1,175,947
Messages
5,900,498
Members
434,833
Latest member
lulubalisa ### 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?    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