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 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Oct 8, 2013 at 11:51 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Oct 8, 2013 at 11:51 AM
Related:
- EXCEL Formula for percentage of...
- Date formula in excel dd/mm/yyyy - Guide
- Number to words in excel formula - Guide
- Excel grade formula - Guide
- Logitech formula vibration feedback wheel driver - Download - Drivers
- Kernel for excel - Download - Backup and recovery
3 responses
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Oct 8, 2013 at 11:13 AM
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:
=COUNTIF(MyRange,0)/COUNTA(MyRange)
Hope you like.
Best regards,
Trowa
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)Now use the following formula and you won't have to adjust the range anymore:
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
=COUNTIF(MyRange,0)/COUNTA(MyRange)
Hope you like.
Best regards,
Trowa
Mazzaropi
Posts
1985
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 24, 2023
147
Oct 8, 2013 at 08:25 AM
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.
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.
Ivan Petkov
Posts
4
Registration date
Tuesday October 8, 2013
Status
Member
Last seen
October 8, 2013
Oct 8, 2013 at 08:39 AM
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.
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.
Oct 8, 2013 at 11:32 AM
Oct 8, 2013 at 11:51 AM