AVERAGE Formula Help

[RPGreg]

I'm trying to figure out how to average a row of cells (no breaks), but only if there is a value in the cells.

For Example, if I have a row of B4 through M4, and I have 4 cells w/ values, I want the average to calculate based on 4 numbers. If I add values in two more cells, it should then calculate based on 6. The AVERAGE wants to take all 10 cells, so my numbers are coming out too low.

Feel like this should be an easy one.

Thanks,

Greg

[mrice]

Are you using the built in AVERAGE function? - this should ignore blanks as you wish. If you still have problems, I would suggest posting a copy of the sheet that you are working on as there maybe something unusual about it.

[RPGreg]

I found this in another thread. I originally tried it, but I must have not put in all the correct parameters. Thanks to DaddyLongLegs!!

...and you want 6? The AVERAGE functions suggested ignore blanks (as you asked) but not zeroes. Try the version I suggested in my edit to last post, i.e.

=SUM(G11,I11,J11)/MAX(1,(G11>0)+(I11>0)+(J11>0))

[daddylonglegs]

If you have a continuous range like B4:M4 then you can use a simple AVERAGE function to ignore blanks, i.e.

=AVERAGE(B4:M4)

....but if you want to ignore zeroes too try AVERAGEIFS

=AVERAGEIFS(B4:M4,">0")

[RPGreg]

Ultimately, I wanted it to ignore blank cells which works great using your formula that you wrote for another user.

Thanks for the reply!

Greg