Help on a formula
 

I have a workbook that has about 230 rows of data. Three lines for
every name. On the third of the three lines is an average of the two
rows above it.

in C234:O234 i have a row that is titled "Percent Monitored". when it
used to be very small, i had the following formula in these cells:
=COUNTA(C4,C7,C10,C13,C16,C19,C22,C25,C28,C31,C34, C37,C40,C43,C46,C49,C52,C55,C58,C61,C64,C67,C70,C7 3,C76,C79,C82,C91)/COUNTA(A4:A91)

This gave me the number of audits divided by the number of names in
Column A. Now, i have way too many rows to do this. What would be an
easier way to determine teh amount of monitors (keep in mind that every
three rows is an average row of the two previous rows) divided by names
in Column A?

Thanks.


--
ForSale
------------------------------------------------------------------------
ForSale's Profile: http://www.excelforum.com/member.php...o&userid=11896
View this thread: http://www.excelforum.com/showthread...hreadid=277223

Hi
try
=SUMPRODUCT(--(MOD(ROW(C3:C200)-4,3)=0),C3:C200)/SUMPRODUCT(--(MOD(ROW(
C3:C200)-4,3)=0),--ISNUMBER(C3:C200))

--
Regards
Frank Kabel
Frankfurt, Germany

"ForSale" schrieb im Newsbeitrag
...

I have a workbook that has about 230 rows of data. Three lines for
every name. On the third of the three lines is an average of the two
rows above it.

in C234:O234 i have a row that is titled "Percent Monitored". when
it
used to be very small, i had the following formula in these cells:

=COUNTA(C4,C7,C10,C13,C16,C19,C22,C25,C28,C31,C34, C37,C40,C43,C46,C49,C
52,C55,C58,C61,C64,C67,C70,C73,C76,C79,C82,C91)/COUNTA(A4:A91)

This gave me the number of audits divided by the number of names in
Column A. Now, i have way too many rows to do this. What would be
an
easier way to determine teh amount of monitors (keep in mind that
every
three rows is an average row of the two previous rows) divided by
names
in Column A?

Thanks.


--
ForSale
---------------------------------------------------------------------
---
ForSale's Profile:
http://www.excelforum.com/member.php...o&userid=11896
View this thread:
http://www.excelforum.com/showthread...hreadid=277223

On Wed, 10 Nov 2004 16:53:28 -0600, ForSale
wrote:


I have a workbook that has about 230 rows of data. Three lines for
every name. On the third of the three lines is an average of the two
rows above it.

in C234:O234 i have a row that is titled "Percent Monitored". when it
used to be very small, i had the following formula in these cells:
=COUNTA(C4,C7,C10,C13,C16,C19,C22,C25,C28,C31,C34 ,C37,C40,C43,C46,C49,C52,C55,C58,C61,C64,C67,C70,C 73,C76,C79,C82,C91)/COUNTA(A4:A91)

This gave me the number of audits divided by the number of names in
Column A. Now, i have way too many rows to do this. What would be an
easier way to determine teh amount of monitors (keep in mind that every
three rows is an average row of the two previous rows) divided by names
in Column A?

Thanks.

If I understand your setup correctly, the number of averages in any column
would be something like =COUNT(C4:C91)/3 and your number of names would be
=COUNTA(A4:A91).

So would not the percent monitored be simply

=(COUNT(C4:C91)/3)/COUNTA(A4:A91)


--ron