headermask image

Business Tools Blog

Yoda Translater

LOVE THIS!!  Convert any statement into Yoda speak

http://www.yodaspeak.co.uk/index.php

Acting like yoda are you.  Herh herh herh.

Beginning of the Month Function in Excel

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.

  • =EOMONTH(A1,-1)+1
  • In the formula below =EOMONTH(A1,-1), returns the last day of the prior month = 10/31/08
  • Then “+1″ adds 1 day to 10/31/08 = 11/1/08

Photobucket

Note - if you receive a #NAME? error, make sure your addins are turned on.

Excel Tip - changing dates to the End of Month

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:

  • Goto Tools
  • Add-Ins…

Photobucket

  • Select Analysis ToolPak and Analysis ToolPak - VBA
  • Click OK

Photobucket

Next type the EOMONTH command:

  • +EOMONTH(B4,1)
  • The formula takes the date in cell B4, adds 1 month and reports the last day in that month.

Photobucket

Next format the date.

  • Here the Mar-01 date is used.  This would be an appropriate date for a forecasting template.

Photobucket

Then, copy the formula to the right:

  • Click the little black box in the lower right corner of the cell that you wish to copy
  • Then, drag that cell to the right.

Photobucket

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 - graphed

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.

Photobucket

Here’s how you build the chart:

1.  Start by setting up the data sheet:

  • In column B, create boxes and color them.  These will be the legend for your chart
  • You can use the EOMONTH command to create the dates in row 17

Photobucket

2.  Create the graph data (in gray below).  The graph requires 4 bars.

  • Blank bar -  Formula =IF(D20<0,D20,”") If the Net Installs<0, then this bar = Net Installs. If Net Installs>0, then this bar has no value
  • Gross Install1 bar - Formula=IF(D23<0,”",D20) If Blank<0, then this bar has no value, if Blank>0, then this bar=Net Installs.
  • Gross Install2 bar - Formula=IF(D23<0,D18,D19) If Blank<0, then this bar = -Gross Installs, if BLANK>0, then this bar=Disconnects
  • Disconnects bar - Formula=+D19.  This bar=Disconnects

Photobucket

3. Create the chart

  • INSERT >> CHART >> Column >> Stacked Column

Photobucket

  • Select the gray area as the range for the chart

Photobucket

  • Create the Title

Photobucket

  • On the Legend tab, uncheck the Show Legend box
  • Click Finish

Photobucket

Your Chart looks like this:
Photobucket

4. Format Chart Area

  • Remove the chart outline
  • Double click on the line on the outside of the chart
  • Select Border=None and Area=None
  • Click OK

Photobucket

5.  Format Plot area

  • Remove the gray shading in the plot area
  • Double click on outline of the plot area
  • Select Border=None and Area=None
  • Click OK

Photobucket

6.  Format the Y axis, so that it displays currency

  • Double click on the Y axis
  • On the Number tab, click Currency
  • Decimal places=0
  • Symbol=$
  • Negative numbers=($1,234)

Photobucket

7.  Format the X Axis

  • Double click on the X axis
  • On the patterns tab, Tick mark labels=Low

Photobucket

8.  Color the bars

  • Double click on the Disconnect bar
  • Change the color to red
  • Click OK

Photobucket

  • Double Click on the Gross Install1 and Gross Install2 Bars and make them blue (you will have to do this separately
  • Click OK

Photobucket

  • Double click on the Blank bar
  • On the Patterns tab, select Border=None and Area=None

Photobucket

Your chart now looks like this:

Photobucket

Go to Businesstoolsblog Shared Files Google Group for a free template.

Converting numbers to words in excel without the dollars and cents

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)

  • Type [ALT] + F11 to open VBA editor
  • Select Insert > Module from the toolbar
  • Copy and Paste below text:
  • To use the formula type  ”=English(a2)” or “=English(100)”
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 don’t want my excel or PowerPoint chart to plot zeros

Question -

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”?

Answer:

Add an “if” statement with “NA()” to your formula.

Example - if cell A1 contains zero, use this formula

=if(A1=0,NA(),A1)

The cell will now show #N/A.  Excel and Powerpoint charts do not graph #N/A’s

Add manual data labels in PowerPoint graphs/charts

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!

Photobucket

Step-by-Step:

1. Click anywhere within the chart
2. Click “Select Data” from the Design tab
3. Click “Add” in the Legend Entries (Series) box

Photobucket

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)

Photobucket

5. Edit the Horizontal (Category) Axis Labels

Photobucket

6. Select the numeric data for the Axis label range. Traditionally this data would be the dates

Photobucket

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

Photobucket

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”

Photobucket

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 AxesSecondary Horizontal AxisShow Left to Right Axis and ignore the effects on the chart

Photobucket

12. From the Layout tab “Current Selection” drop-down box, select Secondary Horizontal (Category) Axis and click “Format Selection”

Photobucket

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

Photobucket

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)

Photobucket

15. From the Layout tab “Current Selection” drop-down box, select Secondary Vertical (Value) Axis and click “Format Selection”

Photobucket

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

Photobucket

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

Display Pivot Tables with the Row Labels Side by Side instead of in One Column

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:

Photobucket

And you want them to look like this, with the Labels in separate columns:

Photobucket

Just right click on the pivot table and select “PivotTable Options”

Photobucket

Then check the box next to “Classic Pivot Table layout” and click “OK”

Photobucket

Your Pivot Table now looks like this:

Photobucket

Pivot Tables in 2007 - How to Stack Values vs. Displaying Side by Side

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.

Photobucket

In Excel 2007 right click “Values” >select “Move Values to”>select “Move Values to Rows”

Photobucket

Your values will now be stacked like this:

Photobucket

Another option is to use the Field List and drag the values from Columns to Rows:

Photobucket

Convert a Number with Currency in Excel into a Spelled/Written Word for use in Mail Merge

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.)

=TEXT(A1,”$#,##0.00″)

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)

=ConvertCurrencyToEnglish(A1)

But not so fast.  That formula only works after you have added this macro: http://support.microsoft.com/kb/210586

  1. Start Microsoft Excel.
  2. Press ALT+F11 to start the Visual Basic Editor.
  3. On the Insert menu, click Module.
  4. Type the following code into the module sheet.
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