# 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