remove 0s after cells

[Rammy]

Hi All,

I need an Excel function (or VBA code) that will average including first 0's and only including 1 of that last 0s. For instance,

0
0
2
0
4
0
0
0

Would average out to 1, since (0+0+2+0+4+0)/6 = 1.
The problem is there may be multiple zeros between two positive numbers.
Honestly, I have thought about this for sometime (and started a similar thread for a related problem), but I haven't had much luck.

Any help would be fantastic!

Thanks!

p.s. I am using Excel 2003.

 

[AlphaFrog]

If your data starts in cell A1 and goes down column A, then try this...

=AVERAGE(INDIRECT("A1:A"&SUMPRODUCT(MAX((ROW(A1:A1000))*(A1:A1000<>0)))+1))

 

[Rammy]

It works perfectly for what I asked, but I need to be able to drag the formula
horizontally (indirect doesn't seem to like the quotes).

Thank you very much!

 

[Aladin Akyurek]

Hi All,

I need an Excel function (or VBA code) that will average including first 0's and only including 1 of that last 0s. For instance,

0
0
2
0
4
0
0
0

Would average out to 1, since (0+0+2+0+4+0)/6 = 1.
The problem is there may be multiple zeros between two positive numbers.
Honestly, I have thought about this for sometime (and started a similar thread for a related problem), but I haven't had much luck.

Any help would be fantastic!

Thanks!

p.s. I am using Excel 2003.

Looks like...

Control+shift+enter, not just enter:

=AVERAGE(A1:INDEX(A1:A8,MIN(ROWS(A1:A8),MATCH(9.99999999999999E+307,1/A1:A8)+1)))

 

[Rammy]

The last one worked perfectly, and I hate ask again, but I need the same function to work on rows (I would tranpose, but there are too many rows to fill up the columns).

 

[Aladin Akyurek]

The last one worked perfectly, and I hate ask again, but I need the same function to work on rows (I would tranpose, but there are too many rows to fill up the columns).

I guess you want it row-wise (horizontal)...

Control+shift+enter, not just enter:

=AVERAGE(A1:INDEX(A1:F1,MIN(COLUMNS(A1:F1),MATCH(9.99999999999999E+307,1/A1:F1)+1)))

 

[Rammy]

Thank you again, you guys are geniuses.