 # 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

## Files

• Re: problem with AVERAGE function

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

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

• 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!