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

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

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

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

"KellyKulpa" wrote:

> 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

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





In article <[email protected]>,
"KellyKulpa" <[email protected]> wrote:

> 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

Thanks David. I tried it and the result was 0.57143

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

"David Hepner" wrote:

>
> =SUM(A1:A5)/COUNT(A1:A5)
>
> Format cell as a fraction
> Format>Cell>NumberTab>Fraction
>
> "KellyKulpa" wrote:
>
> > 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

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" wrote:

> 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
>
>
>
>
>
> In article <[email protected]>,
> "KellyKulpa" <[email protected]> wrote:
>
> > 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
>

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)


In article <[email protected]>,
"KellyKulpa" <[email protected]> wrote:

> 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.

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???



In article <[email protected]>,
"KellyKulpa" <[email protected]> wrote:

> Thanks David. I tried it and the result was 0.57143
>
> Do you have a formula for something like 5/10 ?

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" wrote:

> 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)
>
>
> In article <[email protected]>,
> "KellyKulpa" <[email protected]> wrote:
>
> > 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.
>

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


In article <[email protected]>,
"KellyKulpa" <[email protected]> wrote:

> 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