I have Column Q total the gross profit's in each row. So Q3=total $ in
each cell of Row 3.

There are 120 rows. With a total $ amount at the bottom of column Q. I
need to track my average gross per unit.

I found out a way to average Column Q without factoring in 0.
=AVERAGE(IF(Q3:Q120<0, Q3:Q120,""))

I just discovered today that I need it to count 0's but I don't need it
to count BLANK Cells. Since each cell in Column Q has something in it,
if I simply us the AVERAGE(Q3:Q120) it takes my total gross and divdes
by 117 cells because they all have either a $ amount or they have
=SUM(K12:P12) or the like.

Since I will sometimes have a $0 gross and it needs to be taken into
consideration is there anyway to have it AVERAGE Column Q that only
have a $ amount, or to make it not count Blank cells.

Of course the cells have +SUM commands in them but they show as blank.

Thanks guys.


--
lherndo
------------------------------------------------------------------------
lherndo's Profile: http://www.excelforum.com/member.php...o&userid=15569
View this thread: http://www.excelforum.com/showthread...hreadid=501285

I haven't tested this, but you could try:

=AVERAGE(IF(Q3:Q120<"", Q3:Q120,""))

I just changed the test for zero to a test for blank.

You might also put ISBLANK in place of the zero.

Hope this helps.

I thought you couldn't average any Gross amount. I am trying to average
Gross Margin and it doesn't work

"lherndo" wrote:


I have Column Q total the gross profit's in each row. So Q3=total $ in
each cell of Row 3.

There are 120 rows. With a total $ amount at the bottom of column Q. I
need to track my average gross per unit.

I found out a way to average Column Q without factoring in 0.
=AVERAGE(IF(Q3:Q120<0, Q3:Q120,""))

I just discovered today that I need it to count 0's but I don't need it
to count BLANK Cells. Since each cell in Column Q has something in it,
if I simply us the AVERAGE(Q3:Q120) it takes my total gross and divdes
by 117 cells because they all have either a $ amount or they have
=SUM(K12:P12) or the like.

Since I will sometimes have a $0 gross and it needs to be taken into
consideration is there anyway to have it AVERAGE Column Q that only
have a $ amount, or to make it not count Blank cells.

Of course the cells have +SUM commands in them but they show as blank.

Thanks guys.


--
lherndo
------------------------------------------------------------------------
lherndo's Profile: http://www.excelforum.com/member.php...o&userid=15569
View this thread: http://www.excelforum.com/showthread...hreadid=501285