+ Reply to Thread
Results 1 to 11 of 11

Creating a chart with only top 10 in a series

  1. #1
    Registered User
    Join Date
    11-02-2012
    Location
    Kentucky
    MS-Off Ver
    Excel 2007
    Posts
    8

    Creating a chart with only top 10 in a series

    I would like to create a chart where the chart only shows the top 10 items in the series. Currently, I am doing it "manually" where I determine the top 10 results, but I am sure there is a different way.

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Surrey, UK
    Posts
    5,077

    Re: Creating a chart with only top 10 in a series

    you could try a pivot table/pivot chart -where you can select the top ten

    perhaps a sample of your data and the result you are trying to achieve would help us
    Wayne

    if my assistance has helped, and only if you wish to , there is a reputation icon * on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Registered User
    Join Date
    11-02-2012
    Location
    Kentucky
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Creating a chart with only top 10 in a series

    Example of Top 5.xlsx

    This is the data I was given. I need to have a daily chart of the top 5 issues. (the ones with the greatest value as the numbers represent minutes) I also need to do a chart for the top 10 issues for the week. I would like to make it to where I do not have to choose the top 5 or 10 each day/week.

  4. #4
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Surrey, UK
    Posts
    5,077

    Re: Creating a chart with only top 10 in a series

    i'm sure someone will give a better solution,
    but with the way your data is structured , I would simply sort it horizontally

    so select B2:W166
    Sort>custom> options > left to right
    and then choose
    row166
    and Value
    High to low
    or
    low to high
    now you have your top5 and top10
    and can graph

    also the graph will be in descending or ascending order

    see attached - took a few minutes to do

    you could copy the data and transpose and then you would be able to create top10 and top5 charts using a pivot table- but that would probably take longer than just doing the sorting.

    i transposed the data into a new sheet and then created a top5 and top10 chart
    see attached sheet with top10 and top5 charts

    Now anyupdate to the transdata sheet could be quickly and simply refreshed on those two pivot chart sheets


    depends on the process you have for importing the information into excel and if you could have a couple of tables that you update and then the charts would automatically be created
    Attached Files Attached Files
    Last edited by etaf; 08-07-2013 at 12:25 PM.

  5. #5
    Registered User
    Join Date
    11-02-2012
    Location
    Kentucky
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Creating a chart with only top 10 in a series

    I would have to "sort" this everyday doing it that way, right? What I need is to be able to enter data each day on the corresponding row and chart what the top 5 issues are daily and weekly. I hope that I am articulating well what I need. Have a hard time putting it into words sometimes. Should I set up the data entered differently?

  6. #6
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Surrey, UK
    Posts
    5,077

    Re: Creating a chart with only top 10 in a series

    yes see my edit on my post #4

    you may need a macro to extract the data

    you are physically typing this information into your table - is that correct ?

    how do you show daily and weekly
    next update on your sample would be 6/8/13 which is tuesday

    so you want a graph for tuesday
    how would you now define the weekly chart - do you wait till the end of the week is completed ?
    Last edited by etaf; 08-07-2013 at 12:47 PM.

  7. #7
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Surrey, UK
    Posts
    5,077

    Re: Creating a chart with only top 10 in a series

    OK
    so i had a play
    and not doing this by using a macro

    in trandata sheet - i have simply transposed you data
    and now you enter the data in the column rather than the row

    in the row however, you increment the number so that my formulas can pick up the latest day that you have completed
    this will now transfer the daily information across to the new sheet
    trandata (2) in the daily column

    the weekly also uses the number on row1 ofthe trandata sheet and then goes back 7 columns and adds up those numbers - so its always a running 7 days and not a week

    now the sheet top5-10 will show a pivot table and chart - for the top10 items (and therefore the top5 also )
    for weekly and for daily data

    we could have separate sheets for
    top5 daily
    top5 weekly
    top10 daily
    top10 weekly

    so in theory
    you update the column in trandata
    you add the next number in row 1 of that column
    and then the sheet trandata (2) automatically updates

    you goto the sheet top5-10
    goto pivot options and refresh
    now the weekly and daily data is updated and so is the chart

    anyway probably not what you want

    but it least a start

    see attached

    i'm sure someone will come up with some VBA/Macro to do this in much more robust and nicer way
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    11-02-2012
    Location
    Kentucky
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Creating a chart with only top 10 in a series

    Thank you. I was hoping it wouldn't be so difficult and involved. I will give this a try and see if it is what they are wanting. If not, they'll just have to do it manually. Which, if you think about it, is not all that difficult to begin with. I apprecitate you taking the time to help!

  9. #9
    Registered User
    Join Date
    11-02-2012
    Location
    Kentucky
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Creating a chart with only top 10 in a series

    Thank you. I was hoping it wouldn't be so difficult and involved. I will give this a try and see if it is what they are wanting. If not, they'll just have to do it manually. Which, if you think about it, is not all that difficult to begin with. I appreciate you taking the time to help!

  10. #10
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Surrey, UK
    Posts
    5,077

    Re: Creating a chart with only top 10 in a series

    well , not much to do here

    just enter the data - which they had to do anyway
    then add one more entry in row 1 a number
    then goto a sheet and hit the refresh button

    so thats just 2 steps - add a number and refresh - a lot less that sorting and selecting the range and drawing a graph

  11. #11
    Forum Expert GeneralDisarray's Avatar
    Join Date
    09-15-2011
    Location
    Pittsburgh, PA, USA
    MS-Off Ver
    Windows Excel 2007/2010/2013
    Posts
    1,416

    Re: Creating a chart with only top 10 in a series

    What's this data rolled up from? Can you access the raw data (assuming there is in this case such data)?

    Seems like you could pivot the data to show a count and use built in excel filters to see the top 5, top 10, etc. (described here:http://www.contextures.com/excel-piv...p10.html#Using). You could set up a pivot chart for the day or week..

    Also, for weekly report you could assign each record another field for the weeknum (assuming there is raw data) by adding a column to use this formula: =weeknum(date_field) THat would give you the option to pivot out by Week.


    --
    If this data is being rolled up for you from some other set, and you can post that set I can show you what I'm talking about. :D
    Last edited by GeneralDisarray; 08-07-2013 at 02:33 PM.
    Remember, saying thanks only takes a second or two. Click the star icon(*) below the post you liked, to give some Rep if you think an answer deserves it.

    Please,mark your thread [SOLVED] if you received your answer.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1