LOVE THIS!! Convert any statement into Yoda speak
Acting like yoda are you. Herh herh herh.
LOVE THIS!! Convert any statement into Yoda speak
Acting like yoda are you. Herh herh herh.
Yesterday Ting Liu, Finance Lead at Zayo Bandwidth, asked if Excel had a function to calculate the first day of the month. Great question …
A prior post showed how to use the EOMONTH function. EOMONTH returns the last day of the month.
You can also use EOMONTH, to calculate the first day of the month.
Note - if you receive a #NAME? error, make sure your addins are turned on.
You’ll often find that you need to show the end of the month in data analysis or when setting up a forecast template. The EOMONTH command is the right tool for this job.
First, make sure your addins are turned on:
Next type the EOMONTH command:
Next format the date.
Then, copy the formula to the right:
Each cell now shows the last day of each successive month.
Note - +EOMONTH(B4,0) would have shown the last day of the current month.
Net Installs are tracked and forecasted on a weekly basis. We use historical net installs as a rear view mirror and a forecast, to tell us where we are going.
We use a stacked bar to depict net installs. The top of the bar = Net Installs. Notice that when Net Installs >$0, the blue bar is split into 2. The bottom blue bar = the disconnect bar, the other blue bar = the amount that Net Installs is >$0.
Here’s how you build the chart:
1. Start by setting up the data sheet:
2. Create the graph data (in gray below). The graph requires 4 bars.
3. Create the chart
4. Format Chart Area
5. Format Plot area
6. Format the Y axis, so that it displays currency
7. Format the X Axis
8. Color the bars
Your chart now looks like this:
Go to Businesstoolsblog Shared Files Google Group for a free template.
Converting numbers to words without the cents feature can be done with the below VBA code (there are no dollars and cents and no “exactly” in this macro)
Function English(ByVal N) As String Const Thousand = 1000@ Const Million = Thousand * Thousand Const Billion = Thousand * Million Const Trillion = Thousand * Billion If (N = 0@) Then English = "zero": Exit Function Dim Buf As String: If (N < 0@) Then Buf = "negative " Else Buf = "" Dim Frac As Currency: Frac = Abs(N - Fix(N)) If (N < 0@ Or Frac <> 0@) Then N = Abs(Fix(N)) Dim AtLeastOne As Integer: AtLeastOne = N >= 1 If (N >= Trillion) Then Debug.Print N Buf = Buf & EnglishDigitGroup(Int(N / Trillion)) & " trillion" N = N - Int(N / Trillion) * Trillion If (N >= 1@) Then Buf = Buf & " " End If If (N >= Billion) Then Debug.Print N Buf = Buf & EnglishDigitGroup(Int(N / Billion)) & " billion" N = N - Int(N / Billion) * Billion If (N >= 1@) Then Buf = Buf & " " End If If (N >= Million) Then Debug.Print N Buf = Buf & EnglishDigitGroup(N \ Million) & " million" N = N Mod Million If (N >= 1@) Then Buf = Buf & " " End If If (N >= Thousand) Then Debug.Print N Buf = Buf & EnglishDigitGroup(N \ Thousand) & " thousand" N = N Mod Thousand If (N >= 1@) Then Buf = Buf & " " End If If (N >= 1@) Then Debug.Print N Buf = Buf & EnglishDigitGroup(N) End If If (Frac = 0@) Then Buf = Buf & " " ElseIf (Int(Frac * 100@) = Frac * 100@) Then If AtLeastOne Then Buf = Buf & " and " Buf = Buf & Format$(Frac * 100@, "00") & "/100" Else If AtLeastOne Then Buf = Buf & " and " Buf = Buf & Format$(Frac * 10000@, "0000") & "/10000" End If English = Buf End Function Private Function EnglishDigitGroup(ByVal N As Integer) As String Const Hundred = " hundred" Const One = "one" Const Two = "two" Const Three = "three" Const Four = "four" Const Five = "five" Const Six = "six" Const Seven = "seven" Const Eight = "eight" Const Nine = "nine" Dim Buf As String: Buf = "" Dim Flag As Integer: Flag = False Select Case (N \ 100) Case 0: Buf = "": Flag = False Case 1: Buf = One & Hundred: Flag = True Case 2: Buf = Two & Hundred: Flag = True Case 3: Buf = Three & Hundred: Flag = True Case 4: Buf = Four & Hundred: Flag = True Case 5: Buf = Five & Hundred: Flag = True Case 6: Buf = Six & Hundred: Flag = True Case 7: Buf = Seven & Hundred: Flag = True Case 8: Buf = Eight & Hundred: Flag = True Case 9: Buf = Nine & Hundred: Flag = True End Select If (Flag <> False) Then N = N Mod 100 If (N > 0) Then If (Flag <> False) Then Buf = Buf & " " Else EnglishDigitGroup = Buf Exit Function End If Select Case (N \ 10) Case 0, 1: Flag = False Case 2: Buf = Buf & "twenty": Flag = True Case 3: Buf = Buf & "thirty": Flag = True Case 4: Buf = Buf & "forty": Flag = True Case 5: Buf = Buf & "fifty": Flag = True Case 6: Buf = Buf & "sixty": Flag = True Case 7: Buf = Buf & "seventy": Flag = True Case 8: Buf = Buf & "eighty": Flag = True Case 9: Buf = Buf & "ninety": Flag = True End Select If (Flag <> False) Then N = N Mod 10 If (N > 0) Then If (Flag <> False) Then Buf = Buf & "-" Else EnglishDigitGroup = Buf Exit Function End If Select Case (N) Case 0: Case 1: Buf = Buf & One Case 2: Buf = Buf & Two Case 3: Buf = Buf & Three Case 4: Buf = Buf & Four Case 5: Buf = Buf & Five Case 6: Buf = Buf & Six Case 7: Buf = Buf & Seven Case 8: Buf = Buf & Eight Case 9: Buf = Buf & Nine Case 10: Buf = Buf & "ten" Case 11: Buf = Buf & "eleven" Case 12: Buf = Buf & "twelve" Case 13: Buf = Buf & "thirteen" Case 14: Buf = Buf & "fourteen" Case 15: Buf = Buf & "fifteen" Case 16: Buf = Buf & "sixteen" Case 17: Buf = Buf & "seventeen" Case 18: Buf = Buf & "eighteen" Case 19: Buf = Buf & "nineteen" End Select EnglishDigitGroup = Buf End Function
I created an Excel/PowerPoint chart and I don’t want the zeros to be graphed. I clicked the option to ”not plot empty cells” but I am using a formula to create the data. Because the cell contains a formula, Excel is not recognizing this as an empty cell and plots it as zero. How can I change the chart so it does not to plot the formula as “zero”?
Add an “if” statement with “NA()” to your formula.
Example - if cell A1 contains zero, use this formula
The cell will now show #N/A. Excel and Powerpoint charts do not graph #N/A’s
Drew Moldane came up with this trick to automate adding manual labels to PowerPoint graphs/charts. This is great for displaying related data in a straight line on any chart. It also displays words that can’t otherwise be graphed. He won the high five Buddha … since this is a huge time-saver!
Please see below for the step-by-step walkthrough on how to convert manually added labels to linked labels. Please let me know if any of the steps don’t make sense or if you’re following along and I’ve missed something and I’ll correct. As I’m sure you know with these things, what might make perfect sense to me may come across confusing to the reader, so let me know if that is the case …hope this is helpful!
1. Click anywhere within the chart
2. Click “Select Data” from the Design tab
3. Click “Add” in the Legend Entries (Series) box
4. Select the data that you want to appear as labels (this data may need to be manually added to the PPT chart excel sheet and then linked to your earnings supplement)
5. Edit the Horizontal (Category) Axis Labels
6. Select the numeric data for the Axis label range. Traditionally this data would be the dates
7. The “Select Data” box should look like the below screenshot, with CV added as a new Legend Entries and the CV values ($64, $61, etc) as the Horizontal Axis Labels
8. Return to the PPT and click anywhere within the chart
9. From the Layout tab “Current Selection” drop-down box, select the newly created Series and click “Format Selection”
10. In the new Format Data Series box, plot the series on the Secondary axis. Click OK and ignore the effects on the chart
11. From the Layout tab, click AxesSecondary Horizontal AxisShow Left to Right Axis and ignore the effects on the chart
12. From the Layout tab “Current Selection” drop-down box, select Secondary Horizontal (Category) Axis and click “Format Selection”
13. Format Axis Window:
a. Axis Options: select Major/Minor tick mark type: None, and leave the Axis labels: Next to Axis
b. Number: enter the custom Format Code below (this will create the M in the $xxM),uncheck Linked to source and click Add. There are various format codes for %, +/-, etc
c. Fill: No fill
d. Line Color: No line
14. Click Close. With the same Secondary Horizontal (Category) Axis selected, click the Home tab and format the Font Face/Size/Color/Bold to match the current format (most used color scheme settings below)
15. From the Layout tab “Current Selection” drop-down box, select Secondary Vertical (Value) Axis and click “Format Selection”
16. Format Axis Window:
a. Axis Options as shown below. Where the horizontal axis crosses should be the ONLY recurring manual step. This value will have to be updated depending on how the numbers shift. Pay attention to the Min/Max/Major unit on this page, as it will tell you about where the axis will cross and where it will show up on your chart
b. Fill: No fill
c. Line Color: No line
d. Click Close
17. Delete the current data labels / text boxes and voila! You now have labels that update automatically with the rest of your charts. No more nudging to get them to line up properly / remembering to update them! You still need to keep the “Contract Value = “ data label in place
18. Note: there are slight variations that have to be made for different chart types (%ages, etc), but the basic steps are the same
Question: How do I change the pivot table so that if I have 2 or more row labels, they can be displayed side by side, in different columns vs. stacked in one column? In other words, please make my Pivot Table look like it did when I used Excel 2003.
My guess is that your pivot table looks something like this:
And you want them to look like this, with the Labels in separate columns:
Just right click on the pivot table and select “PivotTable Options”
Then check the box next to “Classic Pivot Table layout” and click “OK”
Your Pivot Table now looks like this:
Thanks to Rachelle for helping me figure this one out. In Excel 2007, pivot tables have a lot of new options, but some of the old functions seem to be missing.
For example, In the below table … I wanted to change the format so that the MRR and Term Values are stacked in the Pivot Table vs. being displayed side by side. In 2003, you could accomplish this by dragging and dropping the titles. In 2007, dragging and dropping fields no longer works.
In Excel 2007 right click “Values” >select “Move Values to”>select “Move Values to Rows”
Your values will now be stacked like this:
Another option is to use the Field List and drag the values from Columns to Rows:
It makes me crazy when I have to complete a mail merge and the numbers that are so nicely formated in Excel turn into unformatted numbers in Word’s mail merge. This can be solved by using the TEXT function, for example, if I wanted 1000 to show up like $1,000.00 in my mail merge document, I would use the below formula (where cell A1has the number that I want to be formatted as currency in my mail merge document.)
The next challenge is when I want to proceed the $1,000.00 with the words “One Thousand Dollars and No Cents”. I can make this happen using the following formula (where cell A1has the number that you want to be formatted as text in your mail merge document)
But not so fast. That formula only works after you have added this macro: http://support.microsoft.com/kb/210586
Function ConvertCurrencyToEnglish (ByVal MyNumber) Dim Temp Dim Dollars, Cents Dim DecimalPlace, Count ReDim Place(9) As String Place(2) = " Thousand " Place(3) = " Million " Place(4) = " Billion " Place(5) = " Trillion " ' Convert MyNumber to a string, trimming extra spaces. MyNumber = Trim(Str(MyNumber)) ' Find decimal place. DecimalPlace = InStr(MyNumber, ".") ' If we find decimal place... If DecimalPlace > 0 Then ' Convert cents Temp = Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2) Cents = ConvertTens(Temp) ' Strip off cents from remainder to convert. MyNumber = Trim(Left(MyNumber, DecimalPlace - 1)) End If Count = 1 Do While MyNumber <> "" ' Convert last 3 digits of MyNumber to English dollars. Temp = ConvertHundreds(Right(MyNumber, 3)) If Temp <> "" Then Dollars = Temp & Place(Count) & Dollars If Len(MyNumber) > 3 Then ' Remove last 3 converted digits from MyNumber. MyNumber = Left(MyNumber, Len(MyNumber) - 3) Else MyNumber = "" End If Count = Count + 1 Loop ' Clean up dollars. Select Case Dollars Case "" Dollars = "No Dollars" Case "One" Dollars = "One Dollar" Case Else Dollars = Dollars & " Dollars" End Select ' Clean up cents. Select Case Cents Case "" Cents = " And No Cents" Case "One" Cents = " And One Cent" Case Else Cents = " And " & Cents & " Cents" End Select ConvertCurrencyToEnglish = Dollars & Cents End Function Private Function ConvertHundreds (ByVal MyNumber) Dim Result As String ' Exit if there is nothing to convert. If Val(MyNumber) = 0 Then Exit Function ' Append leading zeros to number. MyNumber = Right("000" & MyNumber, 3) ' Do we have a hundreds place digit to convert? If Left(MyNumber, 1) <> "0" Then Result = ConvertDigit(Left(MyNumber, 1)) & " Hundred " End If ' Do we have a tens place digit to convert? If Mid(MyNumber, 2, 1) <> "0" Then Result = Result & ConvertTens(Mid(MyNumber, 2)) Else ' If not, then convert the ones place digit. Result = Result & ConvertDigit(Mid(MyNumber, 3)) End If ConvertHundreds = Trim(Result) End Function Private Function ConvertTens (ByVal MyTens) Dim Result As String ' Is value between 10 and 19? If Val(Left(MyTens, 1)) = 1 Then Select Case Val(MyTens) Case 10: Result = "Ten" Case 11: Result = "Eleven" Case 12: Result = "Twelve" Case 13: Result = "Thirteen" Case 14: Result = "Fourteen" Case 15: Result = "Fifteen" Case 16: Result = "Sixteen" Case 17: Result = "Seventeen" Case 18: Result = "Eighteen" Case 19: Result = "Nineteen" Case Else End Select Else ' .. otherwise it's between 20 and 99. Select Case Val(Left(MyTens, 1)) Case 2: Result = "Twenty " Case 3: Result = "Thirty " Case 4: Result = "Forty " Case 5: Result = "Fifty " Case 6: Result = "Sixty " Case 7: Result = "Seventy " Case 8: Result = "Eighty " Case 9: Result = "Ninety " Case Else End Select ' Convert ones place digit. Result = Result & ConvertDigit(Right(MyTens, 1)) End If ConvertTens = Result End Function Private Function ConvertDigit (ByVal MyDigit) Select Case Val(MyDigit) Case 1: ConvertDigit = "One" Case 2: ConvertDigit = "Two" Case 3: ConvertDigit = "Three" Case 4: ConvertDigit = "Four" Case 5: ConvertDigit = "Five" Case 6: ConvertDigit = "Six" Case 7: ConvertDigit = "Seven" Case 8: ConvertDigit = "Eight" Case 9: ConvertDigit = "Nine" Case Else: ConvertDigit = "" End Select End Function