Need to use array formula average(if( for 2003 instead of averageif( but can't get formula to update relative addresses

[kayaker246]

The post title basically sums up my issue. I have two lists of numbers, A & B. A are basically categories, we'll say all the values are either 1, 2 or 5 (anyone get the joke?). B contains values that need to be averaged. The hitch is that there are basically three averages. Those numbers in column B whose corresponding value in column A is 1 need be averaged together, and the same for B column values corresponding to 2 and 5.

I entered the formula in VBA using HHGRng.Formula = Averageif($blah:$blee, blah, $gobble:$bobble) where blah:blee is the range to test in column A and gobble:bobble is the range to average in column B.

However, I learned - to my dismay - that Averageif isn't supported in XL 2003. The horror! So instead I did HHGRng.FormulaArray = Average(If($blah:$blee=blah,$gobble:$bobble,FALSE)).

Unfortunately, excel doesn't update the relative 'blah' reference for the Array Formula as it does for the averageif function. This is annoying.

My question is this: can I get the 'blah' reference to be adjusted appropriately for each subsequent cell in HHGRange without resorting to a loop function and manually incrementing the row?

Thanks!
'yakker

 

[Jonmo1]

Welcome to the board...

Sorry not sure I follow what you mean by getting blah reference to update


Can you post the actual formulas, instead of these gobbledy gook psudo code formulas?
The formula as it worked correctly in XL2007
And the formula you are trying in XL2003

 

[AlphaFrog]

If you had this in Excel 2007
= Averageif($blah:$blee, blah, $gobble:$bobble)

You could use this in Excel 2003
= Sumif($blah:$blee, blah, $gobble:$bobble) / Countif($blah:$blee, blah)

If you have two criteria to match e.g. 2 and 5
= (Sumif($blah:$blee, 2, $gobble:$bobble) + Sumif($blah:$blee, 5,
$gobble:$bobble)) / (Countif($blah:$blee, 2) + Countif($blah:$blee, 5))

 

[kayaker246]

Haha, sorry.

The formula in '03 (that doesn't update when entered via VBA): {=AVERAGE(IF($R$5:$R$94=R5,$U$5:$U$94,FALSE))}

The formula in '07:
=AVERAGEIF($R$5:$R$94,R5,$U$5:$U$94)

Thanks!

 

[Jonmo1]

That formula works just fine for me in xl2003.

So blah = R5 right?

What do you mean you can't get the R5 reference to update?
Are you putting the formula in multiple cells with a loop or something, and can't get R5 to incriment to R6 (or T5) ?

 

[kayaker246]

Yes, it works fine when entered manually, but entering it in VBA via the FormulaArray command seems to preclude the automatic incrementing of R5's row that I have using averageif() in xl2007. I wanted to know if there's a way around this without looping and incrementing R5's row.

 

[Jonmo1]

Hmmm, interesting...

using .Formula incriments R5 as expected. But of coarse it's not CSE entered
using .FormulaArray does not incriment R5.

Strange.

Think I may have heard similar things before, and it's just a glitch.

not sure of any solution other than looping, or the SUMIF/COUNTIF suggested eariler by AlphaFrog

 

[AlphaFrog]

Or maybe use the .AutoFill method...

    Range("S5").FormulaArray = "=AVERAGE(IF($R$5:$R$94=R5,$U$5:$U$94,FALSE))"
    Range("S5").AutoFill Destination:=Range("S5:S94")

 

[Jonmo1]

Ah, I found a couple resolutions.
http://www.mrexcel.com/forum/showthread.php?t=538511&highlight=.formulaarray

Either use R1C1 reference

or

Put the formulaarray in the first cell, then Fill it Down.