I have a spreadsheet where I need to create a formula that will show a ratio.

Example
E2 = Y
E3 = N
E4 = Y
E5 = Y
F2 needs to show the percentage of those 4 cells

Do you want a ration or a percentage? Try this in F2 first:

="Ratio is "&COUNTIF(E2:E5,"Y")&" yes to "&COUNTIF(E2:E5,"N")&" no
responses"

Or you could try this:

=COUNTIF(E2:E5,"N")/COUNTA(E2:E5)

and format as percentage - should give you 25%.

Hope this helps.

Pete

Cindy wrote:

I have a spreadsheet where I need to create a formula that will show a ratio.

Example
E2 = Y
E3 = N
E4 = Y
E5 = Y
F2 needs to show the percentage of those 4 cells

The percentage of the 4 cells is 100%, if the set considered contains those
four elements.

If you're looking for the percentage of Ys, then do something like
=COUNTIF(E2:E5,"Yes")/COUNTA(E2:E5) and format as percent.

Dave
--
Brevity is the soul of wit.


"Cindy" wrote:

I have a spreadsheet where I need to create a formula that will show a ratio.

Example
E2 = Y
E3 = N
E4 = Y
E5 = Y
F2 needs to show the percentage of those 4 cells

There are probably neater solutions but it can be done with 2 helper cells

In helper cell1 (say g1) type =COUNTIF(E2:E5,"=y")
In helper cell2 (say g2) type =COUNTIF(E2:E5,"=n")

in your answer cell f2 type =G1/(SUM(G1,G2))*100&"% Are Yes"
in another cell say f3 type =G2/(SUM(G1,G2))*100&"% Are No"


"Cindy" wrote:

I have a spreadsheet where I need to create a formula that will show a ratio.

Example
E2 = Y
E3 = N
E4 = Y
E5 = Y
F2 needs to show the percentage of those 4 cells