Formula for AVERAGEIF please

[thegreengrocer]

I have a huge amount of data (on the left) from which i would like to extract the averages to the table on the right.

the formula to go in K4 would give me the average of the Buy1 numbers in column B:B, but only if these numbers had the ID of '3' in column A:A. the formula would want to ignore 0 if possible.

many thanks in advance.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Data									Table
2
3	ID	Buy1	Buy2	Buy3	Buy4	Buy5	Buy6			ID	Buy1	Buy2	Buy3	Buy4	Buy5	Buy6
4	3	74	74	71	0	0	0			3
5	3	69	76	75	0	0	0			4
6	3	71	77	69	0	0	0			5
7	4	69	70	72	0	0	0			6
8	4	69	71	0	0	0	0
9	4	71	78	74	0	0	0
10	4	67	75	0	0	0	0
11	5	69	71	77	0	0	0
12	6	73	69	72	0	0	0
13	6	74	75	72	0	0	0
Sheet1

 

[Aladin Akyurek]

I have a huge amount of data (on the left) from which i would like to extract the averages to the table on the right.

the formula to go in K4 would give me the average of the Buy1 numbers in column B:B, but only if these numbers had the ID of '3' in column A:A. the formula would want to ignore 0 if possible.

many thanks in advance.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Data									Table
2
3	ID	Buy1	Buy2	Buy3	Buy4	Buy5	Buy6			ID	Buy1	Buy2	Buy3	Buy4	Buy5	Buy6
4	3	74	74	71	0	0	0			3
5	3	69	76	75	0	0	0			4
6	3	71	77	69	0	0	0			5
7	4	69	70	72	0	0	0			6
8	4	69	71	0	0	0	0
9	4	71	78	74	0	0	0
10	4	67	75	0	0	0	0
11	5	69	71	77	0	0	0
12	6	73	69	72	0	0	0
13	6	74	75	72	0	0	0
Sheet1

If you are on Excel 2007 or later...

K4, copy across and down:

=AVERAGEIFS(
   INDEX($B$4:$G$13,0,MATCH(K$3,$B$3:$G$3,0)),
   INDEX($B$4:$G$13,0,MATCH(K$3,$B$3:$G$3,0)),">0",
   $A$3:$A$13,$J4)

On all systems...

Control+shift+enter, not just enter, copy across, and down:

=AVERAGE(
    IF($A$3:$A$13=$J4,
    IF(ISNUMBER(INDEX($B$4:$G$13,0,MATCH(K$3,$B$3:$G$3,0))),
    IF(INDEX($B$4:$G$13,0,MATCH(K$3,$B$3:$G$3,0)) > 0,
      INDEX($B$4:$G$13,0,MATCH(K$3,$B$3:$G$3,0))))))

 

[thegreengrocer]

hi, many thanks for your response, whilst i am most appreciative i have to tell you that formula is just returning the average of 71.111 which is the average for the whole column. the answer in K4 should return 71.333. i am using excel 2000 if this affects things, sorry to be a pain but is there any chance you could check the formula please as we appear to be nearly there? thank's once again.

 

[Aladin Akyurek]

hi, many thanks for your response, whilst i am most appreciative i have to tell you that formula is just returning the average of 71.111 which is the average for the whole column. the answer in K4 should return 71.333. i am using excel 2000 if this affects things, sorry to be a pain but is there any chance you could check the formula please as we appear to be nearly there? thank's once again.

Oops. A mistake in range spec... It should be:

=AVERAGE(IF($A$4:$A$13=$J4, 
   IF(ISNUMBER(INDEX($B$4:$G$13,0,MATCH(K$3,$B$3:$G$3,0))),
   IF(INDEX($B$4:$G$13,0,MATCH(K$3,$B$3:$G$3,0)) > 0,
    INDEX($B$4:$G$13,0,MATCH(K$3,$B$3:$G$3,0))))))

Another way of expressing the same calc would be...

=AVERAGE(
    IF($A$4:$A$13=J4,
    IF($B$3:$G$3=K$3,
    IF(ISNUMBER($B$4:$G$13),
    IF($B$4:$G$13 > 0,
      $B$4:$G$13)))))

 

[thegreengrocer]

thank you very much for your time thats works a treat now. just as a matter of interest where a zero occurs in the left table the formula returns #DIV/0!, is there any way to make it return a zero or blank in this instance. no probs if not, thanks again, very pleased.

 

[Aladin Akyurek]

thank you very much for your time thats works a treat now.

You are welcome.

just as a matter of interest where a zero occurs in the left table the formula returns #DIV/0!, is there any way to make it return a zero or blank in this instance. no probs if not, thanks again, very pleased.

Control+shift+enter, not just enter:

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,
   AVERAGE(IF($A$4:$A$13=$J4, 
    IF(ISNUMBER(INDEX($B$4:$G$13,0,MATCH(K$3,$B$3:$G$3,0))),
    IF(INDEX($B$4:$G$13,0,MATCH(K$3,$B$3:$G$3,0)) > 0,
     INDEX($B$4:$G$13,0,MATCH(K$3,$B$3:$G$3,0))))))))

This will return a 0 in lieu of the #DIV/0! error.

 

[thegreengrocer]

many thanks, very grateful to be able to take advantage of your skills. kind regards thegreengrocer.

 

[Aladin Akyurek]

many thanks, very grateful to be able to take advantage of your skills. kind regards thegreengrocer.

You are welcome. Thanks for providing feedback.

 

[thegreengrocer]

hi again, added more rows of data to the table on the left this morning, then went into your formula to try to expand the cell range (instead of 13 rows it now has 2000). i simply tried changing all the 13's within the formula to 2000 but this will not work. as the formula reverts to just giving the average of the whole column rather than the average for just a given ID. would you mind at all suggesting which numbers in the formula to change in order to expand the cell range to 2000+ rows? thanks once again.

 

[Aladin Akyurek]

hi again, added more rows of data to the table on the left this morning, then went into your formula to try to expand the cell range (instead of 13 rows it now has 2000). i simply tried changing all the 13's within the formula to 2000 but this will not work. as the formula reverts to just giving the average of the whole column rather than the average for just a given ID. would you mind at all suggesting which numbers in the formula to change in order to expand the cell range to 2000+ rows? thanks once again.

Control+shift+enter, not just enter...

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,
   AVERAGE(IF($A$4:$A$2000=$J4, 
    IF(ISNUMBER(INDEX($B$4:$G$2000,0,MATCH(K$3,$B$3:$G$3,0))),
    IF(INDEX($B$4:$G$2000,0,MATCH(K$3,$B$3:$G$3,0)) > 0,
     INDEX($B$4:$G$2000,0,MATCH(K$3,$B$3:$G$3,0))))))))

Or we can the refences to the data area dynamic, such that the formula always works with the actual data...

Define BigNum as referring to:

=9.99999999999999E+307

Define Lrow by means of Insert | Name | Define as referring to:

=MATCH(BigNum,Sheet1!$A:$A)

Note that the data is assumed to be on Sheet1. Adjust to suit if needed.

Define Data as referring to:

=Sheet1!$A$3:INDEX(Sheet1!$G:$G,Lrow)

Note that this defines the data area from column A to G. If the data also change in horizontal direction, we need to tweak the foregoing definition accordingly.

Now, we can modify the essential formula...

Control+shift+enter, not just enter...

=LOOKUP(BigNum,CHOOSE({1,2},0, AVERAGE(IF(INDEX(Data,0,1)=$J4, 
   IF(ISNUMBER(INDEX(Data,0,MATCH(K$3,INDEX(Data,1,0),0))), 
   IF(INDEX(Data,0,MATCH(K$3,INDEX(Data,1,0),0)) > 0, 
    INDEX(Data,0,MATCH(K$3,INDEX(Data,1,0),0))))))))