EXCEL Formula for percentage of...

Closed
Report
Posts
4
Registration date
Tuesday October 8, 2013
Status
Member
Last seen
October 8, 2013
-
Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
-
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

Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
490
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
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Posts
4
Registration date
Tuesday October 8, 2013
Status
Member
Last seen
October 8, 2013

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 ?
Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
490
That's easier then you might think. Add "1-" in front of the formula.
Posts
1947
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
November 11, 2021
147
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.
Posts
4
Registration date
Tuesday October 8, 2013
Status
Member
Last seen
October 8, 2013

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.