Help with Formula
 

Hello all,

I have an Excel sheet with state and county data. Column C indicates which
state and Column D indicates which county in that state from Column D.

I want to write a formula for percentile (Percentile()) and standard
deviation (STDEVP()) to determine percentile and standard deviation for any
state or county that I choose. Since there are more than one state and one
county in the sheet, I don't know how to write those formula.

In Excel, there are formula like sumif() and countif() if I want to sum a
specific state or county or count the records of specific state or county. I
do not find similar formula to do the percentile and standard deviation.

Please help.

Thanks.

Help with Formula
 

You can use a formula like
=VAR(IF(C1:C20="CA",D1:D20,""))

Remember to use CTRL-SHIFT-ENTER after typing the formula...
-------------------------------------
Pl. click ''''Yes'''' if this was helpful...



"AccessHelp" wrote:

Hello all,

I have an Excel sheet with state and county data. Column C indicates which
state and Column D indicates which county in that state from Column D.

I want to write a formula for percentile (Percentile()) and standard
deviation (STDEVP()) to determine percentile and standard deviation for any
state or county that I choose. Since there are more than one state and one
county in the sheet, I don't know how to write those formula.

In Excel, there are formula like sumif() and countif() if I want to sum a
specific state or county or count the records of specific state or county. I
do not find similar formula to do the percentile and standard deviation.

Please help.

Thanks.

Help with Formula
 

Hi Sheeloo,

Thanks for the formula. I tried it, and I got the #Value! error. Just
curious, if it works, is it going to turn out the way percentile and stdevp?

Thanks.

"Sheeloo" wrote:

You can use a formula like
=VAR(IF(C1:C20="CA",D1:D20,""))

Remember to use CTRL-SHIFT-ENTER after typing the formula...
-------------------------------------
Pl. click ''''Yes'''' if this was helpful...



"AccessHelp" wrote:

Hello all,

I have an Excel sheet with state and county data. Column C indicates which
state and Column D indicates which county in that state from Column D.

I want to write a formula for percentile (Percentile()) and standard
deviation (STDEVP()) to determine percentile and standard deviation for any
state or county that I choose. Since there are more than one state and one
county in the sheet, I don't know how to write those formula.

In Excel, there are formula like sumif() and countif() if I want to sum a
specific state or county or count the records of specific state or county. I
do not find similar formula to do the percentile and standard deviation.

Please help.

Thanks.

Help with Formula
 

Did you use CTRL-SHIFT-ENTER?
If you just press ENTER it won't work...
Also I gave you Variance formula showing how you can use condition to
restrict the values passed to it...
You still need to use PERCENTILE or STDEVP instead of VAR...

-------------------------------------
Pl. click ''''Yes'''' if this was helpful...



"AccessHelp" wrote:

Hi Sheeloo,

Thanks for the formula. I tried it, and I got the #Value! error. Just
curious, if it works, is it going to turn out the way percentile and stdevp?

Thanks.

"Sheeloo" wrote:

You can use a formula like
=VAR(IF(C1:C20="CA",D1:D20,""))

Remember to use CTRL-SHIFT-ENTER after typing the formula...
-------------------------------------
Pl. click ''''Yes'''' if this was helpful...



"AccessHelp" wrote:

Hello all,

I have an Excel sheet with state and county data. Column C indicates which
state and Column D indicates which county in that state from Column D.

I want to write a formula for percentile (Percentile()) and standard
deviation (STDEVP()) to determine percentile and standard deviation for any
state or county that I choose. Since there are more than one state and one
county in the sheet, I don't know how to write those formula.

In Excel, there are formula like sumif() and countif() if I want to sum a
specific state or county or count the records of specific state or county. I
do not find similar formula to do the percentile and standard deviation.

Please help.

Thanks.

Help with Formula
 

Good morning Sheeloo,

You are right. I forgot to do the CTRL-SHIFT-ENTER. No wonder it didn't
work. Now it works on percentile and standard deviation.

You are the best! I have definiately clicked Yes on the rating.

Thanks for your helps!

"Sheeloo" wrote:

Did you use CTRL-SHIFT-ENTER?
If you just press ENTER it won't work...
Also I gave you Variance formula showing how you can use condition to
restrict the values passed to it...
You still need to use PERCENTILE or STDEVP instead of VAR...

-------------------------------------
Pl. click ''''Yes'''' if this was helpful...



"AccessHelp" wrote:

Hi Sheeloo,

Thanks for the formula. I tried it, and I got the #Value! error. Just
curious, if it works, is it going to turn out the way percentile and stdevp?

Thanks.

"Sheeloo" wrote:

You can use a formula like
=VAR(IF(C1:C20="CA",D1:D20,""))

Remember to use CTRL-SHIFT-ENTER after typing the formula...
-------------------------------------
Pl. click ''''Yes'''' if this was helpful...



"AccessHelp" wrote:

Hello all,

I have an Excel sheet with state and county data. Column C indicates which
state and Column D indicates which county in that state from Column D.

I want to write a formula for percentile (Percentile()) and standard
deviation (STDEVP()) to determine percentile and standard deviation for any
state or county that I choose. Since there are more than one state and one
county in the sheet, I don't know how to write those formula.

In Excel, there are formula like sumif() and countif() if I want to sum a
specific state or county or count the records of specific state or county. I
do not find similar formula to do the percentile and standard deviation.

Please help.

Thanks.