You are here:

Excel/tabular to frequency

Advertisement


Question
Freq
Freq  
Hey Tom, Happy New Year.
I have an interesting data set that i want to transpose? to a matrix

Sample data

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

Any suggestions

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.

Cheers

Paul

Answer
Paul,

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

=if(B2<=-15,-15,if(B2>=15,15,B2))

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 twogilvy@msn.com

--
Regards,
Tom Ogilvy

About Excel
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

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.