Link to home
Start Free TrialLog in
Avatar of Maliki Hassani
Maliki HassaniFlag for United States of America

asked on

Excel Charts to display summary and average in charts


So I have another question concerning my  dashboard  This is related to creating a list box that will populate with the total count and average of EACH selected ID#.  I have setup the dashboards with a listbox but just need an expert's guidance on what is the best way to approach this.

Thank you!   NOC-Reports-r18.xlsm
Avatar of dlmille
Flag of United States of America image

Please elaborate a bit more.  what is count and what to average - where is the data, etc.?  Share an example, please.  Where is the output going?  Is that in the spreadsheet already?

Avatar of Maliki Hassani


No problem..  I was assuming you would ask for more details.  So this request is due to a question at work today, where a manager wanted to see the total amount of DAC tickets created -C4.  I was able to show the weeks and values but not the total sum or the average of those weeks.  So basically,each chart currently shows weeks and the value of the weeks.  Example: week 48 = 200, week 49 = 205 etc.  I am looking to add to the dashboard a place to include the sum and average of each line item that is being selected.  The output right now is setup to display above the larger chart for each sheet in the dashboard.    Let me know what you think?
Correction: below the chart is where I have a listbox setup..
Other E-E's may be able to help in the interim.  I might have a chance to look at this tonite, otherwise, late tomorrow afternoon.  I have a big workshop to facilitate and some modeling due - gotta do REAL work, lol


Ha Ha..   No problem!  I am working on other dashboard stuff.  No rush...  :)
In Executive Rollup Data, I see no data for Week 48... (Column DR is blank)... In Surveillance, I'm having a hard time seeing how you come up wtih 200 - is that average or count????  still confusing.  You say you want to sum each line item - so that line item would be a series of weeks, based on the timeline, so youor clarifying comments are still confusing.  But, there's an out - there's some work you can do to get you half-way there in one step.

1st step - for each of the datasets you want count and averages on, go to the datasheets and create a column with exactly that for each of the items that could be selected.  Make one for each time period, just like you made the chart series for each time period.  In fact, you can use those ranges to do your operation on.  E.g., countif(rangereference, > 0) could give you the count.  sumif(rangereference)/countif(rangereference,>0) would give you the average.  Once you have that put together, post your spreadsheet.

2nd step - if you get this far, the next step would be to create a table or some type of box to put your metrics in - so think about how you want the info displayed.

3rd step - after the chart updates, it needs to fire a subroutine to update that info.

Get to step 1 and think about how you want the info displayed, then we can chat if/when you get stuck from there.

Thanks I will work on that..  By the way the 200 and week 48 was used as an example.  This was populating the list box to help give you an idea of what I was planning on doing..  I will work on this tomorrow.  Later!
Newest Version Is attached..  I havn't made any changes yet in reference to this question..  I was working on the "Monthly dashboard".  Will get to it later tonight.
let me know when you're ready to proceed.

Hi Dave:  Man, it has been a long but productive week.  Hope work went well...  
So here are my updates:
- Added scrolling features to the listboxes1. (couldn't get the rest of the boxes to scroll) :(
- Charts automatically show trend lines
- Made small charts dynamically pullin with what managers requested
- Monthly tab includes 4 reports that I run.  (Need to change the tab name to 30/60/90 since some reports are not monthly but 90 days.
- Fixed error handling issues

Now for the purpose of this post.  Yesterday, I tried to figure out what is the best way to display the total counts and averages without bring headaches to the both of us..  When you look at the attached spreadsheet you will see that "Executive Rollup Data"  now has several column names added (Far right).  3 groups: 1-Counts, 2-Averages, 3-Combo of both 1 and 2. Column 3 is the bread and butter of the listbox3 in the Executive dashboard.  Listbox3 displays all of the headers like ID#, 1yr, 6m, 3m, 1m, and the values of each going down.  The listboxes still has the functionality to select and chart is updated too.  

So here is the issue.  I created SAMPLE data (numbers) to show this concept.  However, I couldn't seem to get the offset formula to work with a count and the average.  If you could please take a look and provide me with a working formula I will be able to duplicate this on all sheets.  I mean I tried yesterday and I just couldn't get it it work.  :(  I have an engagement dinner to go to later but will definetly keep in contact if you get a chance to review this further.  Thanks Dave!
What other boxes are you trying to scroll (sheet and which one)?

Where can I find the sample data that shows what the output looks like?

ok - I think I found it in your table...

ok - give the basics here.  What is your DEFINITION of COUNT.  Explain cell JK7.  Also for Average -cell JR7.  I'm missing something or we're speaking different lingo.  I think of COUNT as the number of items - e.g., counting # of weeks that have data, etc.  You obviously see COUNT as something different and I can't seem to reverse engineer your calculus...

Module2 contains the main script and each disboard has the call out at top. Trying to get listbox 2 and 3 to scroll aswell.
Module2 contains the main script and each disboard has the call out at top. Trying to get listbox 2 and 3 to scroll aswell.
Don't worry about my made up numbers I just through something in the there.

Ok count is the total amount for 1 month, or 3 months, 6 months.

Example. Let's make up fake values.  Week1 = 50 week 2 = 70, week 3= 50. so the count would be 170 and average =57
PS I modified to make run MUCH faster - actually appears instantaneous:

Sub unhideall()

Dim WS As Worksheet
Application.ScreenUpdating = False
    For Each WS In Worksheets
        WS.Visible = True
Application.ScreenUpdating = True
End Sub

PS - why are you hooking mouse hover over the listbox?  The functionality of doing multiple selections and scrolling seem to be messed up with this.  What are you trying to do?

Can't really answer that.. Code came like that... I researched.
I'll look at your count/average now.  If you would, share with me what you were trying to do with that listbox.  If you can't answer that, why are you using the code? :)

I just wanted to have the ability to scroll in the listboxes. Using the the mouse instead of the listbox slider. :)
I just wanted to have the ability to scroll in the listboxes. Using the the mouse instead of the listbox slider. :)
Ok - updated for COUNT and AVERAGE.

When you have a cell displaying a range, the way to use that range in a formula is with the INDIRECT function.  Average = SUM / COUNT - you might want to put an IF on the count to ensure no DIV!#0 errors!

Sa-weet!  I will take a look later.. Thanks..
Sa-weet!  I will take a look later.. Thanks..
Your listboxes DO scroll.  Still not sure why you're hooking in the mouse.  Let me backtrack to the posted question I didn't respond as others had.

Ok - I found the question and know what you're trying to do - scroll with mouse wheel.  Let me review what you have...

:)  Really jazzing it up!
Yes the scroll with the mouse..  Gotta try to impress the big boys.. lol
Wow, I never even heard of indirect in a formula.  Man you have some cool tricks.. Looks great!
Now, i am not sure why listboxes 1 is not allowing me to scroll , I think I will use the old script.  Checking out the ListBox1_DBLClick now...
OMG, I never realized that the double click to clear was there all along.  I am such a goof!
you have to turn off the mouse hook stuff for the double click to work with ListBox1.  I never got the wheelmouse to work and stopped looking at other options (I was about 80%) when you said you got it to work no problem... lol
and the below code looks wierd - VK_UP for both???

            If wParam = WM_MOUSEWHEEL Then
                LowLevelMouseProc = True
                If lParam.mouseData > 0 Then
                    PostMessage _
                    lListBoxhwnd, WM_KEYDOWN, VK_UP, 0
                    PostMessage _
                    lListBoxhwnd, WM_KEYUP, VK_UP, 0
If you still have problems, here's a link that makes more sense (using rotation of wheel mouse):

I guess I am not sure what the mouse hook means?  Is that where you can see the selection move down as you mouse scroll down?
It is weird because with the new version you provided only the executive dashboard has a scrolling issue the rest of the spreadsheets are fine.
I am just going to use version 21 and make the adjustments to unhideall and the formulas (indirect). Everything else should be good to go. Any new ideas for the dashboard?
What are your thoughts on how the dashboard is turning out?
Looking good, Lance.
I was able to get v22 to work by destroying LB1 and recreating it.  The double click works just fine to delete selection... Will work on your next query coming, re: doing the other listboxes with wheel control...

Avatar of dlmille
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I'm ready for the next post :)


Great stuff!  Very pleased with everything.  Just curious how hard would it be to only show the data in listboxes3 for only the specific ID# that is being graphed?  I am thinking that showing all the ID data at once is too much and would be better under the graph, showing only what is selected to be viewed.  If this is headache buster then we can move on.  
Here is the attached file with some template mods. NOC-Reports-r23.xlsm
More thoughts on this:  Then that would mean that the buttons for 1w, 1m, etc would have to update the Count/Avg.  Perhaps this is ian dea that should be thrown away.. lol
Let me think on this.  Are you posting a query on getting the other Listboxes to work with mouse wheel?

I have a v.23 you're going to want :)
On your question with the count/avg.  What data would you want to plot - count or average?  No changes to option buttons would be necessary, I don't think, as that just sets the date range for the plot.  All you need to do is create the DATA for that date range (all you've done at this point is display COUNT / AVERAGE for a date range - that already aggregates it).  What data would you plot?  If you know that, you have to create that series in your datasheet, and then have pointers to it (ranges for 1 year, 6 months, 3 months, etc.).  Then, you call the chart just like any other, except with that range.

Got it?

The data that I would want to plot is not determined by the count/avg.  The count/avg is just an add on to the selection of the desired chart a person wants to display.  
Ok - what data would you want to plot, if someone were to select on that listbox?

Would it be the same as ListBox1??
Basically, someone clicks on example E2 and clicks on the "Update Chart" button.  We will have an update chart and a listbox that will show what ever the count and average for what is being shown in the chart.  They click on 1 month button, now we have an updated chart and a listbox that shows the data for the 4 weeks. etc
I guess that's what its already set up to do.  Perhaps I don't understand the question?
When I read back to your post, your idea is to select something to plot (if a single item), then listbox3 displays that data table?

Right now we have listboxe3 showing ALL of the executive ID's with count and averages for 6 columns (1 month , 2 month etc). I don't want to see all that info.  Only display such info based on what is being graphed, and what button is being selected.
So the table in ListBox 3 has only the data range that is selected off the main chart object buttons, but for all the ID's?
Just like it is now, but 2 columns - ID, and Data (but only for the date range currently being plotted)?
Yes, that is correct..  

I just changed my idea which would include multiple selections if the case, and only display the count/ averages based on what weeks are shown in the graph.
Yes, and only the ID's that are being used to plot the graph at that time.
I will be moving list boxes 3 some where near the main chart and making it transparent.  This will allow more dashboard space for other info..  How hard is this task?
not hard at all.  check your email.

oh.. lol