Request for information on how to ignore leading Zeroes in Average Function

Hello Excel Champs,

Need your help with figuring out on how to ignore leading Zeroes in Average Function.

I have a a list of Row Numbers which have leading Zeroes, I need to ignore Leading Zeroes in my data while using Average Function, your response would be appreciated.

Thanks for your time,
Joshi Ram

If you have leading zeroes showing, then that implies that the values are text values, in which case the AVERAGE function will not be able to evaluate them directly. (It might be that the leading zeroes are put there by formatting, in which case there should be no problem, as formatting does not affect the underlying value). You can use this array* formula to change the text values to numbers:

=AVERAGE((A1:A10)*1)

*Note that an array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE), instead of the usual < Enter >.

Hope this helps.

Pete

Dear Pete_Uk,

The Problem with the data is, the Row Numbers are as follows 0 0 0 0 58 39 53 39

I want the average to ignore the first four Zeroes(Leading Zeroes in the Data which is not sorted) If you could help me with this, it would be awesome. Thanks for taking time to answer it, I appreciate it.

Best,
Joshi Ram

In that case you can use this normally-entered formula:

=AVERAGEIF(A1:A10,"<>0")

Adjust the range to suit.

Hope this helps.

Pete

Hey Pete,

The Data is pretty long with 303 Coloumns and 4000 rows, Zeroes are at the beginning and in between the data. I want to average by row, by ignoring just the Leadning Zeros sorted by Date.

Please help me with this.

Thanks,
Joshi Ram

So your data goes out to column KQ? You can use this formula in cell KR2:

=AVERAGEIF(A2:KQ2,"<>0")

which will give you the average for row 2. Then you can copy that formula down for the other rows.

Hope this helps.

Pete

Hey Pete,

Thanks, but I just wnat to ignore the leading Zeros, not all the zeros in the data. The data is assigned in chronological order and I can't sort it, all I need is to ignore just the leading zeroes in the beginning.

Thanks again,
Joshi Ram

Okay, try this one in KR2:

=AVERAGE(INDEX(A2:KQ2,MATCH(0.5,A2:KQ2)+1):KQ2)

then copy down.

Hope this helps.

Pete