Category Archives: Excel

Visualizing WordPress stats with Excel Power Map

Power Map is a feature available in recent release of Microsoft Excel providing easy to use visualization of data in geographical form as well as temporal animation.

In a sample usage to understand where the readers of this blog are from this year, the WordPress stats data from 2016 are imported into Excel Power Map. WordPress already offered graphical presentation of visitor origin in a nice world map. For animating the data through a whole year by month, Excel Power Map is a nice and easy to use tool with just a little bit more effort to create such animation.

The statistic page should be familiar to all WordPress user. Download the data for each month as CSV and store on local drive.
powermap0

Now prepare the data by consolidating them all in a single Excel worksheet. I used the good ole awk as the source files are in plain text CSV to save some copy and paste efforts. For the animation to work, a new column indicating the time of data (e.g. data for USA in January 2016) has to be added for the Power Map to handle temporal attribute, and this column can not be in formula form.
powermap1a

When the source data are ready, select the whole region and click the 3D Map button under the Insert menu.
powermap2a

Now the Power Map UI will be displayed. A globe map will be displayed by default but since I preferred a flat map form instead of 3D so I clicked on the Flat Map button.
powermap3

The Power Map user interface works pretty much like a pivot table. You drop the dimension on the pre-defined attribute settings pane on the right hand side to tell Power Map how you would like the map to present your data. To create animation, just drag the date column to the “Time” section as the attribute and Power Map will be able to render accordingly.
powermap4

Now that everything is ready, the animation play nicely and Power Map will also be able to create the animation file.powermap5

Doing matrix multiplication in TI Nspire and Excel

In TI Nspire, matrix multiplication can be accomplished in the Calculator page. The process is intuitive and easy with the visual templates.

Firstly use the matrix template to define one:
matrixnspire1matrixnspire2

Then simply do multiplication after filling in the matrix elements.
matrixnspire4

Doing so in Excel required the use of the MMULT() function. A little trick is needed for this function for returning answer in array format – by pressing CTRL+SHIFT+ENTER at the same time after inputting the function for the cells selected as output array.

First step, fill in the matrix with the elements into respective cells.
matrixexcel1

Mark the output region to use the array function. In this example it is also a 3×3 matrix.
matrixexcel2

Apply the MMULT() function on the upper left corner cell of the region selected.
matrixexcel3

The trick is here, when finished typing in the function, press CTRL+SHIFT+ENTER instead of just ENTER. The answer from the function will then be populated in the selected cells, and the formula will be displayed with curly brackets enclosed, like  {=MMULT(B2:D4,F2:H4)}.matrixexcel4

Confidence Interval calculation in TI Nspire and Excel

In TI Nspire, calculation of Student’s t confidence interval can be achieved using data or statistics input. The menu can be accessed from “Statistics > Confidence Interval > t Interval”.
t-confidenceinterval1

In Excel, the same function does not have a separate menu item, but instead is included in the Descriptive Statistics dialog with a check box of “Confidence Level for mean”, and a percentage input box next to it. Unlike in Nspire, lower and upper are not included in the output.
t-confidenceinterval3

Simple Excel conditional RAG icons comparing yearly fluctuations

The conditional icon set in Excel is a great feature for visualizing data values. A simple usage will be showing up / down arrows in red / amber / green color. In its default Excel will do the calculation across the cells to determine which icon to use. This is nice but for some other usage it might be necessary to have some customization and it can be achieved with the respective Conditional Formatting dialog. For some usage, however, the restrictions imposed in this dialog like relative reference prohibited the presentation of certain commonly used intuitions.

For example, the data below depict sales value country-wise. Using automatic icon set in defaults, Excel will not present the desired icons in the way of showing sales moving up or down for each country, but instead it will show the icons by the yearly value.

excelautoicon1

A simple solution is to add a calculation column using the if() function to return either -1, 0, or 1 corresponding to the sales value comparison by country. The auto formatting icon can then be applied to this column. With a final touch of column width setting, the result is nicely presented.

excelautoicon2