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).

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.

--
Biff
Microsoft Excel MVP


"Sandy" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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
>

Yes, but all the rows below the countif figure are blank so surely the
answers should be the same.
Sandy

"T. Valko" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> The difference is that both formulas are using different sized ranges.
>
>>$A$29 is "=Countif(A31:A3000,A2).
>
> 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.
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "Sandy" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> 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
>>
>
>

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)

"Sandy" <(E-Mail Removed)> wrote in message
news:u%(E-Mail Removed)...
> Yes, but all the rows below the countif figure are blank so surely the
> answers should be the same.
> Sandy
>
> "T. Valko" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> The difference is that both formulas are using different sized ranges.
>>
>>>$A$29 is "=Countif(A31:A3000,A2).
>>
>> 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.
>>
>> --
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "Sandy" <(E-Mail Removed)> wrote in message
>> news:%(E-Mail Removed)...
>>> 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
>>>
>>
>>
>
>

"T. Valko" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> The difference is that both formulas are using different sized ranges.

both are different? I thought only one was different <g>.

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))

In article <u#(E-Mail Removed)>,
"Sandy" <(E-Mail Removed)> wrote:

> Yes, but all the rows below the countif figure are blank so surely the
> answers should be the same.

Ok, then what results are you getting from both formulas?

--
Biff
Microsoft Excel MVP


"Sandy" <(E-Mail Removed)> wrote in message
news:u%(E-Mail Removed)...
> Yes, but all the rows below the countif figure are blank so surely the
> answers should be the same.
> Sandy
>
> "T. Valko" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> The difference is that both formulas are using different sized ranges.
>>
>>>$A$29 is "=Countif(A31:A3000,A2).
>>
>> 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.
>>
>> --
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "Sandy" <(E-Mail Removed)> wrote in message
>> news:%(E-Mail Removed)...
>>> 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
>>>
>>
>>
>
>

Argh! <bg>

--
Biff
Microsoft Excel MVP


"Bob Phillips" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
>
> "T. Valko" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>>
>> The difference is that both formulas are using different sized ranges.
>
> both are different? I thought only one was different <g>.
>

Yes that does the trick - Thanks once again
Sandy

"JE McGimpsey" <(E-Mail Removed)> wrote in message
news:jemcgimpsey-(E-Mail Removed)...
> 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))
>
> In article <u#(E-Mail Removed)>,
> "Sandy" <(E-Mail Removed)> wrote:
>
>> Yes, but all the rows below the countif figure are blank so surely the
>> answers should be the same.