Average (follow on)

  • Thread starter Thread starter Sandy
  • Start date Start date
S

Sandy

Why does this:-

=IF($A$29=0,0,IF($A$2="",0,SUMIF(OFFSET(A31,,,$A$29,1),$A$2,OFFSET(F31,,,$A$29,1))))/$A$29

produce a different result tothis?:-

=IF($A$29=0,0,IF($A$2="",0,SUMIF($A$31:$A$3000,$A$2,F31:F3000)))/$A$29

where $A$2 is "Red" and $A$29 is "=Countif(A31:A3000,A2).

Sandy
 
The difference is that both formulas are using different sized ranges.
$A$29 is "=Countif(A31:A3000,A2).
Click to expand...

Suppose the result of that COUNTIF = 10

The formula that uses OFFSET is only using the range A31:A40 and F31:F40.

The other formula is using the range A31:A3000 and F31:F3000.
 
Yes, but all the rows below the countif figure are blank so surely the
answers should be the same.
Sandy
 
What data gives different values, I get the same?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Only if ALL the values in A31:A{lastrow} = A2.

You might be better off using this array-formula (CTRL-SHIFT-ENTER, or
CMD-RETURN):

=IF($A$29=0,0,AVERAGE(IF($A$31:$A$3000=A2,$F$31:$F$3000))
 
Back
Top