problem with AVERAGE function

  • Respected,forum.ozgrid.com/index.php?attachment/68818/
    I have to calculate average of attached an excel 20-25 reading in which there are various combination like
    0.01, -0, -0.01 and 0,
    The format comes from the station which takes the reading so I am not able to control them.
    There are about 200 such calculation daily so at present I have to modify formulae for each to adjust .
    I am looking for a formaule which applies and give correct result though the readings can be in any format.


    I have attached xls file to make the things clear


    Zohar Batterywala

  • Re: problem with AVERAGE function


    Try this formula, however it also takes the Decimals into consideration!


    [BFN]=AVERAGE(IF(A$1:A$37="","",VALUE(IFERROR(LEFT(A$1:A$37,FIND("#",A$1:A$37,1)-1),""))))[/BFN]


    It's an array formula so CTRL+SHIFT+ENTER

  • Re: problem with AVERAGE function


    Respected Sir Mohammed Ismail,
    Salaam-Alaikum
    I am not able to understand this
    "It's an array formula so CTRL+SHIFT+ENTER"


    JazakAllahu Khair

    Zohar Batterywala

  • Re: problem with AVERAGE function


    Quote from StephenR;768972

    You could use this formula in a helper column and then average that.


    =VALUE(LEFT(A1,FIND("#",A1)-1))


    Respected Sir Stephen,
    THANK YOU
    Zohar Batterywala

Participate now!

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