Help on a formula

[ForSale]

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

in C234:O234 i have a row that is titled "Percent Monitored". when i
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,C73,C76,C79,C82,C91)/COUNTA(A4:A91)

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

Thanks

 

[Frank Kabel]

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

 

[Ron Rosenfeld]

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,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.

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