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


    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!

Register Yourself Login