Conditional Average

E

Excel_Learner

can anybody please help me out?
I have a worksheet with below mentioned details:

State Qty
A 50
A 60
A
B 70
B 65

Now i need average of every state.
Please help.
 
M

Mike H

Try,


=SUMIF(A2:A6,"=A",B2:B6)/COUNTIF(A2:A6,"=A")

Change the A to the state you want.

Mike
 
N

Niek Otten

Data>Subtotals, in Use Function choose Average

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| can anybody please help me out?
| I have a worksheet with below mentioned details:
|
| State Qty
| A 50
| A 60
| A
| B 70
| B 65
|
| Now i need average of every state.
| Please help.
 
E

Excel_Learner

Thaks for your quick reply Mike. But the result will be 110/3. while result
shuld be 110/2 since a cell is blank.
 
M

Max

Then you probably need a conditional average:

Try this, array-entered* in say, C2:
=AVERAGE(IF((A2:A6="A")*(B2:B6<>""),B2:B6))

*Press CTRL+SHIFT+ENTER to enter the formula, instead of just pressing ENTER
 
E

Excel_Learner

Thank you all for your precious time. Mike's trick did my work. Thank to Mike
again.
 
Top