While it sounds relatively easy, I am experiencingdifficulty creating a formula to validate the actual result. Within the rangeto be calculated, data includes 0’s, no numbers at all and/or text. For thiscalculation, I started with =AVERAGEIFS(D4:G46,D4:G46,">0",D4:G46,"<>"""),butnoticed it returned an erroneous number. This formula contains the entire data range,but does not exclude row or cells which are part of the result. Therefore itwas modified to=AVERAGEIFS(D4:H12,D4:H12,">0",D4:H12,"<>"""),AVERAGEIFS(D14:H24,D14:H24,">0",D14:H24,"<>"""),butunable to get it working properly. I determined, the ranges were not equal insize. My next step was to try =SUM(D4:D46,G4:G46)/INDEX(FREQUENCY((D4:D46,G4:G46),0),2))but encountered a separate problem. I was unable to add additional criteria anddeal with the unwanted cells/rows. After going back to basics, I was able toget =AVERAGE(D4:D11, G4:G11, D13:D23, G13:G23, D25:D32, G25:G32, D34:D35,G34:G35, D37:D38, G37:G38, D40:D43, G40:G43, D45, G45) to work. However, I havebeen unsuccessful in creating a formula that will be easier to incorporate futurechanges (ie: additional columns)
The data to be averaged is contained within columns D and G, (lines 4 to 46),but must exclude rows 12 (cells D12 and G12), 24, 33, 36, 39, 44 and 46.
Any assistance would be greatly appreciated.
The data to be averaged is contained within columns D and G, (lines 4 to 46),but must exclude rows 12 (cells D12 and G12), 24, 33, 36, 39, 44 and 46.
Any assistance would be greatly appreciated.
