Peltier Technical Services - Excel Charts and Programming
In the comments of Choice of Category Axis Order a reader asked how to use only part of a range as the source data for a chart. Her data and chart initially looked like this:
The line “Total Other Items” is a sum of the items below it in the list, and the chart does not show this subtotal. The series formula looks like:
=SERIES(Sheet1!$B$1,(Sheet1!$A$2:$A$4,Sheet1!$A$6:$A$8),
(Sheet1!$B$2:$B$4,Sheet1!$B$6:$B$8),1)
I was asked if there is any way to count all of the bold cells in a range. Of course there is. Here is a simple VBA function that carries out this task:
Function CountBoldCellsInRange(rng As Range) As Long
Dim rCell As Range
Dim iBold As Long
iBold = 0
For Each rCell In rng
If rCell.Font.Bold Then
iBold = iBold + 1
End If
Next
CountBoldCellsInRange = iBold
End Function
This function can be called in a number of ways. Here are two examples showing how to call the function from other VBA procedures.
Sub Test1CountBoldCellsInRange()
Dim i As Long
If TypeName(Selection) = "Range" Then
i = CountBoldCellsInRange(Selection)
MsgBox "There are " & i & " bold cells in the selected range."
Else
MsgBox "The selected object is not a range."
End If
End Sub
Sub Test2CountBoldCellsInRange() Dim i As Long Dim r As Range Dim sAddress As String sAddress = "A1:F20" Set r = ActiveSheet.Range(sAddress) i = CountBoldCellsInRange(r) MsgBox "There are " & i & " bold cells in " & sAddress & "." End Sub
You can even call it from a worksheet cell, using this syntax.
=CountBoldCellsInRange(A1:F20)
In 9 Steps to Simpler Chart Formatting I suggested using data labels to identify each series rather than using a legend. I have a small VBA procedure that I use for this. It labels the last point of each series, and removes other labels. It also has an error trap that skips points that are not plotted because of blank cells or #N/A errors.
Sub LabelLastPoint()
Dim mySrs As Series
Dim iPts As Long
Dim bLabeled As Boolean
If ActiveChart Is Nothing Then
MsgBox "Select a chart and try again.", vbExclamation
Else
For Each mySrs In ActiveChart.SeriesCollection
bLabeled = False
With mySrs
For iPts = .Points.count To 1 Step -1
If bLabeled Then ' series has already received valid label
' handle error if point isn't plotted
On Error Resume Next
' remove existing label if it's not the last point
mySrs.Points(iPts).HasDataLabel = False
On Error GoTo 0
Else ' series does not yet have valid label
' handle error if point isn't plotted
On Error Resume Next
' remove existing label (linked labels otherwise resist reassignment)
mySrs.Points(iPts).HasDataLabel = False
' add label
mySrs.Points(iPts).ApplyDataLabels _
ShowSeriesName:=True, _
ShowCategoryName:=False, ShowValue:=False, _
AutoText:=True, LegendKey:=False
bLabeled = (Err.Number = 0)
' 2010 no error if point doesn't exist: label applied, but it's blank
If bLabeled Then bLabeled = (Len(mySrs.Points(iPts).DataLabel.Text) > 0)
If Not bLabeled Then
' remove blank label
mySrs.Points(iPts).HasDataLabel = False
End If
On Error GoTo 0
End If
Next
End With
Next
' remove legend
ActiveChart.HasLegend = False
End If
End Sub
To implement this procedure, follow the steps in How To: Use Someone Else’s Macro.
This post presents a VBA procedure that extracts all data from a chart, and places it into a new worksheet. Why would you want to extract a chart’s data? Sometimes a chart gets its data from diverse sources, and you’d like to have the data in one place. You may receive a workbook that contains a chart, but the chart’s data is in a workbook which you don’t have access to. For whatever reason, this procedure comes in handy.
Sub ExtractChartData()
Dim iSrs As Long
Dim cht As Chart
Dim srs As Series
Dim ws As Worksheet
If ActiveChart Is Nothing Then Exit Sub
Set cht = ActiveChart
Set ws = Worksheets.Add
For iSrs = 1 To cht.SeriesCollection.Count
Set srs = cht.SeriesCollection(iSrs)
On Error Resume Next
ws.Cells(1, 2 * iSrs).Value = srs.Name
ws.Cells(2, 2 * iSrs - 1).Resize(srs.Points.Count).Value = _
WorksheetFunction.Transpose(srs.XValues)
ws.Cells(2, 2 * iSrs).Resize(srs.Points.Count).Value = _
WorksheetFunction.Transpose(srs.Values)
Next
End Sub
The procedure merely extracts the data, it does not change the data source of the chart to the ranges in the new worksheet. If desired, you could embellish this macro, so it restores information such as chart type, series formatting (marker or fill style and colors, line or border styles and colors), axis types and scales, axis and chart titles, data labels, and more.
If you are not sure how to use this procedure, read How To Use Someone Else’s Macro.
The Calculating Easter post on John Walkenbach‘s new Spreadsheet Page blog got me thinking. He presented a formula for calculating the date of Easter in a given year:
=DOLLAR(("4/"&A2)/7+MOD(19*MOD(A2,19)-7,30)*14%,)*7-6
Chip Pearson‘s website also has a couple of formulas for calculating Easter:
=FLOOR("5/"&DAY(MINUTE(A2/38)/2+56)&"/"&A2,7)-34
=FLOOR(DATE(A2,5,DAY(MINUTE(A2/38)/2+56)),7)-34
Chip also offers a UDF (VBA User Defined Function) which is valid through 2099 but breaks after that because it oversimplifies the calculation of leap years.
Public Function EasterDate(Yr As Integer) As Date
'Chip Pearson from unknown
'see http://cpearson.com/excel/holidays.htm#Easter
Dim d As Integer
d = (((255 - 11 * (Yr Mod 19)) - 21) Mod 30) + 21
EasterDate = DateSerial(Yr, 3, 1) + d + (d > 48) + 6 - ((Yr + Yr \ 4 + _
d + (d > 48) + 1) Mod 7)
End Function
In addition, I saved a couple of UDFs from the newsgroup a few years back. Norman Harker wrote these based on an extensive discussion of the topic on Claus Tondering’s website.
Function EASTER(year As Single) As Date
'Based on Claus Tondering algorithm interpretation.
'See http://www.tondering.dk/claus/cal/calendar29.html
'Norman Harker 10-Jul-2004
Dim G As Integer: Dim C As Integer: Dim H As Integer
Dim I As Integer: Dim J As Integer: Dim L As Integer
Dim EM As Integer: Dim ED As Integer
Dim Adj1904 As Integer
G = year Mod 19
C = year \ 100
H = (C - C \ 4 - (8 * C + 13) \ 25 + 19 * G + 15) Mod 30
I = H - (H \ 28) * (1 - (29 \ (H + 1)) * ((21 - G) \ 11))
J = (year + year \ 4 + I + 2 - C + C \ 4) Mod 7
L = I - J
EM = 3 + (L + 40) \ 44
ED = L + 28 - (31 * (EM \ 4))
If ActiveWorkbook.Date1904 = True Then
Adj1904 = 365 * 4 + 2
End If
EASTER = DateSerial(year, EM, ED) - Adj1904
End Function
Function ORTHODOXEASTER(year As Single) As Date
'Based on Claus Tondering algorithm interpretation.
'See http://www.tondering.dk/claus/cal/calendar29.html
'Norman Harker 11-Jul-2004
Dim G As Integer: Dim I As Integer: Dim J As Integer
Dim L As Integer
Dim EM As Integer: Dim ED As Integer
Dim Adj1904 As Integer: Dim JulAdj As Integer
G = year Mod 19
I = (19 * G + 15) Mod 30
J = (year + year \ 4 + I) Mod 7
L = I - J
EM = 3 + (L + 40) \ 44
ED = L + 28 - (31 * (EM \ 4))
If ActiveWorkbook.Date1904 = True Then
Adj1904 = 365 * 4 + 2
End If
JulAdj = 10 + (year \ 100) - 16 - (year \ 400) + 4
ORTHODOXEASTER = DateSerial(year, EM, ED) - Adj1904 + JulAdj
End Function
The worksheet formulas are interesting because they are compact and don’t require VBA. Chip’s two worksheet formulas agree with each other, which is not surprising because they look somewhat similar, but they disagree with John’s worksheet formula and with Chip and Norman’s UDFs, which agree. Neither John nor Chip admit ownership of these respective calculations, so the error comes from the originators of one or the other relationship. Since John’s formula agrees with Norman’s UDF, which is based on a very rigorous explanation by Claus, I would guess that Chip’s source is mistaken on the worksheet formulas. The only difference in the calculated dates for Easter between 1900 and 2199 is that Chip’s calculation is a week earlier in 2079. The same error occurs in 2744. I doubt that anyone reading this page will be aware of the problem in 2079 (I’ll be 119 years old!) or even alive in 2744.
In Gradients, Fills, and Shadows, Oh My, I commented on the bar chart John used to show the occurrence of Easter on a given date from 1900 to 2199, and the discrepancy between John’s and Chip’s formulas. What I’m more interested in is the difference between the standard (“Roman”) and Orthodox (“Greek”) determination of Easter. Both are based on misinterpretations of the Jewish lunar calendar, and the Greek calculation has an additional discrepancy between the Julian and Gregorian date systems. I won’t go into these issues, because Claus Tondering covers them in great detail. (Another detailed source is The Determination of Easter, on both the Julian and the Gregorian Calendars.) But I had a few interesting observations that may incidentally illustrate some useful charting techniques.
I started with the line chart I made previously to compare John’s and Chip’s Easter calculations. You can see that the two curves are offset, but the overlapping of the curves makes it hard to follow them.
I made a column chart of the data, then made it into a panel chart as follows. I moved the Greek series to the secondary axis and made sure I had both primary and secondary category axes. I set the primary value axis for a minimum of 0, a maximum of 28, and a spacing of 2. I set the secondary value axis for a minimum of -14, a maximum of 14, and a spacing of 2. I set both category axes to cross their respective value axes at a value of zero. I used custom number formats for the axis tick labels: primary [>14]" ";0, secondary "0;;0".
In Choice of Category Axis Order, I showed this same data in simple line charts.
This display arrangement (as time series) makes it easier to see that the range of Roman dates begins almost two weeks earlier than the range of Greek dates, and also ends two weeks earlier. The following chart shows the distributions from the column chart above, offset so their centers coincide.
The shapes of the distributions are similar, but not exact, and from experience, I know that the difference is not always the same. Below I’ve plotted a histogram of the misalignment between Roman and Greek Easter. Almost half the time, Greek Easter is the week after Roman Easter. Another quarter of the time (a bit more actually), the two holidays coincide. Nearly a quarter of the time, Greek Easter is a month later, occasionally four weeks but mostly five weeks later than Roman Easter. There is never a difference of two or three weeks: both calculations put Easter right after a full moon, so either both calculations use the same full moon (0 or 1-week difference), or the Greek calculation uses the next full moon (4 or 5 weeks difference). The one-week differential is an offset in the Greek calculation to ensure that Easter follows Passover.
I suppose I could have shown this distribution with a (gasp!) pie chart, although the pie hides the absence of 2 or 3-week offsets, while the histogram above retains the empty categories.
