• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calculate and display percent occurence from a column of numbers

J

jay pugh

New Member
I am using Excel 2007 and have a column of numbers that I want to calculate and display the percent of occurrences of each number. I guess I need to have a count of the occurrences of each number and then a total divided by numbers in column. I want to display 107 is 1.2%, 108 is 2.5 %, etc. Can someone give me a way to do this in Excel 2007?
 
Welcome to Chandoo.org forums.

With cell formatted as percentage, insert following formula:
Code: 
=COUNTIF($A$1:$A$200,$A1)/COUNT($A$1:$A$200)
where A1:A200 is total range and A1 is the range for which we are testing occurrence and then copy down.
 
You can also use a pivot table:
  1. Select your data (including a header at the top)
  2. Go to the "Insert"-tab in the ribbon
  3. Select PivotTable
  4. In the "PivotTable Field List"-window (on the right side), place the field with your data in the "Row Labels"-box
  5. Also place the field with your data in the "Values"-box
  6. Select the value in the "Values"-box and go to the "Field Settings" (either in the menu you get or via the ribbon).
  7. Choose "Count" in the "Summarize value field by"-tab and press OK
  8. Now select "% of Column Total" via the ribbon: "PivotTable Tools" > "Options" > "Show Values as"
  9. ...
  10. Profit!
This assures that you only see a consolidated list of your data.
(PS: These steps are in Excel 2010)
 
Hi Jay ,

Your latest post refers to the solution given by Xiq ; what about the solution given by Shrivallabha ?

Is your problem resolved or no ?

If no , can you upload a sample workbook ?

Narayan
 
Shrivallabha post works I just needed to manually copy the formula to only one row of the number as it occurs not copy it in every cell in the column beside the actual number.
I uploaded the file with a pivot table that does not work.
 

Attachments

Hi Jay,
Looking at your sample file, I noticed you missed a few steps in the PivotTable solution. See the uploaded file how it's setup.

A few notes on what you missed:
  • Instead of "% of Row Total", you should have used "% of Column Total"
  • In the "PivotTable Field List"-window, you need to have the field "Product Temp..." in both "Row Labels"- and "Values"-box
I hope this will help.
 

Attachments

You must log in or register to reply here.
Back
Top