Formula to average multiple ranges containing negative numbers and zeros

[Lanzer]

I want to average numbers in twelve non-contiguous ranges of cells that contain positive, negative, and zero values. Prior to calculating the average, I want to change the negative numbers to absolute values (positive numbers), and ignore the cells with zero values.

I first tried this formula, and a few other approaches, with no success:

=AVERAGEIF(ABS(C9:C39),ABS(C42:C70),ABS(C73:C103),ABS(C106:C135),ABS(C138:C168),ABS(C171:C200),ABS(C203:C233),ABS(C236:C266),ABS(C269:C298),ABS(C301:C331),ABS(C334:C363),ABS(C366:C396),"<>0")

Any suggestions how to accomplish this?

 

[RoryA]

A gentle reminder:

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:
Formula to average multiple ranges containing negative numbers and zeros

There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Lanzer]

I've always understood cross-posting as posting the same thing in more than one discussion thread on the SAME discussion forum website. I never do that.

 

[Peter_SSs]

I've always understood cross-posting as posting the same thing in more than one discussion thread on the SAME discussion forum website.

That is referred to as "duplicate posting" (see rule #12). If you followed (as requested) the link at the end of the rule you have been referred to (#13), you would see a good explanation about cross-posting and the issues surrounding it.

 

[Lanzer]

OK then, well we're making some progress over at the other website forum. Calling it a day now, and will be back at it in the morning.

 

[Lanzer]

Problem solved. Three formula approaches all returned the correct result:

1) =SUMPRODUCT(ABS(($C$8:$C$400))*($C$8:$C$400<>0)*(ISNUMBER($A$8:$A$400)))/COUNTIFS($C$8:$C$400,"<>",$C$8:$C$400,"<>0",$A$8:$A$400,">0")

2) {=SUM(ABS((C8:C400))*ISNUMBER($A8:$A400))/SUM((C8:C400<>0)*ISNUMBER($A8:$A400))} Ctrl+Shft+Enter for Array Formula

3) {=AVERAGE(IF(ISNUMBER($A8:$A400)*(C8:C400<>0),ABS(C8:C400),""))} Ctrl+Shft+Enter for Array Formula

Apparently Excel does not have a function combo able to handle this using just the "C" column numbers (my original approach using AVERAGEIF and ABS), and instead the "A" column number correlation reference was required.

 

[Lanzer]

Option #3 revised to handle blank cells:

{=AVERAGE(IF(ISNUMBER($A8:$A400)*(C8:C400<>0)*(C8:C400<>""),ABS(C8:C400),""))} Ctrl+Shft+Enter for Array Formula