Percents

[Guest]

I need to know how the formula to find the percent of a column. For example.
What percent of the numbers in column A are above 70. OR What percent of
column B (Gender) are Male.

Also, need to find the percent of what percent of Males (column B) are
above 70 (column A). Refering to 2 columns?

Thanks.

 

[Bob Phillips]

=COUNTIF(A:A,">70")/COUNTA(A:A)

=COUNTIF(A:A,"Male")/COUNTA(A:A)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Bernard Liengme]

1) What percentage of numbers in A are greater than 70:
=COUNTIF(A:A,">70")/COUNT(A:A) and format the cell with %,
or =100*COUNTIF(A:A,">70")/COUNT(A:A)
If you mean 70 or more (i.e 70 is be counted)
=COUNTIF(A:A,">=70")/COUNT(A:A)
If you have a definite range for the numbers
=COUNTIF(A5:A105,">70")/COUNT(A5:A105)
2) Gender
=COUNTIF(B:B,"M")/COUNTA(B:B) (do note the A in CountA here)
3) combined, what percentage of the Males are over 70
=SUMPRODUCT(--(A1:A65536>70),--(B1:B65536="M"))/COUNTIF(B1:B65536,"M")
What percentage of all entries are Males over 70
=SUMPRODUCT(--(A1:A65536>70),--(B1:B65536="M"))/COUNT(A1:A65536)
Cannot use entire column (A:A) with Sumproduct
For info on SUMPRODUCT see
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
http://mcgimpsey.com/excel/formulae/doubleneg.html

best wishes

 

[Guest]

It comes up 0 when I type this formula in a cell below the column.

Judy

 

[Bob Phillips]

Works fine for me. What is the data like?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Bernard Liengme]

When you see A:A is means the whole column
SO if you put that formula in A you get a circular reference.
Put the formula somewhere else or change to =COUNTIF(A1:A100,">70")
but use your range in place of A1:A100
best wishes