Suppose that you’re putting together a line chart that will, for whatever reason, have several plateaus.

Now, you’d like to add labels to indicate the value…

Not quite what you had been hoping for? Now, I very well could be wrong, but I couldn’t find a quick way of indicating to Excel which labels you’d like to show, e.g., every third label, etc. Don’t worry, this is a quick and easy work around for this.

Suppose that your data sheet is setup as follows:

All that you need to do, is add a new row for the labels themselves. In cell B3, type the following formula:

=IF(B2>0,IF(B2=A2,"",B2))

What this is doing is looking at B2 and ignoring it if it is equal to 0. The reason that we do this is so that we don’t end up with a row of 0’s printred across the x-axis of our chart. Of course, if your chart extends into the negative numbers, go ahead and change the value following > to be equal to the lower limit of your y-axis. Now, if it finds that the value in B2 is greater than 0 (or your lower limit) it checks it against the value in the cell to it’s left. If they are the same, if prints a blank space, if they differ, it prints the value of B2.

The next step is to add another data series to your chart. Go back to your chart, right click on the chart area, and choose “Select Data” or “Source Data”, depending on the version of Excel that you’re using. Click “Add”, and identify your new row as the source of the series data. Click OK to exit this window.

Now, right click on the new line that has been plotted (it should look like a series of spikes whos apex’s touch the first data point on each plateau. In the right click menu, choose Format Data Series. Set your line and markers to none, then turn on your data labels for values. You should end up with something like this:

If you’d like to try and center the labels as I have here, assuming that the majority of your plateaus are the same width, you have to adjust the formula to look back one or two more cells, instead of just checking for a match to the immediate left.

Excel Solutions Excel, Line charts

I have a spreadsheet that I’m working on with thousands and thousands of time values listed strictly in seconds that I need to have converted into an hh:mm:ss format. Now, if we were just working in Excel, that’s as easy as saying =A1 / (1440*60) where A1 holds the value in seconds, then applying the time/37:30:55 format; a format that ensures that the value doesn’t roll over after 24 hours. The problem I had was that it all needed to be done in VBA which, apparently, doesn’t have any means of formatting a value in such a way… atleast none that I could fine in a day or so of searching…

So, just in case any one else out there is having the same issue, I thought I’d share the function that I ended up writing. It does the job quite nicely.

Function convertTime(i As String)
' D'Arcy Gregoire
' October 24, 2007
'
Dim hVal As Double
Dim hInt As Double
Dim hDec As Double
Dim hFin As String
Dim mVal As Double
Dim mInt As Integer
Dim mDec As Double
Dim mFin As String
Dim sVal As Double
Dim sInt As Integer
Dim sFin As String
'
' Hours
If i < 3600 Then
        hFin = "00"
        mVal = Evaluate(i / 60)
    Else
        hVal = Evaluate(Round(i, 0) / 3600)
        hInt = InStrRev(hVal, ".")
        If hInt = 0 Then
            hFin = "0" & Trim(Str(hVal))
        Else
            hInt = Evaluate(Left(hVal, hInt - 1))
            If hInt <= 9 Then
                hFin = "0" & Trim(Str(hInt))
            Else
                hFin = Trim(Str(hInt))
            End If
        End If
        hDec = InStrRev(hVal, ".")
        If hDec = 0 Then
            hDec = 0
        Else
            hDec = Evaluate(Mid(hVal, hDec, 99))
        End If
        mVal = Evaluate(60 * hDec)
    End If
' Minutes
    If i < 60 Then
        mFin = "00"
        sVal = i
    Else
        mInt = InStrRev(mVal, ".")
        If mInt = 0 Then
            mFin = "0" & Trim(Str(mVal))
        Else
            mInt = Evaluate(Left(mVal, mInt - 1))
            If mInt <= 9 Then
                mFin = "0" & Trim(Str(mInt))
            Else
                mFin = Trim(Str(mInt))
            End If
        End If
        mDec = InStrRev(mVal, ".")
        If mDec = 0 Then
            mDec = 0
        Else
            mDec = Evaluate(Mid(mVal, mDec, 99))
        End If
        sVal = Evaluate(60 * mDec)
    End If
' Seconds
    If sVal <= 9 Then
        sFin = "0" & Trim(Str(Evaluate(Round(sVal, 0))))
    Else
        sFin = Trim(Str(Evaluate(Round(sVal, 0))))
    End If
    ' Display result
    convertTime = hFin & ":" & mFin & ":" & sFin
End Function

Excel Solutions Excel, VBA, visual basic