Conditional Average

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
Try,


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

Change the A to the state you want.

Mike
 
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.
 
Thaks for your quick reply Mike. But the result will be 110/3. while result
shuld be 110/2 since a cell is blank.
 
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
 
Back
Top