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

"Excel_Learner" wrote:

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.

DataSubtotals, in Use Function choose Average

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Excel_Learner" wrote in message
...
| 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.

"Mike H" wrote:

Try,


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

Change the A to the state you want.

Mike

"Excel_Learner" wrote:

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.

ok,

=SUMIF(A2:A6,"=A",B2:B6)/SUMPRODUCT((A2:A6="A")*(B2:B60))

mIKE

"Excel_Learner" wrote:

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

"Mike H" wrote:

Try,


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

Change the A to the state you want.

Mike

"Excel_Learner" wrote:

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.

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Excel_Learner" wrote:
Thaks for your quick reply Mike. But the result will be 110/3. while result
shuld be 110/2 since a cell is blank.

"Max" wrote in message
...
Then you probably need a conditional average:

You mean, as he stated in the subject? <vbg

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

"Mike H" wrote:

ok,

=SUMIF(A2:A6,"=A",B2:B6)/SUMPRODUCT((A2:A6="A")*(B2:B60))

mIKE

"Excel_Learner" wrote:

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

"Mike H" wrote:

Try,


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

Change the A to the state you want.

Mike

"Excel_Learner" wrote:

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.

Then you probably need a conditional average:
You mean, as he stated in the subject? <vbg

Aha <g, think I missed out in the earlier descript: "additional condition"
Then you probably need an additional condition in the conditional average
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---