# 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

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

#### 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
3K
Replies
1
Views
693
Replies
20
Views
793
Replies
1
Views
406

### Forum statistics

1,140,924
Messages
5,703,180
Members
421,280
Latest member
Jaycee01 ### 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.

### Which adblocker are you using?    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

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