AVERAGEIFS in 2003

[sdsurzh]

Hi,


How to use AVERAGEIFS formula in 2003 excel.


Thanks,

Suresh Kumar s

 

[shrivallabha]

Hi Suresh,


You've not posted a sample data that resembles your requirement.


Suppose in A1:A10 following data is kept:

1

2

3

4

5

6

7

8

9

10


And you want to check the Average if the value is greater than 2 and less than 9 so the AVERAGEIFS formula would be:

=AVERAGEIFS(A1:A10,A1:A10,">"&2,A1:A10,"<"&9)


In 2003 [CTRL + SHIFT + ENTER] this formula,

=SUM(IF(A1:A10>2,IF(A1:A10<9,A1:A10)))/SUM(IF(A1:A10>2,IF(A1:A10<9,1)))

 

[Hui]

or without using an Array Formula

=Sumproduct((A1:A10>2)*(A1:A10<9)*(A1:A10))/Sumproduct((A1:A10>2)*(A1:A10<9))

 

[NARAYANK991]

Hi Shrivallabha ,


Doesn't the following work in Excel 2003 ?


=AVERAGE(IF(A1:A10>2,IF(A1:A10<9,A1:A10)))


entered as an array formula , using CTRL SHIFT ENTER.


If it does , shouldn't it be used instead of the SUM(IF(..)) construct ?


Ref. : http://en.allexperts.com/q/Excel-1059/2008/12/Excel-2003-convert-Averageifs.htm


Narayan

 

[sdsurzh]

Hi Excel Heros,


Can you please look the same file:


http://www.2shared.com/file/KXzHN3t5/Book3.html


Thanks,

Suresh Kumar s

 

[NARAYANK991]

Hi Suresh ,


Enter the following array formula , using CTRL SHIFT ENTER , in B3 :


=AVERAGE(IF($G$1:$G$11=$A4,IF($H$1:$H$11=B$1,IF($I$1:$I$11=$A$2,$J$1:$J$11))))


Copy it across and down.


Narayan

 

[sdsurzh]

Hi Narayan,


Thanks Narayan.


Even i tried the same forumla but the J column i made it in the different place.


Regards,

Suresh Kumar S

 

[sdsurzh]

Thanks Shrivallabha & Hui for your kind help

 

[shrivallabha]

Hi Narayan,


Yes, it is much better construct than what I've posted as it removes the need to rework the criteria array. It should be used. However for smaller data the calculation speed won't be affected.


So suresh can use with the layout in his last post in Cell B3:


Narayan's approach (Array Entered):

=AVERAGE(IF($G$1:$G$11=$A3,IF($H$1:$H$11=B$1,IF($I$1:$I$11=$A$2,$J$1:$J$11))))


Shrivallabha's approach (Array Entered):

=SUM(IF($G$1:$G$11=$A3,IF($H$1:$H$11=B$1,IF($I$1:$I$11=$A$2,$J$1:$J$11))))/SUM(IF($G$1:$G$11=$A3,IF($H$1:$H$11=B$1,IF($I$1:$I$11=$A$2,1))))


Hui's approach (Normal Entered):

=SUMPRODUCT(($G$2:$G$11=$A3)*($H$2:$H$11=B$1)*($I$2:$I$11=$A$2)*$J$2:$J$11)/SUMPRODUCT(($G$2:$G$11=$A3)*($H$2:$H$11=B$1)*($I$2:$I$11=$A$2))


In my opinion Narayan's and Hui's constructs are better from formula "maintenance" perspective.

 

[sdsurzh]

Thanks shrivallabha for your detailed clarification.