Archive

Posts Tagged ‘MS-Excel’

Line chart of GINI coefficients for select economies since 1980

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

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:
```Sub ccPropertiesPrint()
On Error Resume Next
Dim count As Integer
Dim response

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:

Dim xmlDoc As MSXML2.DOMDocument60
'here it is already fixed? Dim xmlDoc As MSXML2.DOMDocument6

Dim xmlStyle As MSXML2.DOMDocument60

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

Dim xmlDoc As MSXML2.DOMDocument60
'trp: Dim xmlDoc As MSXML2.DOMDocument

Set xmlDoc = New MSXML2.DOMDocument60
'trp: New MSXML2.DOMDocument

```
5. ```test_writetofile():

Set xmlDoc = New MSXML2.DOMDocument60
'here it is already fixed, and here

Set xmlStyle = New MSXML2.DOMDocument60

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. Viola. And I sure like me some Seinfeld references in my sample data….

How to sort a Pivot table by a calculated field column

(Click on image for original size version, now bigger.)

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. Comes with a nifty zoom:
4. Like with Bing Maps Visualization, the weighting is surprising:

First steps with MS-Calendar Analytics

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. “Enter a start date and end date for the timeframes for the Dashboard to reflect”/
4. “The interface will prompt you for the user’s aliases to add.  You can add multiple.”
5. “Type in users’ aliases of whom you have permission (i.e. “Full details”) to access their Outlook calendar and click Add”
6.
7. Do not use the fully qualified email address for the alias, or else:
8. You cannot comma delimit  (so AutoIt to the rescue).
9. You can continue after such a non-fatal error:
10. Is your “Update all” greyed out ? Restart Excel.
11. Accept the offer to upconvert:
12. 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 ?
13. Not that here upconversion to the new powerpivot version does not “take”:
14. If after a restart of excel your powerpivot tab on the ribbon  does not show, reenable the powerpivot addin.
15. How to reenable: excel file/ options / select dropwdown: addins, button:go, check the Checkbox  in front of powerpivot:
16. 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:
17. Delete the key and restart excel, your powerpivot ribbon tab =should be back. Hope this will last?
18. Now updating does something:
19. This opens the powerpivot window with the meeting data collected fro mms-exchange:
20. Which can be interactively: visualized in the calendaranalytics sheet
21. More results here.