Posts by darkyam

Re: Replace Second Word Of A String

=LEFT(AI1,FIND(" ",AI1)-1)&" Monday"

Re: Sum Data By Dates

If you don't want to use a pivot table, you could also use array formulas.
If we call the cell that has the start date A2, the cell with the end date B2, the cell with the letter you want to sum by C2, and your range of data (not including headers) A5:C17, then you can use the following formulas:
1. =SUM((A5:A17>=A2)*(A5:A17<=B2)*(C5:C17))
2. =SUM((A5:A17>=A2)*(A5:A17<=B2)*(B5:B17=C2)*(C5:C17))
For both of these formulas, you will have to press Ctrl+Shift+Enter to confirm them, as they are array formulas.
3. I would suggest formatting the start and end dates as dates, so that all numbers entered into them are automatically formatted as dates. You can also use data validation to allow only dates in those cells. If the start date is greater than the end date, if the end date is blank, or if both cells are blank, both of these formulas will automatically return 0. If start date is blank, it will return the history of your range through the end date.

Re: Formula With Text Function Rounding Up Result

Hi, Dave. You're right on that, but replacing the "0" with a number format, "0.0", etc. gives "7.5" and "7" is needed. The "0" formatting works, as long as the RoundDown takes place first.

Re: Formula Rounding Up - Unwanted

Try =TEXT((ROUNDDOWN(SUM(D98,D117,D136))/12,0),"0"&...

Re: paste values no value

You don't need a macro to do this. When I try to do a similar formula (can't find where your formula is on this spreadsheet) where a cell in a range evaluates to "", it does not count it when I do a Count(range) formula. However, I have noticed this issue in Offset formulas for some reason and I get around it by Count(range)-Countif(range,"").

Re: Caculate Number To Reach Target

Ahhh, now I think I have it. So, you want to take into account not only the moving target of the forecast, but also your group's current pace compared to what is needed, right?
If so, then I have modified column G to show the the number from E if D is filled in (C times the formula I put in H4 if not) and added the sum of the difference between E and F for all previous days divided by the count of days left. For example for Dec. 6, 450 came in, meaning 129 (technically 128.90) had to be done. For Dec. 3-5th, 450 items needed to be completed and 438 were completed, leaving a difference of 12. This difference was divided by the 26 days from Dec. 6-31 and added to the 128.9, leaving a result of 129.32 (both would, of course, show up as 129).
In other words, if you are very close to your target, the numbers in G will match up very closely with E.
I made a few other minor changes that you can delete if you want (deleting H4 will require the original formula for determining items to be completed to be added back to all necessary cells, so hiding it is recommended rather than deleting). I added a moving forecast in H2. This takes the original forecast, subtracts the amounts in C for days that have happened (i.e. for which amounts exist in D) and adds the sum of D. Cell J4 has a moving Target based on the Moving Forecast.
Cell H3 shows whether your moving forecast is within 10% of the original forecast.
Cell H4 is a shortening of the formula for determining necessary items to be completed. Since everything but the forecast numbers in that formulas are fixed, it makes no sense to repeat the formula, so I replaced all instances of it with the forecast times H4. The big advantage is that, should you ever need to change this formula, you can do it once rather than a bunch of times.

Re: Adding Dashboard Functionality

I updated the ranking formulas to allow for two places having the same %. Since it appears that Sheet 2 is formatted for public viewing, I hid the rankings table (column Q has all the data; I whited it out and hid it). Sheet 1 now shows the rankings by percentage.

Re: Count Between 2 Numbers

If the formula returned as 100%, it sounds like a formatting issue (as 100% is the equivalent of 1). Right click on that cell and choose format cells, then go to the Number tab and select Number as the format.

Re: Caculate Number To Reach Target

The formulas currently in the sheet I understand. I apologize for not being more clear in my questions. Is Inbound to be added to Completed BC, and if not, how does it interact with Completed BC? The reason I ask is that it seems clear from the formatting of the sheet that you want a number (and only one number) for each day and, as I interpret it, that number is a target for just Completed BC for what you have to hit that day to achieve your monthly goal. As a minimum, technically, you could do nothing for a day, and it would just increase the pace you would need to maintain for the rest of the month. If I need to add Inbound to BC, that's a very easy change to make. The formula in H9 would simply become =(($F$2-SUM($G$9:G9))+($C$3-SUM($D$9:D9))/COUNTA(B10:$B$37) and you could drag that down.

If you do not want the values showing up in column H until G and (possibly) D are filled in, adding a simple IF statement can have them evaluate as blanks until values are entered.

Re: Formula With Multiple Criteria

How exactly are Inbound and BC related (I'm not familiar with the term BC)? What I did does not take into account the inbound part because it was unclear how that related to BC. Right now, it is just based off completed BC (and would thus update with each value put into G for that day).

Re: Formula With Multiple Criteria

I based the formulas in column H off of Completed BC. The formula in H returns the BC needed per day to finish the task as of the end of that day. For example, on row 9, it takes Target BC minus 133 completed and divides the result by the 28 days left in the month. If you were looking to base it on multiple columns, could you please be more specific about precisely what you want the formula to evaluate?

Re: Calculate Shortest Distance From Coordinates

I was able to modify the formulas a little bit and shorten this to only two additional columns. If you want this as only one or two cells, your best bet is probably some VBA code.

Re: Sum If-function With More Than One Criteria

You could also use an array formula (remember to hit Ctrl+Shift+Enter after entering the formula):
=SUM((Country="Sweden")*(Year=2005)*(Value))
You can replace "Sweden" and 2005 with cell references.

Re: Scatter Chart Coloration Tied To Cell Values

You, sir, are absolutely brilliant. I'm constantly amazed that sites like this have people like you willing to help twits like me out of the goodness of your hearts.

Re: Scatter Chart Coloration Tied To Cell Values

I based it off named ranges, so that it will adjust depending on how many rows are shown in columns H:K. The SERIES formula is =SERIES(Sheet1!$I$1,'test data2.xls'!Conversion,'test data2.xls'!SessionDuration,1,'test data2.xls'!PageViews).

Re: Checkboxes To Delete From A List

Another possibility, if you're not set on the idea of a checkbox object, is to put an "X" or, if you would rather have a check mark, a lower-case "a" with the font changed to Marlett, in column A instead of the checkbox. Then adjust your macro to read column A for the "X" or "a" and delete accordingly, so that when the row is deleted, the "X" or "a" goes with it.

Re: Scatter Chart Coloration Tied To Cell Values

By killing the charts, I was able to get the size down low enough. I left the macros in question in, but with the one I removed, the values in column M have to be changed manually (each true/false determines whether that row shows up in a chart that is created). Thanks for your help on this and patience with a humble VBA neophyte.

Re: Scatter Chart Coloration Tied To Cell Values

With the 2 modules and the chart on there, even deleting the other macro in the spreadsheet and all but four rows of data still leaves me 12kb high after zipping it up.

Re: Scatter Chart Coloration Tied To Cell Values

I tried, but the sheet is 660kb (490kb zipped) and the upload limit is 48kb. Is there a way to compress this so I can upload it? Thanks.

The idea of getting a scatter chart to have a smooth spectrum from http://www.ozgrid.com/Excel/xy-scatter-color.htm is brilliant, but there is a slight problem I am having with it. The number of points possible in the chart determines the color, rather than the values of cells. I have a table with 100 rows and the chart is based off data from that table; however, instead of using cell references in SERIES, I have named ranges, as part of the table returns a shortened list of values based on user-selected parameters.
In short, this means that if only 7 values are returned in this shortened table, the coloration sees 7 values out of 100 and goes only 7/100ths of the way from red to green. Is there a way to get the coloration based on a certain value in the worksheet for that row? For example, if I have a list of test scores ranging from 0% to 100%, can I get the same coloration to show for a score of 88%, regardless of whether one record is selected or all of them?
Also, if I wanted a user to be able to easily change the parameters of the spectrum so that they could see a spectrum of scores from 50% to 100%, can I get the macro to look these values up from the worksheet?
There are two modules in what I am using now, the class module is called CSpectrum and reads:

Option Explicit


Private Enum enumSpectrum
    Red = 1
    Green
    Blue
End Enum


Private m_lngStartColor As Long
Private m_lngEndColor As Long
Private m_lngCountColor As Long
Private m_lngSpectrum() As Long
Private m_blnUpdatedSpectrum As Boolean
Public Property Let Count(RHS As Long)
    If RHS < 1 Then
        m_lngCountColor = 1
    ElseIf RHS > 255 Then
        m_lngCountColor = 255
    Else
        m_lngCountColor = RHS
    End If
    
    m_blnUpdatedSpectrum = False
End Property
Public Property Get Count() As Long
    Count = m_lngCountColor
End Property
Public Sub CreateSpectrum()
'
' Calculate the spread of colours
'
    Dim lngIndex As Long
    Dim lngColor As Long
    Dim sngSpreadRed As Single
    Dim sngSpreadGreen As Single
    Dim sngSpreadBlue As Single
    Dim sngRed As Single
    Dim sngGreen As Single
    Dim sngBlue As Single
    
    If m_lngCountColor = 0 Then
        m_lngCountColor = 2
        ReDim m_lngSpectrum(m_lngCountColor) As Long
        m_lngSpectrum(1) = m_lngStartColor
        m_lngSpectrum(2) = m_lngEndColor
        m_blnUpdatedSpectrum = True
    End If
    
    ReDim m_lngSpectrum(m_lngCountColor) As Long
    m_lngSpectrum(1) = m_lngStartColor
    m_lngSpectrum(m_lngCountColor) = m_lngEndColor
    sngRed = CSng(m_Color2RGB(m_lngSpectrum(1), Red))
    sngGreen = CSng(m_Color2RGB(m_lngSpectrum(1), Green))
    sngBlue = CSng(m_Color2RGB(m_lngSpectrum(1), Blue))
    
    sngSpreadRed = (m_Color2RGB(m_lngSpectrum(m_lngCountColor), Red) - sngRed) / m_lngCountColor
    sngSpreadGreen = (m_Color2RGB(m_lngSpectrum(m_lngCountColor), Green) - sngGreen) / m_lngCountColor
    sngSpreadBlue = (m_Color2RGB(m_lngSpectrum(m_lngCountColor), Blue) - sngBlue) / m_lngCountColor
    
    For lngIndex = 2 To m_lngCountColor - 1
        sngRed = sngRed + sngSpreadRed
        sngGreen = sngGreen + sngSpreadGreen
        sngBlue = sngBlue + sngSpreadBlue
        m_lngSpectrum(lngIndex) = RGB(CInt(sngRed), CInt(sngGreen), CInt(sngBlue))
    Next
    
    m_blnUpdatedSpectrum = True
    
End Sub
Private Function m_Color2RGB(Color As Long, Element As enumSpectrum) As Long
'
' Return RGB element for given color
'
    Select Case Element
    Case enumSpectrum.Red
        m_Color2RGB = Color \ 256 ^ 0 And 255
    Case enumSpectrum.Green
        m_Color2RGB = Color \ 256 ^ 1 And 255
    Case enumSpectrum.Blue
        m_Color2RGB = Color \ 256 ^ 2 And 255
    End Select
    
End Function


Public Property Get SpectrumColor(Index As Long) As Long
    If Index > m_lngCountColor Then
        SpectrumColor = m_lngSpectrum(m_lngCountColor)
    ElseIf Index < 1 Then
        SpectrumColor = m_lngSpectrum(1)
    Else
        SpectrumColor = m_lngSpectrum(Index)
    End If
End Property
Public Property Let StartColor(RHS As Long)
    m_lngStartColor = RHS
    m_blnUpdatedSpectrum = False
End Property
Public Property Let EndColor(RHS As Long)
    m_lngEndColor = RHS
    m_blnUpdatedSpectrum = False
End Property
Public Property Get StartColor() As Long
    StartColor = m_lngStartColor
End Property
Public Property Get EndColor() As Long
    EndColor = m_lngEndColor
End Property
Private Sub Class_Initialize()


    ' default settings
    m_lngCountColor = 56
    m_lngStartColor = RGB(0, 0, 255)  ' blue
    m_lngEndColor = RGB(255, 0, 0)    ' red
    m_blnUpdatedSpectrum = False
    CreateSpectrum
    
End Sub

The other module is called MSpectrum and reads:

Option Explicit


Sub Main()


    Dim clsSpectrum As CSpectrum
    
    Set clsSpectrum = New CSpectrum
    With clsSpectrum
        .Count = 256
        .StartColor = RGB(255, 0, 0)
        .EndColor = RGB(0, 255, 0)
        .CreateSpectrum
    End With


    UsingMarkers clsSpectrum, ActiveSheet.ChartObjects(1).Chart
    UsingCustomMarkers clsSpectrum, ActiveSheet.ChartObjects(2).Chart
    
End Sub


Sub UsingMarkers(Spectrum As CSpectrum, Cht As Chart)
'
' Using builtin color palette
'
    Dim lngIndex As Long
    Dim intPoint As Integer
    
    With Cht
        With .SeriesCollection(1)
            For intPoint = 1 To .Points.Count
                lngIndex = intPoint * (Spectrum.Count / .Points.Count)
                With .Points(intPoint)
                    .MarkerBackgroundColor = Spectrum.SpectrumColor(lngIndex)
                    .MarkerForegroundColor = Spectrum.SpectrumColor(lngIndex)
                End With
            Next
        End With
    End With
    
End Sub
Sub UsingCustomMarkers(Spectrum As CSpectrum, Cht As Chart)
'
' Use a shape as a custom marker
'
    Dim shpMarker As Shape
    Dim lngIndex As Long
    Dim intPoint As Integer
    
    Application.ScreenUpdating = False
    Set shpMarker = ActiveSheet.Shapes("Marker")
    With Cht
        With .SeriesCollection(1)
            For intPoint = 1 To .Points.Count
                lngIndex = intPoint * (Spectrum.Count / .Points.Count)
                shpMarker.Fill.ForeColor.RGB = Spectrum.SpectrumColor(lngIndex)
                shpMarker.CopyPicture
                .Points(intPoint).Paste
            Next
        End With
    End With
    Application.ScreenUpdating = True
    
End Sub

Thanks!