Calculating the percentage of a row

  • Thread starter Thread starter Carol
  • Start date Start date
C

Carol

I have a profile spreadsheet with one column
headed "Male/Female". Each row contains either a "M"
or "F".

What formula can I use to calculate the percentage of "F"
in the entire spreadsheet as well as the percentage of "M"
in the spreadsheet?

example: M
M
F
M
F
 
Try this: =COUNTIF(A:A,B1)/COUNTA(A:A)

Column A = data
B1 = input field (m or f)
This formula will only work correctly if you only enter m or f in your data
column.If you enter any other letter or number it will not calculate the
percentage correctly.

HTH.
 
=+COUNTIF(B3:B200,"M")/COUNTIF(B3:B200,"<>")


Use that formula for M (replace M with F for F), and change the range
b3:b200 to whatever range contains the M and F. It doesn't matter if
the range is *more* than you have data (e.g. the range could be
B3:b50000)-- the formula looks at the number of Ms and divides by the
total # of "people" (rows) that exist there.
 
Anders, why the -1 in there? That gives the wrong result, and gives a
DIV#/0 when there is only 1 occurence of "F" or "M" in that range.

Dave
 
Dave,

The -1 is to account for the header row, so that it is not included in the
calculation.

Anders
 
Back
Top