Averaging cells which contain #DIV/0!
This sounds like what I need, also. Except in my situation, the cells I need
to find the average of are not contiguous. "Domenic" wrote: Try... =AVERAGE(IF(ISNUMBER(A1:A10),A1:A10)) ...entered using CONTROL+SHIFT+ENTER. Hope this helps! -- Domenic ------------------------------------------------------------------------ Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785 View this thread: http://www.excelforum.com/showthread...hreadid=271242 |
maryj Wrote: This sounds like what I need, also. Except in my situation, the cells I need to find the average of are not contiguous. Which cells do you want to average? -- Domenic ------------------------------------------------------------------------ Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785 View this thread: http://www.excelforum.com/showthread...hreadid=271242 |
E10,E13,E16,E19,E22. These values were all derived from a sumif.
"Domenic" wrote: maryj Wrote: This sounds like what I need, also. Except in my situation, the cells I need to find the average of are not contiguous. Which cells do you want to average? -- Domenic ------------------------------------------------------------------------ Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785 View this thread: http://www.excelforum.com/showthread...hreadid=271242 |
Try the following array formulas, entered using CONTROL+SHIFT+ENTER... =AVERAGE(IF((MOD(ROW(E10:E22),3)=1)*(ISNUMBER(E10: E22)),E10:E22)) OR =IF(COUNT(E10,E13,E16,E19,E22),AVERAGE(IF((MOD(ROW (E10:E22),3)=1)*(ISNUMBER(E10:E22)),E10:E22)),"") Hope this helps! maryj Wrote: E10,E13,E16,E19,E22. These values were all derived from a sumif. "Domenic" wrote: maryj Wrote: This sounds like what I need, also. Except in my situation, the cells I need to find the average of are not contiguous. Which cells do you want to average? -- Domenic ------------------------------------------------------------------------ Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785 View this thread: http://www.excelforum.com/showthread...hreadid=271242 -- Domenic ------------------------------------------------------------------------ Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785 View this thread: http://www.excelforum.com/showthread...hreadid=271242 |
That did it! Thanks!
"Domenic" wrote: Try the following array formulas, entered using CONTROL+SHIFT+ENTER... =AVERAGE(IF((MOD(ROW(E10:E22),3)=1)*(ISNUMBER(E10: E22)),E10:E22)) OR =IF(COUNT(E10,E13,E16,E19,E22),AVERAGE(IF((MOD(ROW (E10:E22),3)=1)*(ISNUMBER(E10:E22)),E10:E22)),"") Hope this helps! maryj Wrote: E10,E13,E16,E19,E22. These values were all derived from a sumif. "Domenic" wrote: maryj Wrote: This sounds like what I need, also. Except in my situation, the cells I need to find the average of are not contiguous. Which cells do you want to average? -- Domenic ------------------------------------------------------------------------ Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785 View this thread: http://www.excelforum.com/showthread...hreadid=271242 -- Domenic ------------------------------------------------------------------------ Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785 View this thread: http://www.excelforum.com/showthread...hreadid=271242 |
All times are GMT +1. The time now is 09:36 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com