I need a formula to calculate the ratio of #'s in a row and column

[Guest]

Hello. I'm using Excel 2000 and need help finding a formula to calculate the
ratio in columns and rows.

For example: I'm using 1's and 0's ( 1 if the item is present and 0 if it is
not present). So, I need a formula that will show 8/10 of the items were
present. See my example below.

Column C
1
0
1
0
1
=3 (sum)
=3/5 (ratio)

Thanks for your help!!!! Kelly

 

[Guest]

=SUM(A1:A5)/COUNT(A1:A5)

Format cell as a fraction
Format>Cell>NumberTab>Fraction

 

[JE McGimpsey]

You say you want 8/10, but your example shows 3/5, or 6/10...

One way:

=SUM(A1:A5)/COUNT(A1:A5)

If you want to always see 10ths, use Format/Cells/Number/Custom ?/10

 

[Guest]

Thanks David. I tried it and the result was 0.57143

Do you have a formula for something like 5/10 ?

 

[Guest]

Each of my spreadheets are different. Some have 15 rows and some have 5.
Therefore, if there's 15 rows I need to caculate how many out of 15 items
were present. If there are only 5 rows on my spreadsheet I would need the
same but out of 5.

 

[JE McGimpsey]

If there's nothing in the column other than your data:

=SUM(C:C)/COUNT(C:C)

or, since your data is 1s and 0s:

=AVERAGE(C:C)

 

[JE McGimpsey]

Did you try David's suggestion to format the cell as a fraction, which
will return

4/7

??

Or are you trying to round 0.57143 to 5/10???

 

[Guest]

I tried this and it's still giving me 0.5333 when I need it to show 5 out of
10 like this: 5/10
I appreciate your help!

 

[JE McGimpsey]

That's because you're still not formatting the cell as a fraction as I
explained in my previous post.