# Calculate average for specified numbers from a column

• Hello I need help with this please. If I have in column A:

in cell A2: 77.123456789;88.12345678;122;100.1;MMMM
in cell A3: 11.123456789;33.12345678;50;100.1;VVVV
in cell A4: 70.123456789;89.12345678;251;100.1;HHHH
in cell A5: 22.123456789;99.12345678;72;100.1;DDDD

In cell B2 I want an average of these numbers from A2:A5 rows: 122, 50, 251, 72.

I find this formula:

=MID(SUBSTITUTE(A2,";"," "),31,3)

But this formula can extract the number of only one row.

• Re: Calculate average for specified numbers from a column

Try this

Code
``=MID(SUBSTITUTE(A2,";"," "),26,SEARCH(";",A2,31)-32)``
• Re: Calculate average for specified numbers from a column

[cp]*[/cp]

• Re: Calculate average for specified numbers from a column

Another way.

Array entered

Code
``=AVERAGE(--TRIM(MID(SUBSTITUTE(SUBSTITUTE(A2:A5,";",REPT(" ",256),3),";",REPT(" ",256),2),256,256)))``

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter. (CSE for short.)

• Re: Calculate average for specified numbers from a column

Hi FlameRetired, welcome to OzGrid.

Unfortunately, the OP has already received an answer to this question on another site - which means that by not providing the links to their cross-post, they have in fact wasted your time. This is one of the reasons that it is against the forum rules to cross-post without providing links.

That being said, a good solution nonetheless - so thanks for your contribution.

Regards,

S O

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!