If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#2
|
|||
|
|||
Average based on two rows and two criterea?
Anonymous wrote:
[b]Caution: I have a tendency to go to San Fran by way of Miami. With that disclaimer... Haha, I do the same thing... What I have used in the mean time as the non-"total" fields are not populated by zeros is a count of all "total" labels minus the count of the zeros in the rows below. Sample plus error trapment: =IF(ISERROR(SUMIF($F$5:$GC$42,"Total",F7:HN7)/(COUNTIF($F$5:$GC$5,"total")-(COUNTIF(F7:GC7,0)))-1),"-",SUMIF($F$5:$GC$42,"Total",F7:HN7)/(COUNTIF($F$5:$GC$5,"total")-(COUNTIF(F7:GC7,0)))) The length of this spreadsheet is constantly changing. You have given me a good idea though, every new idea is welcome! --- Message posted from http://www.ExcelForum.com/ |
#3
|
|||
|
|||
Average based on two rows and two criterea?
If you insist on having a formula without auxiliary computations...
=AVERAGE(IF((MOD(COLUMN($A2:$O2)-CELL("Col",$A2)+1,5)=0)*$A2:$O2,$A2:$O2)) which must be confirmed with control+shift+enter instead of just with enter. "exceller" wrote in message ... I have the solution: Too much to explain, so check the attached file. Attachment filename: example.xls Download attachment: http://www.excelforum.com/attachment.php?postid=380918 --- Message posted from http://www.ExcelForum.com/ |
Thread Tools | |
Display Modes | |
|
|