Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Percents
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Percents
=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) "Colchabay" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Percents
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:A6553670),--(B1:B65536="M"))/COUNTIF(B1:B65536,"M") What percentage of all entries are Males over 70 =SUMPRODUCT(--(A1:A6553670),--(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 -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Colchabay" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Percents
It comes up 0 when I type this formula in a cell below the column.
Judy "Bob Phillips" wrote: =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) "Colchabay" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Percents
Works fine for me. What is the data like?
-- HTH Bob Phillips (remove nothere from email address if mailing direct) "Colchabay" wrote in message ... It comes up 0 when I type this formula in a cell below the column. Judy "Bob Phillips" wrote: =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) "Colchabay" wrote in message ... 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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Percents
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 -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Colchabay" wrote in message ... It comes up 0 when I type this formula in a cell below the column. Judy "Bob Phillips" wrote: =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) "Colchabay" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula - Percents | Excel Discussion (Misc queries) | |||
Hyperlink problem with percents | Excel Discussion (Misc queries) | |||
I need to know formula for matching retirement percents | Excel Worksheet Functions |