# 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

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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.

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:
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.

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))),""))``

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?

If date is in A1:

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

Replies
7
Views
929
Replies
1
Views
2K
Replies
3
Views
2K
Replies
1
Views
7K
Replies
1
Views
2K

1,220,987
Messages
6,157,239
Members
451,407
Latest member
vdaesety

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