Re: Replace Second Word Of A String
=LEFT(AI1,FIND(" ",AI1)-1)&" Monday"
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
Display More
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
Display More
Thanks!