Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
revert formula insertion to old method | Setting up and Configuration of Excel | |||
Simple formula doesn't quite add up | Excel Discussion (Misc queries) | |||
how do I make a formula NOT change when the data range is moved? | Excel Discussion (Misc queries) | |||
Excel2K: Is it possible to use dynamic named ranges in custom data validation formula? | Excel Discussion (Misc queries) | |||
Excel 2003 - Formula result shows as 0:00 | Excel Worksheet Functions |