Formula to calculate most common occuring cell value

[jayneinaccounts]

Hi

I have downloaded our phone bill into a .csv file. I now would like to analyse it, and find out the most commonly called number.

Does anyone know which formula I would need to do this?

Thanks for looking

 

[Chantty]

Try pivot table

Drag Telephone no. in row filed
Drag Telephone no. in data filed, then u will count of telphone nos' that may help you for analysis.

 

[Richard Schollar]

Hi Jayne

The easiest way to do this would probably be to construct a pivot table from the data using the Telephone number as both the row field and the data field and set the data field to summarise by Count. Then you will immediately see how many times each number has been called.

 

[jayneinaccounts]

Thanks guys! First time I have used pivot table! Its now got me thinking! My pivot table has a list of numbers in column A and the number of times each number has been called in column B. Could I add in two further columns from my data for total duration of calls and total costs? If so, where would I need to drag those fields to? left, top or data section?

Thanks for your help!

 

[Wolfshead]

Hi
You could try the MODE function on your worksheet - it should tell you your most frequently occurring entry

Wolfshead

 

[Richard Schollar]

Jane

Drag them into the Data section, make sure they are summarised by Sum (rather than Count). To make them appear in adjacent columns, once you have dragged them into the pivot table, drag the Data button on the Pivot Table over the Total heading in the pivot table.