Using named range in AVERAGEIFS formula

[imgonna]

Hi all


Happy New Year all or as we say in Wales, Blwyddyn Newydd Dda.


I have a 2 named ranges:


SCORE which is defined as StudentData!$F$13:$F$300

SEX which is defined as = StudentData!$C$13:$C$300


On a different sheet in the same workbook I want to use this range in an AVERAGEIFS formula.


If I code using non named ranges, the following formula works fine and dandy:


AVERAGEIFS(StudentData!$F$13:$F$300,StudentData!C13:C300,"M"), to average when sex = M


But if I use the named ranges it fails with #Value!


Now, if I just Avergae(Score) it works fine.


What am I missing or is this a function of the AVERAGEIFS formula?


Cheers

Glen

 

[imgonna]

Hi (again)


Curious...


The =AVERAGEIF(Sex,"m",Score) formula works fine (I know the syntax is different)....


mmMMMMM


G

 

[Hui]

Using Averageifs() the formula should be: =AVERAGEIFS(Score,Sex,"M")

Using Averageif() the formula should be: =AVERAGEIF(Sex,"M",Score)

Both work for me


Check that the Named Ranges both have the correct format of

Sex: =StudentData!$C$13:$C$300

Score: =StudentData!$F$13:$F$300

 

[imgonna]

Hi Hui


Yea I know -- I have the syntax correct and still AVERAGEIFS chucks a Value! error when I use the named ranges.


Fortunatley I can use AVERAGEIF as I don't need multiple criteria in this one, but prefer to use IFS as its more general.......


Cheers

G

 

[imgonna]

Genius


Hui, for some reason known only to my mouse, the named ranges where not the same and some $$ had gone missing.


Odd error -- but thanks for the pointer.


Classic.


G