Archive

Posts Tagged ‘MS-Excel’

Line chart of GINI coefficients for select economies since 1980

select-gini-coefficients-excel-chart

Based on this Dataset from the World Bank, a bit more recent than the Wikipedia Chart currently.

Categories: Charts Tags: , ,

A little addendum about data types to the +N(“comment”) in-cell formula comment trick in Excel

    1. You are relying with this well-documented trick on the fact that addition of 0 will altering a numeric value.
    2. However, what is not mentioned: not all work in Excel involves the number data type.
      1. Try adding your comment or zero to a string data type, and you will get a #VALUE error.
      2. Boolean, however, works also, since – as you can see when you have Excel evaluate your formula – if  you try adding 0 to a Boolean data type, Excel casts the prior Boolean FALSE to 0 or TRUE to 1, and casts the result of your addition (of 0 = nothing) back to the same initial Boolean for further evaluation. excel-evaluate-fromula-w-n-comment boolean
      3. So just put your +N()-comments in those part of your formula that involve either number or Boolean data type, and avoid adding to strings, for example:

=IF(
    OR(
        ISBLANK(
            [Related issues]
        ),
        (            ISERR(
                FIND(
                    #,
                    [Related issues]
                )
            ) )
    ) +
    N(
        do nothing if no related task in col:related issues
    ),
    ,
    IF(
        NOT(
            ISERR(
                FIND(
                    ,,
                    [Related issues]
                )
            )
        ) +
        N(
            do nothing if no 2nd value in col:related issues
        ),
        CONCATENATE(
            MID(
                MID(
                    [Related issues],
                    FIND(
                        ,,
                        [Related issues]
                    ) + 2,
                    LEN(
                        [Related issues]
                    ) 
                    FIND(
                        ,,
                        [Related issues]
                    ) + 1 +
                    N(
                        extracted the 2nd value from col:related issues
                    )
                ),
                FIND(
                    #,
                    MID(
                        [Related issues],
                        FIND(
                            ,,
                            [Related issues]
                        ) + 2,
                        LEN(
                            [Related issues]
                        ) 
                        FIND(
                            ,,
                            [Related issues]
                        ) + 1
                    )
                ) + 1,
                4
            ) +
            N(
                extracted the 4# of id of related issue
            ),
            MID(
                MID(
                    [Relatedissues],
                    FIND(
                        ,,
                        [Relatedissues]
                    ) + 2,
                    LEN(
                        [Relatedissues]
                    ) 
                    FIND(
                        ,,
                        [Relatedissues]
                    ) + 1
                ),
                FIND(
                     ,
                    MID(
                        [Related issues],
                        FIND(
                            ,,
                            [Related issues]
                        ) + 2,
                        LEN(
                            [Related issues]
                        ) 
                        FIND(
                            ,,
                            [Related issues]
                        ) + 1
                    )
                ) + 1,
                2 +
                N(
                    appended to or from to the 4# of id of related issue
                )
            )
        ),
       
    )
)

    1. This splits, extracts and copies values from a none to multi-value column for better sortability: image

VBA script to more easily examine the properties of your Content Control-based forms

  1. The routine loops through the Content Controls and outputs properties as text into the VBA-Editor immediate window,
  2. from where it can be easily converted into an Excel-table which makes it easy t o spot and mark inconsistencies and outright oversights, like so: image
Sub ccPropertiesPrint()
On Error Resume Next
Dim strHeadings, strProps As String
Dim count As Integer
Dim response

strHeadings = strHeadings & "~" & "count"
strHeadings = strHeadings & "~" & "Tag"
strHeadings = strHeadings & "~" & "Title"
strHeadings = strHeadings & "~" & "Type"
strHeadings = strHeadings & "~" & "DefaultTextStyle"
strHeadings = strHeadings & "~" & "Application"
strHeadings = strHeadings & "~" & "BuildingBlockCategory"
strHeadings = strHeadings & "~" & "BuildingBlockType"
'strHeadings = strHeadings & "~" & "Checked"
'strHeadings = strHeadings & "~" & "Creator"
'strHeadings = strHeadings & "~" & "DateCalendarType"
'strHeadings = strHeadings & "~" & "DateDisplayFormat"
'strHeadings = strHeadings & "~" & "DateDisplayLocale"
'strHeadings = strHeadings & "~" & "DateStorageFormat"
'strHeadings = strHeadings & "~" & "DropdownListEntries"
strHeadings = strHeadings & "~" & "ID"
strHeadings = strHeadings & "~" & "LockContentControl"
strHeadings = strHeadings & "~" & "LockContents"
strHeadings = strHeadings & "~" & "MultiLine"
'strHeadings = strHeadings & "~" & "Parent"
strHeadings = strHeadings & "~" & "ParentContentControl"
strHeadings = strHeadings & "~" & "PlaceholderText"
strHeadings = strHeadings & "~" & "Range"
strHeadings = strHeadings & "~" & "ShowingPlaceholderText"
strHeadings = strHeadings & "~" & "Temporary"
'strHeadings = strHeadings & "~" & "XMLMapping"
Debug.Print strHeadings
  If ActiveDocument.ContentControls.count > 0 Then
    For Each oCC In ActiveDocument.ContentControls
    'debug
    'If oCC.Type = 9 Then 'debug: repeating
count = count + 1
If count = 50 Then
'dim response = vbmsgboxresponse
    Exit For
End If
    
' useless cannot access immediate window If count = 15 Then response = MsgBox("paused", vbOKOnly) End If
strProps = "" 'start over
strProps = strProps & "~"
strProps = strProps & count 'CStr(count)
strProps = strProps & "~"
strProps = strProps & CStr(Replace(Replace(oCC.Tag, Chr(13), "#"), Chr(10), "#"))
strProps = strProps & "~"
strProps = strProps & CStr(Replace(Replace(oCC.Title, Chr(13), "#"), Chr(10), "#"))
strProps = strProps & "~"
strProps = strProps & CStr(Replace(Replace(oCC.Type, Chr(13), "#"), Chr(10), "#"))
strProps = strProps & "~"
strProps = strProps & CStr(Replace(Replace(oCC.DefaultTextStyle, Chr(13), "#"), Chr(10), "#"))
strProps = strProps & "~"
strProps = strProps & CStr(Replace(Replace(oCC.Application, Chr(13), "#"), Chr(10), "#"))
strProps = strProps & "~"
strProps = strProps & CStr(Replace(Replace(oCC.BuildingBlockCategory, Chr(13), "#"), Chr(10), "#"))
strProps = strProps & "~"
strProps = strProps & CStr(Replace(Replace(oCC.BuildingBlockType, Chr(13), "#"), Chr(10), "#"))
'strProps = strProps & "~"
'strProps = strProps & CStr(Replace(Replace(oCC.Checked, Chr(13), "#"), Chr(10), "#"))
'strProps = strProps & "~"
'strProps = strProps & CStr(Replace(Replace(oCC.Creator, Chr(13), "#"), Chr(10), "#"))
'strProps = strProps & "~"
'strProps = strProps & CStr(Replace(Replace(oCC.DateCalendarType, Chr(13), "#"), Chr(10), "#"))
'strProps = strProps & "~"
'strProps = strProps & CStr(Replace(Replace(oCC.DateDisplayFormat, Chr(13), "#"), Chr(10), "#"))
'strProps = strProps & "~"
'strProps = strProps & CStr(Replace(Replace(oCC.DateDisplayLocale, Chr(13), "#"), Chr(10), "#"))
'strProps = strProps & "~"
'strProps = strProps & CStr(Replace(Replace(oCC.DateStorageFormat, Chr(13), "#"), Chr(10), "#"))
'strProps = strProps & "~"
'strProps = strProps & CStr(Replace(Replace(oCC.DropdownListEntries.count, Chr(13), "#"), Chr(10), "#"))
strProps = strProps & "~"
strProps = strProps & CStr(Replace(Replace(oCC.ID, Chr(13), "#"), Chr(10), "#"))
strProps = strProps & "~"
strProps = strProps & CStr(Replace(Replace(oCC.LockContentControl, Chr(13), "#"), Chr(10), "#"))
strProps = strProps & "~"
strProps = strProps & CStr(Replace(Replace(oCC.LockContents, Chr(13), "#"), Chr(10), "#"))
strProps = strProps & "~"
strProps = strProps & CStr(Replace(Replace(oCC.MultiLine, Chr(13), "#"), Chr(10), "#"))
'strProps = strProps & "~"
'strProps = strProps & CStr(Replace(Replace(oCC.Parent, Chr(13), "#"), Chr(10), "#"))
strProps = strProps & "~"
strProps = strProps & CStr(Replace(Replace(oCC.ParentContentControl, Chr(13), "#"), Chr(10), "#"))
strProps = strProps & "~"
strProps = strProps & CStr(Replace(Replace(oCC.PlaceholderText, Chr(13), "#"), Chr(10), "#"))
strProps = strProps & "~"
strProps = strProps & CStr(Replace(Replace(oCC.Range, Chr(13), "#"), Chr(10), "#"))
strProps = strProps & "~"
strProps = strProps & CStr(Replace(Replace(oCC.ShowingPlaceholderText, Chr(13), "#"), Chr(10), "#"))
strProps = strProps & "~"
strProps = strProps & CStr(Replace(Replace(oCC.Temporary, Chr(13), "#"), Chr(10), "#"))
'strProps = strProps & "~"
'strProps = strProps & CStr(Replace(Replace(oCC.XMLMapping, Chr(13), "#"), Chr(10), "#"))
Debug.Print vbCrLf & strProps & vbCrLf
'End If 'oCC.Type=9  then 'debug: repeating
 Next
 End If
End Sub

How to get dkRandomdata to run on 64-bit Excel

  1. Dick Kusleika made this very useful random data generator for Excel (more random data sources I plan to list here).
  2. To get this to run on 64-bit Excel 2013, I need to make a few quick changes in the source (which will break on 32-bit Excel):
    1. First, the usual “must be updated for use on 64-bit systems”: , fixed by this (but note the 1st line):
      Private mlParentPtr As LongPtr 'trp:Long 

       

      #If VBA7 Then 

       

      Private Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" _ 
      	(dest As Any, Source As Any, ByVal bytes As Long) 

       

      #Else 

       

      Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" _
      (dest As Any, Source As Any, ByVal bytes As Long) 

       

      #End If 
    2. Then, to fix the various “Type mismatch” errors , e.g. at , change Lomg into PtrLong: in these locations:
      1. above: Private mlParentPtr As LongPtr 'trp:Long 
      2. Private Function ObjFromPtr(ByVal pObj As LongPtr) As Object 
        'trp Private Function ObjFromPtr(ByVal pObj As Long) As Object 
  3. Then I am finally getting to the xmlDoc As MSXML2.DOMDocument error on Windows 8.1, but that has been solved in the comments here earlier this year (also for Windows 8.1, but apparently with Excel 32-bit): I changed MSXML2.DOMDocument to MSXML2.DOMDocument60 where I could:
    1. Dim xmlDoc As MSXML2.DOMDocument60 'trp: Dim xmlDoc As MSXML2.DOMDocument 
    2. WriteToFile: 
      
      
      
      1. Dim xmlDoc As MSXML2.DOMDocument60 
        'here it is already fixed? Dim xmlDoc As MSXML2.DOMDocument6 
      2. Dim xmlStyle As MSXML2.DOMDocument60 
      3. Dim xmlOutput As MSXML2.DOMDocument60 

    3. LoadFromFile(): Dim xmlDoc As MSXML2.DOMDocument60 
      'trp: Dim xmlDoc As MSXML2.DOMDocument 
    4. testxml() (looks like the XML issue has been worked on since the comments): 
      
      
      
      1. Dim xmlDoc As MSXML2.DOMDocument60 
        	'trp: Dim xmlDoc As MSXML2.DOMDocument 
      2. Set xmlDoc = New MSXML2.DOMDocument60 
        	'trp: New MSXML2.DOMDocument 

    5. test_writetofile(): 
      
      
      
      1. Set xmlDoc = New MSXML2.DOMDocument60 
        'here it is already fixed, and here 
      2. Set xmlStyle = New MSXML2.DOMDocument60 
      3. Set xmlOutput = New MSXML2.DOMDocument60 

  4. Finally, if you have trouble getting the add-in buttons to show in Excel 2013 ribbon, close all workbooks, and you may (but I did not) have to restart Excel (KB2761240):
    1. image
    2. Viola. And I sure like me some Seinfeld references in my sample data…. Smiley 

How to sort a Pivot table by a calculated field column

(Click on image for original size version, now bigger.)
excel-pivot-sort-calculated-field

Mapping of Language Student Locations using PowerView

  1. What do our language students call home? Based on a pivot-table that counts zip codes, let PowerView tell you, which is can now distributed as a free add-in for Excel 2013 Prof, but you need to enable it (under File / Options / Add-Ins).
  2. image
  3. Comes with a nifty zoom: image
  4. image
  5. Click, then Hover a data bubble for more info; image
  6. Like with Bing Maps Visualization, the weighting is surprising: image

First steps with MS-Calendar Analytics

2013/06/22 2 comments
  1. After installing Calendar analytics (and, if you do not already have it, PowerPivot), open the Calendaranalytics spreadsheet from your desktop and click on “Refresh Calendar”.
  2. “Enter your full email address.  Users with more than 1 email address may need to try each of them.”
  3. clip_image001
  4. clip_image002
  5. “Enter a start date and end date for the timeframes for the Dashboard to reflect”/
  6. “The interface will prompt you for the user’s aliases to add.  You can add multiple.”
  7. clip_image003
  8. “Type in users’ aliases of whom you have permission (i.e. “Full details”) to access their Outlook calendar and click Add”
  9. clip_image004
  10.  clip_image005
  11. Do not use the fully qualified email address for the alias, or else:
  12. clip_image006
  13. You cannot comma delimit  (so AutoIt to the rescue).
  14. clip_image007
  15. You can continue after such a non-fatal error:
  16. clip_image001
  17. clip_image002
  18. Is your “Update all” greyed out ? Restart Excel.
  19. clip_image003
  20. Accept the offer to upconvert:
  21. clip_image004
  22. clip_image005
  23. clip_image003
  24. Update all becoame available after restart of excel, even with download exceptions – maybe the unsaved update of the spreadsheet form powerpivot 1 was the root cause ?
  25. Not that here upconversion to the new powerpivot version does not “take”:
  26. clip_image006
  27. If after a restart of excel your powerpivot tab on the ribbon  does not show, reenable the powerpivot addin.
  28. How to reenable: excel file/ options / select dropwdown: addins, button:go, check the Checkbox  in front of powerpivot:
  29. clip_image007
  30. What if even after a restart of excel, this does not “take”?  See whether in the registry the key   CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Resiliency\DisabledItems contains the path to your powerpivot add-in,like here:
  31. clip_image008
  32. Delete the key and restart excel, your powerpivot ribbon tab =should be back. Hope this will last?
  33. clip_image009
  34. Now updating does something:
  35. clip_image010
  36. This opens the powerpivot window with the meeting data collected fro mms-exchange:
  37. clip_image011
  38. Which can be interactively: visualized in the calendaranalytics sheet
  39. clip_image012
  40. More results here.