Trying to take an average of numbers in specific cells...

**corty_d** · 11-13-2008, 11:40 AM

What im trying to do is to take an average of a set of numbers that are next to a certain number in the next column.... this is going to be hard to explain

Basically.... in one column i will have either a 0, 1, 2 or 3. and this could be listed down like this (these are priorities):

and what i need to do is to take an average of the value of the cell next to a specific number.... for example:

So i would have a formula for each of the priorities in a cell that would average the numbers to the right of that priority...

So for example... the average of all priority 2's should be 10+12+30/3 = 17.333. ok now for the twist.....

I may have a 0 value in a cell next to a priority... i do not want this to be averaged. This is my main problem. I have been using a combination of SUMIF functions and COUNTIF functions but cant get anything to work. Here is where i have got so far (e.g. for a priority 2):

=SUMIF(A1:A9,"=2",B1:B9)

- This seems fine.... this is summing the amounts next to a priority... its the next bit thats tricky... i now need to divide this by the number of cells with a value in it (>0) next to that priority (2)

I can do the

COUNTIF(B1:B9,">0")

but i only need the values next to 2. Can anyone help?

**Cheeky Charlie** · 11-13-2008, 11:52 AM

=sumif(a1:a10,"=2",b1:b10)/sumproduct((a1:a10=2)*(b1:b10<>0))

hth

**Ron Coderre** · 11-13-2008, 11:57 AM

With A1:B11 containing this list:

Priority	Value
2		5
3		6
1		16
1		8
2		10
2		12
0		15
3		3
2		30
0		9

and...
D1: Priority
D2: 0
D3: 1
D4: 2
D5: 3

E1: Average

Then...this formula returns the average for Priority 0 items

E2: =SUMIF($A$2:$A$11,D2,$B$2:$B$11)/COUNTIF($A$2:$A$11,D2)

Copy that formula down through E5

..OR...you could use a Pivot Table to automatically build the table.
Still using the above example...(Column heading must exist):
From the Excel Main Menu: <Data><Pivot Table>
Use: Excel……Click [Next]
Select your data…(A1:B11)…Click [Next]
Click the [Layout] button

ROW: Drag the Priority field here
COLUMN: (leave this area blank)
DATA: Drag the Value field here
If it doesn't list as Average of Value...dbl-click it and set it to Average
Click [OK]
Select where you want the Pivot Table…Click [Finish].

That will list each Priority and the Average Value.

Does that help?
To refresh the Pivot Table, just right click it and select Refresh Data

**Cheeky Charlie** · 11-13-2008, 11:59 AM

I may have a 0 value in a cell next to a priority... i do not want this to be averaged

Ron, I missed this first time through - am I right in thinking you did too?

**corty_d** · 11-13-2008, 12:01 PM

yeh... thats my problem guys... is it possible?

**Ron Coderre** · 11-13-2008, 12:25 PM

Yes....I surely missed the "skip zeros" critria, too.

Ok....Using the same example I posted
Try this formula

E2: =SUMPRODUCT(($A$2:$A$11=D2)*($B$2:$B$11>0)*$B$2:$B$11)/
SUMPRODUCT(($A$2:$A$11=D2)*($B$2:$B$11>0))

or...this shorter ARRAY FORMULA...BUT you need to remember to commit
it with CTRL+SHIFT+ENTER (instead of just ENTER)

E2: =AVERAGE(IF(($A$2:$A$11=D2)*($B$2:$B$11>0),$B$2:$B$11))

Copy the formula down through E5.

Does that help?

**corty_d** · 11-13-2008, 12:32 PM

ok the first formula works well

thats great. What do you mean by commit? what does ctrl+shift+del do?

**Ron Coderre** · 11-13-2008, 12:38 PM

It's CTRL+SHIFT+ENTER

Regarding:

E2: =AVERAGE(IF(($A$2:$A$11=D2)*($B$2:$B$11>0),$B$2:$B$11))

To make Excel treat the referenced cells as proper arrays,
after editing the cell you must:
• Hold down CTRL and SHIFT when you press ENTER

If you just commit the formula with ENTER..it will return incorrect values.
That's one of the pitfalls of ARRAY FORMULAS.

I hope that helps.