• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 348
  • Last Modified:

Excel 2010: Making a top 10 callers chart

I need to make a chart in excel that will show the top 10 callers. One side would be how many times they called, and the other would be who they are. anyone have any ideas?
0
bachopper
Asked:
bachopper
3 Solutions
 
Glenn RayExcel VBA DeveloperCommented:
Use a Pivot Table to summarize the data and then turn on a filter to see the top ten results.  See this example.
 EE-Caller-Summary.xlsx
0
 
jppintoCommented:
You can do it with a Pivot Table and displaying only the Top 10, like on the attached file.

jppinto
Book1.xlsx
0
 
barry houdiniCommented:
Here's a formula based solution - see attached

Sample formula to get the top callers from a list of Callers in Calls!A2:A500

=INDEX(Calls!A$2:A$500,MODE(IF(Calls!A$2:A$500<>"",IF(ISNA(MATCH(Calls!A$2:A$500,B$1:B1,0)),MATCH(Calls!A$2:A$500,Calls!A$2:A$500,0)))))

confirmed with CTRL+SHIFT+ENTER and copied down

regards, barry

regards, barry
Callers.xlsx
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
bachopperAuthor Commented:
@barryhoudini:
ok, I'm stuck on the bold part. What is this supposed to be referencing?

=INDEX(Calls!A$2:A$500,MODE(IF(Calls!A$2:A$500<>"",IF(ISNA(MATCH(Calls!A$2:A$500,B$1:B1,0)),MATCH(Calls!A$2:A$500,Calls!A$2:A$500,0)))))

Here is the translated version so far. Is this correct?:
=INDEX(Table2[Site],MODE(IF(Table2[Site]<>"",IF(ISNA(MATCH(Table2[Site],B1:B1,0)),MATCH(Table2[Site],Table2[Site],0)))))
0
 
ScriptAddictCommented:
Not for points:

ISNA()  - this means that if this formula would normally give the #NA error in the cell.

He's doing a basic error check to make sure the match is there, otherwise just setting it to 0.

0
 
bachopperAuthor Commented:
Thanks guys, including ScriptAddict =)

Ended up using a pivot table. Was able to create the table and the chart I needed and the boss man is happy. =)
0
 
barry houdiniCommented:
The point of the ISNA part is to exclude names from the cells above. It isn't technically required to get the top name so this would suffice in B2

=INDEX(Calls!A$2:A$500,MODE(IF(Calls!A$2:A$500<>"",MATCH(Calls!A$2:A$500,Calls!A$2:A$500,0))))

MATCH is used to get the row number of the first match for each entry. The most common number (found by MODE function) is also the position of the most common name, INDEX gets the name from that number.

...but in the next cell down, B3, I don't want to find the most common name again.....I want to find the most common name excluding those in the cells above....so I use MATCH to see whether the names match the cells above, if the result is #N/A then I do want to consider those names, because they haven't yet appeared on the list, ISNA isolates those names. It's possible to use COUNTIF too, i.e. this version in B2 copied down

=INDEX(Calls!A$2:A$500,MODE(IF(Calls!A$2:A$500<>"",IF(COUNTIF(B$1:B1,Calls!A$2:A$500)=0,MATCH(Calls!A$2:A$500,Calls!A$2:A$500,0)))))

Of course the formulas are referring also to B1 - the contents of B1 doesn't matter....as long as it isn't one of the names in the list

regards, barry
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now