Average If type of function

**quikrich3** · 05-10-2011, 07:11 PM

Alright. I'm using Excel 2003 and as far as i know it doesn't have an AVERAGEIF function. But i could use some help getting an average on certain cells if another cell meets a certain criteria?

For example:
I have a list of drillers in column B with the corresponding well# in column C and corresponding Pump Test Gallons Per Minute in column D.
I have the driller i want to see the averages for in H4.

so i need all of the pump test GPM's averaged if the drillers in column B = H4

So i thought an AVERAGEIF function might work.
I've tried something along the lines of =AVERAGE(IF(B4:B212=H4,D4:D212)) but somethings not right.

Any help would be much appreciated thanks.

**Cutter** · 05-10-2011, 07:23 PM

That formula will work if you confirm it with Ctrl+Shift+Enter instead of just Enter.

**quikrich3** · 05-11-2011, 10:34 AM

I did CTRL + SHIFT + ENTER and it does an average, but its not correct.

**Cutter** · 05-11-2011, 12:33 PM

Post a small sample file showing what you want - manually enter expected results and note the logic behind how those results are obtained.

**quikrich3** · 05-11-2011, 06:40 PM

Here is that sample file. Thanks.

**day92** · 05-11-2011, 06:47 PM

Use this function and drag down...

=AVERAGEIF(B:B,H4,D:D)

**quikrich3** · 05-11-2011, 06:52 PM

I tried it. But it gives an error because i think averageif got added in excel 2007. I'm using excel 2003. But thanks.

**day92** · 05-11-2011, 07:19 PM

Sorry I overlooked that, try this...

Please Login or Register  to view this content.

Confirm with CTRL Shift Enter

**Cutter** · 05-12-2011, 09:41 AM

Another option (to avoid CSE entry)

=SUMIF(B4:B213,H4,D4:D213)/SUMPRODUCT((B4:B213=H4)*(D4:D213<>""))

**quikrich3** · 05-12-2011, 10:17 AM

Hey thanks! I appreciate it.

Average If type of function

LinkBack

Thread Tools

Rate This Thread

Display

Average If type of function

Re: Average If type of function

Re: Average If type of function

Re: Average If type of function

Re: Average If type of function

Re: Average If type of function

Re: Average If type of function

Re: Average If type of function

Re: Average If type of function

Re: Average If type of function

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions