Excel/tabular to frequency
Hey Tom, Happy New Year.
I have an interesting data set that i want to transpose? to a matrix
Company1 ... -1
Company1 .. -5
Company1 .. 150
Company870 .. -6
Company870 .. -5
Company870 .. 50
I was wondering could you suggest a method that i could create a frequency or count matrix.
I have attached an image of what i am trying to achieve.
I have tried to count the data but it ends up counting the frequency only but i want to be able to display across the page in a second sheet.
The data is mainly dispersed between -15 and 15 but there are outliers so frequency for <-15 and >15 would work a treat
ps Its been a while i know and you have been extremely helpful in the past. Im just not sure how to attack this problem.
If you want to show even the outliers, then you would select both columns and create a pivot table.
You would have the company in the row field and the second column, the number, in both the column field and as the data. In the data, you would specify Count rather than Sum as the accumulator.
If you want to do the <-15 think, then I would use a formula in column C with an if formula
then drag fill this down the column C. Then build the pivot table using all three columns, but column C would be used in the column field and as the data.
If you are not familiar with pivot tables, you can send me some sample data such as you show and I can set it up or I can use formulas if you wish.
send to firstname.lastname@example.org
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here