EXCEL Formula for percentage of...

Closed
Ivan Petkov Posts 4 Registration date Tuesday October 8, 2013 Status Member Last seen October 8, 2013 - Oct 8, 2013 at 08:03 AM
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 - Oct 8, 2013 at 11:51 AM
Hi there,
I'm having tough time with this one and I would really appreciate help.
My 'F' column contains different values including 0 (zeroes). I need a formula that will calculate the % of values that are different than 0 (zero). Thank you very much.

3 replies

TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
Oct 8, 2013 at 11:13 AM
Hi Ivan Petkov,

Try this:
=COUNTIF(F1:F4,0)/COUNTA(F1:F4)
Change the format of the cell to percentage.

But since you said that you have a dynamic range, a little bit of code could be useful.

Right-click on the sheets tab and select "view code". Paste the following code in the big white field:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lRow As Integer
If Intersect(Target, Columns("F:F")) Is Nothing Then Exit Sub
lRow = Range("F1").End(xlDown).Row
ActiveWorkbook.Names.Add Name:="MyRange", RefersTo:="=$F$1:$F$" & lRow
End Sub
Now use the following formula and you won't have to adjust the range anymore:
=COUNTIF(MyRange,0)/COUNTA(MyRange)

Hope you like.

Best regards,
Trowa
1
Ivan Petkov Posts 4 Registration date Tuesday October 8, 2013 Status Member Last seen October 8, 2013
Oct 8, 2013 at 11:32 AM
Thank you very much. This one works, but it calculates the percentage of the zeros. How can I change it to calculate the percentage of the ones that are not 0 ?
0
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
Oct 8, 2013 at 11:51 AM
That's easier then you might think. Add "1-" in front of the formula.
0
Mazzaropi Posts 1978 Registration date Monday August 16, 2010 Status Contributor Last seen November 18, 2022 146
Oct 8, 2013 at 08:25 AM
Ivan Petkov, Good morning.

You can use this one:
=IF(F1=0,"",YOUR % FORMULA)

Is this what you desire?
Tell us if it worked for you.

Greetings from Brasil.
Best regards.
0
Ivan Petkov Posts 4 Registration date Tuesday October 8, 2013 Status Member Last seen October 8, 2013
Oct 8, 2013 at 08:39 AM
I'm still not sure how to do it. Here's a better picture:

F1 - 0
F2 - 0
F3 - 35
F4 - 65
and so on (I'm currently at F129 and everyday I add 10 to 20 values, so I need the fomula to calculate what is the percentage of all the values in coloumn 'F' which are different than zero. In this scenario it would be 50% (2 out of for). Sorry if my explanation is bad. Thank you one more time.
0