# 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") "

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?

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.

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%

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?

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.

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.

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.

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.

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

This is a workbook sample.

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

And sorry for the late 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%

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

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.

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?

Page 1 of 2 1 2 Last

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1