VBA code for AVERAGEIF

jim may

Well-known Member

Joined

Jul 4, 2004

Messages

7,486

• Sep 20, 2009

• #2

Posting a Sample Worksheet of data with expected UDF result(s) would be helpful

 

Upvote 0

L

Lester07

New Member

Joined

Aug 4, 2009

Messages

29

• Sep 20, 2009

• #3

Hey Jim thanks for the response. I'm working with Excel 2003 and as far as I know, AVERAGEIF is only available in 2007. hope this example helps... Thanks..

Excel Workbook
	A	B	C	D	E
1	XXX	Score		Rep	XXX
2	Rep1	XXX		XXX	91.25%
3	Rep1	95.00%		Rep2	70.00%
4	Rep1	70.00%		Rep2	80.00%
5	Rep1	100.00%
6	Rep2	80.00%		XXX	XXX
7	Rep2	0.00%
8	Rep2	XXX		XXX
9	XXX	100.00%	XXX		XXX
10	XXX	100.00%
11	XXX	50.00%
12	Rep3	75.00%	XXX		XXX
13	Rep3	95.00%
Sheet1

 

Upvote 0

L

Lester07

New Member

Joined

Aug 4, 2009

Messages

29

• Sep 20, 2009

• #4

Sorry for that one...

A1=Reps, A9=Rep2, A10:A11=Rep3, B2=100%, D2=Rep1, D4=Rep3, E1=Average

 

Upvote 0

Norie

Well-known Member

Joined

Apr 28, 2004

Messages

76,377

Office Version

1. 365

Platform

1. Windows

• Sep 20, 2009

• #5

Couldn't you just use a pivot table?

 

Upvote 0

L

Lester07

New Member

Joined

Aug 4, 2009

Messages

29

• Sep 20, 2009

• #6

Sorry I can't, I'll be putting the results in different cells

 

Upvote 0

Norie

Well-known Member

Joined

Apr 28, 2004

Messages

76,377

Office Version

1. 365

Platform

1. Windows

• Sep 20, 2009

• #7

And a pivot table won't do that?

 

Upvote 0

jim may

Well-known Member

Joined

Jul 4, 2004

Messages

7,486

• Sep 20, 2009

• #8

Probably a Better way but try:

=SUMPRODUCT(--($A$2:$A$13=D2),--(ISNUMBER($B$2:$B$13)),$B$2:$B$13)/COUNTIF($A$2:$A$13,D2)

 

Upvote 0

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• Sep 20, 2009

• #9

Care to explain those XXX's for it seems an XXX stands for a rep, a score, and even for a header as displayed in A1 of your exhibit? Are the values in D:E desired calculations?

 

Upvote 0

jim may

Well-known Member

Joined

Jul 4, 2004

Messages

7,486

• Sep 20, 2009

• #10

Lester, It's better to post the corrected Sheet data again, versus using your Text edit technique.

 

Upvote 0