Repair formula that show winning percentage of a variable?

1. Repair formula that show winning percentage of a variable?

I use this formula to calculate the winning percentage of a certain variable , the total number is 21 with 7 wins and 12 loses and two 0s, the result 7/21 should be 33,33%, but is showing 36,84%. How can i repair the formula?

" =COUNTIF(sheet1!Q4:Q60;">0")/COUNTIF(sheet1!Q4:Q60;"<>0") "  Register To Reply

2. Re: Repair formula that show winning percentage of a variable?

Q4:Q60 is not 21 rows? Perhaps you have other values inside that range that you are not looking at?  Register To Reply

3. Re: Repair formula that show winning percentage of a variable?

Hi,
The only rows filled are Q4:Q24 the rest is blank , i have two 0s that might be affecting the result.  Register To Reply

4. Re: Repair formula that show winning percentage of a variable?

Try changing the ranges to just what you need, because yes, those 2 0's are more than likely being included, although that should reduce the %, not increase it.

7/23 = 30.4%  Register To Reply

5. Re: Repair formula that show winning percentage of a variable?

I have tested the formula without 0s and it worked showing 33,33%. Do i need a new formula to ignore the 0s or maybe its possible to modify this one?  Register To Reply

6. Re: Repair formula that show winning percentage of a variable?

I just re-ran your calc from post 1.

=COUNTIF(sheet1!Q4:Q60;">0")/COUNTIF(sheet1!Q4:Q60;"<>0")
is completely ignoring the 0 (ties?), so you are only counting a max of 19 entries, not 21
7/19 = 36.8%

=COUNTIF(sheet1!Q4:Q60;">0")/COUNTa(sheet1!Q4:Q60)
Although, I would still prefer you used the actyal ranges.  Register To Reply

7. Re: Repair formula that show winning percentage of a variable?

The total range is Q4:Q60 but only Q4:Q24 is filled, the formula is including the blank cells, is showing 12,28% as result.  Register To Reply

8. Re: Repair formula that show winning percentage of a variable?

What formula did you use?
Is there a formula in those "blank" cells - ie where do the results come from?
Will there always be 21 entries?
Can you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.  Register To Reply

9. Re: Repair formula that show winning percentage of a variable?

I use this formula "=COUNTIF(sheet1!Q4:Q60;">0")/COUNTa(sheet1!Q4:Q60)" , The original formula was intended to count only the positive number from range Q4:Q60 but the 0s are affecting the result, so i just want to ignore the 0s, Q4:Q60 have a formula that calculate the numbers positive or negative. So i just want the formula to ignore the 0s and also the cells without a number.  Register To Reply

10. Re: Repair formula that show winning percentage of a variable?

This is a workbook sample.  Register To Reply

11. Re: Repair formula that show winning percentage of a variable?

And sorry for the late reply.  Register To Reply

12. Re: Repair formula that show winning percentage of a variable?

1. You have 8 >0 not 7
2. excluding blanks/zeros, you have a total of 19, not 21
3. 8/19 = 42.1%
=COUNTIF(F4:F24,">0")/COUNTIF(F4:F24,"<>0")

OR if you want to exclude zero from ONLY numerator and NOT the denominator (which I believe is incorrect)...
8/21 = 38.1%  Register To Reply

13. Re: Repair formula that show winning percentage of a variable?

I counted 7 >0 from G4:G24, and the result 7/21 should be 33,33%.  Register To Reply

14. Re: Repair formula that show winning percentage of a variable?

There are zeroes in two of the cells (G13 and G23) conditionally formatted not to appear. Remove them and you will get the result you require.  Register To Reply

15. Re: Repair formula that show winning percentage of a variable?

I will need the 0s, its possible to ignore the 0s, and count only numbers above 0, so that the result 7/21 is 33,33?  Register To Reply