Color the Bars – II (2000)

Topic

New Reply

Hi there,

I had a posting, Color the Bars (59938 on 2 Aug) where the goal was to have the colors of bars in a chart depend on the data point’s values. This was for some teachers I had done a class for. So, for example, students with grades from 91-100 should have blue bars (for example), 81-90 would be green, 71-80 would be tan, and 70 and below would be red.

Only response that I got was from Hans Pottel (thanks) but that opened a few more questions. See the various postings on that thread.

However, something that Hans suggested made something tick in my head that actually provided a solution using other postings. It’s not elegant and not the best approach (in my opinion) but it does the job.

The original problem assumed that students’ names were column headings (or row headings) and their grades were directly below them in the same row. I’d still like to solve that problem. If anyone has a solution, that would be great and still appreciated.

What clicked in my head was converting the grades from 1 row to (using above) 4 rows – 1 for each range of grades. So each student only has 1 entry below his/her name corresponding to the row with the range in which the grade falls; the rest of that col is 0 or blank. All we have to do is get the grades from the original form (all in 1 row) to this form (actually not too hard). Now the chart wizard picks up the non 0 grades in the same range and picks the same color. Different range, different color.

We then applied another tip from the lounge. The bars were ‘aligned’ within each students’ name on the x axis depending on whch range they fell in (lowest range, to the left; highest range to the right). The 0 (or actually blank) grades were not visible. Playing with the gap width and overlap took care of things.

If anyone can solve the original problem as stated there, let me know. For now, this will have to do.

Fred

Reply | Quote

Replies

Fred

I deal with a lot of school grades and I constantly run into limitations of plotting in XL. I pass problems that I cannot deal with onto a colleague who is a wiz in S+, that is much more powerful and can usually do what I want. My colleague assures me that what you want to do would be straightforward in S+. However S+ is quite costly but there is a much cheaper, possibly freeware or shareware, called R. Perhaps a bit of web searching would be worthwhile.

Reply | Quote

Hi Mike,

Thanks for the ideas. Probably going with S+ or even R is not an option – cost on the one and learning curve on both (learning curve with teachers?). I might look into R for my own benefit.

I think the problem is not hard to solve but beyond my means. If you can, look back on the original thread. I think there were only 3 items there – my original posting, Hans’s posting, and my reply to him. I would think that, given a named range to be used like a lookup table (upper limit of test scores in 1 col and colors in the next; OK have to make sure the colors are valid names like blue and red and not cats and dogs), it would not be hard to loop thru the bars of a chart, find the data point value associated with the bar, find the data point’s associated color from the lookup table, change the bar color (I think I recall a thread where color numbers were derived from names). But I know I can’t do this. My Excel VBA is limited and almost non-existent when it comes to charts.

By the way, your tip on dynamic ranges (about 2-3 weeks ago) helped solve my daughter’s problem on making charts using different sets of cells. I was hoping she’d post the solution but I don’t think she’s even taken my example and applied it to her specific needs. She’s said she’d do it a few times, the latest being just about an hour ago when we spoke.

Fred

Reply | Quote

Fred,

I have been away for a while, not being able to continue our discussion. I don’t know if I follow exactly what you want, so correct me if I misunderstood. Below you can find some code that starts from a range in a spreadsheet (namely A1:B10), containing the values 10, 20, … up to 100 in column A (so from A1 till A10) and in column B, next to e.g. 60, I put the number of students with results between 50 and 60 (not including 60). The code below starts from this data, makes an embedded column chart on Sheet1 and then loops through the columns giving it a different color depending on the value in the B column.
This is maybe not what you want to do, but it must give you an idea of what can be done. Personally, I have written some code for my employer that uses an equivalent histogram as an overall picture of evaluation results of the employees in a certain function class and a colored bar then indicates the individual class for one employee. In this way, every employee received a personalized report of his evaluation, as compared to the large group of function holders to which he/she belonged.

Put some data in range A1:B10 of Sheet1 (more or less equivalent to the data I described) and paste the macro in a module of the VBE. Then run the macro from Tools >> Macros >> Macro >> Macro1 and have a look to the output. Maybe, this can be the basis for the next step.

Sub Macro1()
Dim R As Range
Dim i As Integer
Set R = ActiveSheet.Range("A1:B10")
    Charts.Add
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.SetSourceData Source:=R, PlotBy:= _
        xlColumns
    ActiveChart.SeriesCollection(1).Delete
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
    ActiveChart.HasLegend = False
    ActiveChart.SeriesCollection(1).Select

    For i = 1 To R.Rows.Count
    
    ActiveChart.SeriesCollection(1).Points(i).Select
    
    Select Case R.Columns(2).Cells(i).Value
       Case 0 To 30
         Selection.Interior.ColorIndex = 38
       Case 31 To 60
         Selection.Interior.ColorIndex = 6
       Case 61 To 100
         Selection.Interior.ColorIndex = 15
    End Select
    
    Next i
End Sub

Reply | Quote

An alternate way is to use different series for the different colors. Have the base data in the first column. The second column has just the blue data points. The third has red and so one. Make a bar chart from the 2nd column on. Each series gets a different color. Adjust the bar widths and overlaps so that the bars plot on top of each other. This won’t be a problem since at each data point you have only one column with data in so only one bar will plot.

Reply | Quote

Hans,

Thks for getting back to me on this. Hope the time away was restful.

I am attaching a file here that shows what I’m trying to do. On Sheet1 is a set of cells with student names in row 1, grades in row 2, and a resulting chart. If you look at the VBE, you’ll see a macro that does some of the stuff you wrote about in your reply to the original thread and goes a few steps further. However, that didn’t get me very far and the chart is not what I’m after. At best, what I started doing, with the macro recorder on, was selecting bars and changing their colors. Definitely time consuming in a class of any appreciable size.

If you look at Sheet2, you’ll see a chart that has what I want – color of the bar depending on what range the student’s grade is in. But note that the set of cells with names and grades is expanded from what it was on Sheet1: 3 rows for the ranges of grades, instead of 1, plus the row for the names. It probably would not be much work to create the 3 rows of grade ranges on Sheet2 from the 1 corresponding row on Sheet1 (altho I’ve not given it a lot of thought yet).

Adding in an adjustment of the gap width and overlap gives the 2nd chart on Sheet2. I believe there was a thread on this lounge that adjusted these 2 values to get rid of the 0-height bars corresponding to the 0 values in A1:G4.

I think this is similar to gibbindr’s suggestion.

So what I’d ideally like to do is get the chart on Sheet2 (ideally the 2nd chart but 1st one might do also) with the input on Sheet1.

I liked your approach of creating a chart from your previous response and gave it a whirl. It did not seem to work as I expected it. At the very least, following your code, I expected the case statement to give me bars of different colors. All mine were pink. I’ll attach that in a separate posting. (It seems not only is there a 100K Max on attachments but also a single file limit even if multiple files are less than 100K).

Thanks much.

Fred

Reply | Quote

Hans,

Scratch the attachment in this posting. I realized that my col B data were all less than 30 so that all my bars were the same color bcs of the way you set up the select case statement. I changed a few points and got different colored bars. Thanks.

Given this adjustment, I think your code is very close to where I need to be.

The other significant item might be that the teachers want the bars to be specific colors. So what I mentioned in one of the postings (probably my first one in the original thread) was that they’d create a 2-column range with upper bounds of the grade range in, say, col A, and the corresponding color in col B – kind of like a lookup table. This would allow them to choose the color for each grade range. I realize this might be a pain to use for the ColorIndex statement since the color would have to be valid (“Blue” not “Blew”) and would have to be converted to a number. Alternatively, they could let Excel initially pick the color and then go into the legend to change the color for the corresponding grade range. So they only have to change x colors (number of grade ranges) instead of y colors (number of students). Or they can live with what Excel picks.

Thanks much.

Fred

Reply | Quote

One poster came close to the best way to do this. Multiple columns of data, with either zero or the grade, if it fell within certain bounds. You can put the cutoff for the grade at the top of the column, with if statements below that either return the grade or zero. But then the poster had to fiddle with overlaps and spacings.

If you make this a stacked column chart, instead of a clustered column chart, your bars for each student (all zeroes except for a single non zero bar) align perfectly in a stack, with no need to adjust overalp and spacing, and the zeroes don’t show.

– Jon

Reply | Quote

Jon,

Thks for the thoughts.

Actually, the poster who had to fiddle with overlaps and spacings was me.

gibbindr suggested the solution of multiple rows for the ranges (or cols depending on your orientation) where only 1 row gets filled in per col (where col is the student name). I came up with that at the same time and posted that with the attachment showing the chart before and after fooling with the spacing and overlaps to get the desired affect.

Your suggestion of a stacked column chart helps advance the solution 1 step further – no fooling with overlap and spacing as you stated.

I did notice that the category (X) axis provides a lot of room per student, even I rotate the names to 90 degrees. Is there a way to decrease the amount of room on the axis allocated per student?

Still looking for the ideal solution of the chart per Sheet2 but the input per Sheet1 in my attachment earlier in the thread.

fred

Reply | Quote

Fred,

My code starts from the assumption that the names of the students are in column A and their results in column B. So, only two columns are needed for your data. This is the most optimal arrangement of your data as there will be no empty cells. However, the disadvantage is that there is no way to use the legend in the chart as you actually showed it in your attachment. Excel does not allow to change the LegendEntries, it only allows to change its format. This means that you have to delete the legend if you use this arrangement of your data. An alternative would be to add a textbox to your chart, add another small square textbox in the first textbox, with background color e.g. yellow, and next to that another textbox, with contents ’70-80′ etc. In other words, you make your legend manually. If you record this, step by step, then you obtain a macro making a legend. Using the macro I posted earlier will keep the amount of room on the axis allocated per student to the minimum. I know this is not really the easy way but I don’t know of another way to do it in Excel.

Reply | Quote

Hi Hans,

Thks for the reply. I need to go back and look at the code you sent earlier using Col A for the student’s name. I think your previous email used Col A for the grade range. At least that’s the way I took it. One thing I didn’t understand from your code was the following set of statements:
ActiveChart.SeriesCollection(1).Delete
…..
ActiveChart.SeriesCollection(1).Select

Why are there these 2 statements – one to delete and then one to add? It doesn’t seem like anything gets done in between the 2 statements that would necessitate the Select. Also, what is the (1) for – is there also something for (2), (3), etc.

As you can probably tell, my experience with Excel VBA as applied to charts is almost non-existent.

As far as changing the legend, I tried that also and could not find a way to change it. The idea of manually generating the legend did occur to me but I decided against that approach.

Maybe using the stacked column chart with many empty cells, as suggested by jon, might also be a way to go.

thanks

fred

Reply | Quote

Fred,

If you use names of students in Col A, then the code will work too. The ActiveChart.Seriescollection(1).Delete can be omitted in the code. The (1) is the number of the seriescollection. You can have a second, third etc. SeriesCollection if you want.

Reply | Quote

Hi Hans,

I played around with your code a little more just now. changed Col A to student names. Works as you said. I guess I have to forgo the legend unless I do it with the “sparse matrix” approach of multiple rows of grades (with either a stacked column or a clustered column).

I also ran into a problem – run time error at
ActiveChart.SeriesCollection(1).Select

Since you said the …Delete statement wasn’t necessary, I commented that out and it worked.

Thanks for the help. My teacher/student is on vacation (I think until the end of Aug). I think between all the items on the lounge that I should be able to put together a solution for her.

Fred

Reply | Quote

I’ve done a lot of work with VBA and the charts and it is a difficult subject and has a convoluted object to learn. I suggest reading the help files as they are usually good at answering your questions (but they do have errors). From within the VBA editor, put your cursor on the method in question (SeriesCollection) and press F1. I learned most of what I know from reading the help and writing sample code (plus lots of swearing and trial/error).

The data that gets plotted is stored in what Excel calls a “Series” which is a collection (you know that because of the ‘s’ which 95% of the collections use, Names, Charts, etc.) So SeriesCollection(1) is the first set of data plotted. Charts can plot multiple data sets each of which is shown as a line on the graph. Typically you’re storing an array of data to be plotted.

As to why the .Select, I can’t say w/o seeing the code, but in some cases it’s necessary to select the chart object before doing something with it, but not always. Most of the time it’s totally unnecessary but if you look at code that comes from recorded macros, it’s all over the place (that’s one of many reasons why using these macros un-edited is a bad idea as they write crappy code and don’t reveal other ways of doing things). If you’re curious, just comment out that line and see if it still works, if not then put it back. Trial and error is a good teacher, you’ll remember it more than if someone just told you the answer.

Deb

Reply | Quote

Deb,

Thks for the info.

Hans’s code is in one of the posts in this thread. So if you want to see it, check out his 11-Aug posting.

I’m still not sure about your statement of “SeriesCollection(1) is the first set of data plotted”. I know that Excel can create multiple “lines” like a line-column chart. So would data for the line be SeriesCollection(1) while for the column be …(2)? In the case of a column chart (like what I’m doing), are you saying that I could have multiple bars as …(1) and yet other bars as …(2)? If so, it would seem that, with VBA, you could create almost any kind of chart and with any number of data sets.

Based on what you said, it would seem that it should not be difficult to tackle my problem as originally posed: have a set of data with names and grades (and no blanks), then have a macro that turns this into a column chart (easy to do/record) and then go to a “lookup table” that gives the color for the bar based on the grade and change the colors appropriately. However, I think I will stick with the solutions that we’ve arrived at (either Hans’s or mine with the sparse matrix approach).

I did try reading about the chart object model in Steve Roman’s Programming Excel pub’d by O’Reilly. Convoluted is generous. I did try a few things but got nowhere fast.

fred

Reply | Quote

Yes, you are correct in saying each “series” represents one line or in your case column of data (the word you use depends on what type of chart you’re making if it was a pie chart, then each piece of the pie is a ‘series’).

Learning the chart object model is not trivial as you know. I’d recommend starting with recording a non-trivial macro to create a chart then edit the code a bit at a time see what gets changed and what fails. There are undocumented differences in what works for xl97 and xl2000 so don’t assume the same code will work for both versions (if that’s a concern).

In m case, my code dynamically sets the chart title, its size, the # of data sets (series) to be plotted, and legends. To add a new series (set of data to be plotted, columns in your case) you do:
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(ser).Values = vData

where ‘ser’ is the series # (1,2,3,4, etc.) and ‘vData’ is an array of data (40 elements in my case). Keep doing this for as many as you need.

The whole for loop is here (for all the series I want add – not indented nicely here, forgot how to do that in The Lounge):

For ser = 1 To TotalSeries
vData = chtData.ReadChartData(chartName, ser)

ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(ser).Values = vData

With ActiveChart
If chtConfig.IsLegendRequired(chartName) Then
legText = chtConfig.LegendList(chartName)
.HasLegend = True
.Legend.Position = xlLegendPositionBottom
.SeriesCollection(ser).Name = CStr(legText(ser))
Else
.HasLegend = False
End If
.DisplayBlanksAs = xlNotPlotted ‘xlInterpolated, xlZero
End With
Next ser

oCht.RoundedCorners = True
oCht.Width = CHT_WIDTH
oCht.Height = CHT_HEIGHT

With ActiveChart
.HasTitle = True
.ChartTitle.Text = chtConfig.WhatTitle(chartName)
.ProtectSelection = True
.HasDataTable = showTable
End With

If chartName cName_SubProfit And chartName cName_Profit Then
Call DoXLabel(chartName, chartSheet)
End If
Next k

John Green’s book “Excel 2000 VBA” has a very good section on the chart object.

HTH, Deb

Reply | Quote

Thanks Deb. I’ll play around a bit – always like to explore and get my hands dirty (but don’t want to lose any fingers in the process). I’ll also check out John’s book.

fred

Reply | Quote

Hi Deb,

Thks for the help and encouragement. I will play around with charts and its VBA. Will also look into John’s book.

Hans came up with a neat solution for my problem using pivot tables, don’t know if you saw it. Check out his last posting on this thread (as of now) if interested.

I’m sure that between the pivot table and the chart solutions, my teacher/student will have something she can use.

fred

Reply | Quote

Fred,

The thought crossed my mind that what you want can actually be done with a pivottable and a pivotchart (as you are using Excel 2000, this can be done). I attached a file on which I created some student results data and ordered them in a certain way on sheet1. Then I made a pivot table and chart, using the wizard, dragging the fields ‘name’ to the ROW filed, dragging the filed ‘Class’ to the COLUMN field, dragging the ‘result’ field to the DATA and PAGE field and changing the ‘Count of Result’ into ‘Sum of Result’. In that case, the pivotchart should be in the form that you want, allowing you even to restrict your chart to e.g. only the students with values between 70 and 80 or so. Anyhow, this method does not involve macros and the data can be changed (added or deleted) any time. The only thing you have to do is refresh the pivot table and chart.

Reply | Quote

Hans,

Excellent suggestion. I’m running 97 here at home so can’t try it. But will give it a whirl when I’m back in the office with 2000.

For my purposes, it sounds like pivot table/charts may be the way to go. I hardly use them so don’t think of them (I think the pivot chart is new to 2000 so I definitely wouldn’t have used it much).

However, for my teacher/student, I might stick to the chart-based solutions. She had done a fair amount of work on her own and even learned how to set up a customized chart from her husband’s boss. I just did the color the bars part. Pivot tables are probably something she might not even have heard have or ever used, let alone the chart capability in 2000.

Even tho not required for this exercise, I recall my brief glance at the pivot table object model indicated it was on par with the chart object model in terms of simplicity.

fred

Reply | Quote

Hans,

I tried the pivot table approach. Worked great. Did make a slight enhancement – instead of typing out the “class”, I just used the following formula in col C
=TEXT(FLOOR(B15,10),”000″)&”‘s”
to give classes of, for example, “070’s” and “080’s”

Because of the pivot-table sort approach for cols, I needed the format to be 000 instead of #00 (which would have been preferred). I added someone who had a result of 100 (class “100’s”, which ended up sorting ahead of “70’s”).

Two questions for my own edification:
– if I inadvertently leave someone out of the pivot table, is there a way to get her back into it without redoing the entire table (kind of like dragging a set of cells into a chart to add a bar)?
– if I only choose originally to have a pivot table, is there a way to subsequently get a chart on the same sheet? I saw that I could add a pivot chart afterwards but it would be on its own chart-sheet (no choice given).

I will send your pivot table approach to my student and see which way she wants to go.

Thanks for all the help.

fred

Reply | Quote

Fred,

If you click the drop-down arrow on the ‘Name’ field button on the chart, you should be able to check out/on any name you want by clicking the checkbox next to the name. The same applies to the fields ‘Result’ or ‘Class’.
Adding a chart afterwards: you are doing it the right way. As far as I know Excel 2000 does not offer you the choice for the chart location. It’s always a chart sheet, but once this chart sheet is made, then by right-clicking the chart you can choose ‘Location’ and make the chart embedded to a sheet of your choice.

Reply | Quote

Hans,

Thks for the info. On the issue of adding a name, I was hoping there was a way to correct my error when I created the pivot table originally. When I did that, I inadvertently selected the headings and only n students (n being 1 less than what you had set up). What I was looking for was a way to get that last row w/o redoing the entire pivot table. Kind of like with a chart where you can go in after the creation and change the range for the data series. Didn’t see anything like that.

Fred

Reply | Quote

If you want to add a name, this corresponds to adding a row to the original database. You can do this by going back to the wizard, by right-clicking the pivottable. Then by going back a few steps (you need to go back to step 2, I think) you can change the range address of your database. By then pressing OK, your pivot tabel and chart should be updated to the new range. Also, use ‘refresh data’ or ‘refresh chart’ if you changed something to the contents of your database. This ‘refresh data’ can be obtained by right-clicking the tabel, the ‘refresh chart’ can be obtained in an analogous way.

Reply | Quote

Hans,

Have now played with various things with pivot tables per our last few exchanges. everything works great –
– got my pivot chart onto the same page as the pivot table, where the chart was created separately after the table
– got my left-out name back into the pivot table by changing the range by going backward in the wizard.

Excellent.

Will await my student’s return and see which way she wants to go.

Thanks a million.

Fred

Reply | Quote

Deb,

sorry – Hans’s code was in his 8 Aug posting on this thread.

fred

Reply | Quote