Microsoft Office Specialist–First step

As part of my sabbatical I am planning to update my Microsoft Office Specialist certification. Microsoft have changed the way in which this certification can be obtained. Basically there are two stream, Word or Excel. Full details are on the website.

In order to go the Excel route I need to pass two exams for excel, 77-427 and 77-428, one exam for Word (77-418) and choose one from PowerPoint (77-422), Access (77-424), SharePoint (77-419) or OneNote (77-421).

Alternatively it is possible to take two exams in Excel (77-427 and 77-428), two exams in Word (77-425 and 77-426) and one from PowerPoint (77-422), Access (77-424), SharePoint (77-419) or OneNote (77-421).

Note that in the two different scenarios the Word exams are different.

Excel

Since people keep referring to me as the Excel expert I thought I would go the Excel route (although I shy away from the term Expert. There is always someone who knows more than I do and the word reminds me of a has been (ex) and a drip under pressure (spurt)).

Anyway, today saw me sit the first two excel exams. 77-427 and 77-428.

These are managed by Certiport and my nearest testing center is in Grove near Wantage. I duly headed down there this morning to take the exams. The initial setup was easy enough and the exam flowed well enough. However, I am glad that there were two monitors as the format of the exam makes it much easier if the questions are on a different screen. Basically, you are presented with a scenario and a project to complete. The exam presents a number of tasks that must be completed using the actual application. It is recommended that you save your work regularly, in case the system crashes, and the exam should be saved at the end before clicking on Finish as the last saved version is used to mark the exam.

77-427

I started with this exam and worked through it OK, finishing with about 15minutes remaining on the clock. I ensured that I had saved everything and then clicked on Finish.

The screen flashed  a few times and then displayed a message that the exam had failed to load properly, or something similar.

After a few minutes of the invigilator on the phone to Certiport it transpired that my work had been saved but that it had been moved to a separate folder for inspection by a real person, as opposed to being marked by computer.

So I shall have to wait a few days for the result.

77-428

Undeterred I set off on the next exam, after a short break.

This one proved a bit more tricky, mainly because it tested functionality and functions that I was not familiar with. I used up the entire 50 minutes and still was not able to complete one question.

Despite this I passed

So that is one down, hopefully two down, and two to do.

Using Windows Phone and Excel to monitor blood glucose

Diabetes record

Last Friday was the last day I was in the office, before the holiday season. On the spur of the moment I went out for lunch with a couple of the other consultants and the guys from the Service Desk. I was struck by how little I knew these guys and vice versa when I tested my blood sugar when my mean arrived. None of them knew that I was diabetic!

One of them, Marko, who has been with us for a year, commented on the fact that I was making a note of my blood sugar in a good old fashioned little notebook. As an IT consultant he thought I should be adopting a more technical approach. And that got me thinking.

I do use an Excel workbook to log my blood sugar readings and insulin doses. I did this regularly when I was first diagnosed and for the first few years after that. But then I got lazy and just made a note on a piece of paper, that hardly ever got entered into a spreadsheet. Over the last year though I have got back into the habit, mainly because I want to get my D1 licence reinstated and then get an FMT 600 so that I can drive cadets round.

So I have an excel workbook. Could I then update this workbook using my Windows phone? It would seem not. While I can open the file on the phone I cannot edit it. A quick Google suggests that this is because the workbook contains formulas or formatting that the phone can’t handle. Quite probably as there are lots of formulas and pivot tables.

So, the next question was: Can I use a simple spreadsheet and copy the data across. Answer: Yes I can. I have a simple, single sheet Excel workbook where I collect the following information:

Month

Date

Time

Blood sugar

Novorapid dose

Levemir dose

Data types

Month

An integer from 1 to 12

Date

An integer from 1 to 31

Time

A decimal that represents the time.

When constructing the original spreadsheet I found that entering times as times was a pain as I needed to use a colon (:) to separate the hours and the minutes, rather than using a period (.), which was more convenient. I therefore constructed a formula that enabled me to enter the time using a period. If the time is in cell 1504 then the formula shown will convert a decimal such as 12.45 into a time such as 12:45:

=TIME(INT(Data!$E1504),100*(Data!$E1504-INT(Data!$E1504)),0)

(where Data is the name of the sheet).

To make life simpler still I record the time to the nearest quarter of an hour (00, 15, 30, 45).

Blood Sugar

My blood sugar reading in mmol / l. A decimal value.

Novorapid dose

I inject Novorapid before each meal. I adjust the dose based on my blood sugar reading and my expected carbohydrate load.

Levemir dose

I inject Levemir at night. I keep the dose regular. Currently I inject 50 units.

Transferring the data

I use my phone to collect this data and the file is stored online using my OneDrive.

The main blood sugar record file is also stored on OneDrive so I can access either of them from wherever I am.

I can them copy the latest data from the simple worksheet to the main record.

To make matters even smoother, I have created a macro that runs from the main record. This code:

1. Locates the last row in the data worksheet
2. Opens the simple recording worksheet
3. Locates the range of data
4. Copies that data and pastes it into the next available row on the main data worksheet.
5. Deletes all the data from the simple worksheet.
6. Saves and closes the simple worksheet.

Analysis

An overview of the analysis done by my diabetes record:

Formulas look at the date and time and work out whether the reading refers to:

• AM or PM
• Weekend or Weekday
• Which weekday

Pivot tables are then used to plot:

• Average blood sugar and Novorapid dose month on month
• Average blood sugar and Novorapid dose by weekday
• Distribution of blood sugar readings

Slicers are used to further filter the data.

Excel Charts – Trends, targets and highlights

I’ve been reading up on excel charts recently, so I thought I would share a few tips on creating Excel charts. In this post I want to look at three main elements:

• Trends
• Targets
• Highlights

Trends

A large amount of the data that we want to look at is time phased. It is therefore useful to be able to plot the data in ways that make it easy to spot trends over time.

Since we are used to thinking of time as linear, line charts are often the best way of plotting time phased data. For example if we have total work done per day:

Then we can plot this on a chart:

This is a line chart, that shows the full six months worth of data. However, if we apply a filter to the data, by selecting the Data tab and then clicking on the Filter button, we can specify that we only want to view data from, for example, a particular month.

The chart will update to show only the selected data. If your chart continues to show all the data then follow these steps:

1. Select the chart
2. Select the Chart Tools – Design tab
3. In the Data group, click Select Data to display the Select Data Source dialogue box
4. Click the Hidden and Empty cells button to display the Hidden and Empty cells settings dialogue box
5. Ensure the Show data in hidden rows and columns check box is not selected
6. Click OK
7. Click OK

Targets

For a particular set of data we may want to set a target. For example if we look at Resource Utilisation, we may aim for 80% utilisation.

Our initial data and chart may look like this:

If we are using a pivot table as the source then the data may well already be in this nested format. If not then excel is clever enough to plot the chart based on our nested data.

To show the performance against the target of 80% we can add another data series to the chart.

In order to add this additional column to the chart:

1. Select the range E1:E25
2. Do Ctrl+C to copy this data
3. Click on the Chart area to select the chart
4. Do Ctrl+V to paste the data in to the chart

The series can now be formatted as desired.

Highlights

This time we want the chart to automatically pick out the month with the highest, or perhaps lowest, resource utilisation. We can do this by again adding another data series, but this time controlling the value of each data point using a formula.

Essentially the formula says that if the utilisation for the current month is the highest utilisation over the two year period, then show that value, other wise leave it at zero.

When this new series is added to the chart and formatted as a column then only one column is visible:

The lowest point could also be found using a similar formula.

=IF(D2=MIN($D$2:$D$25),D2,0)

This could be combined with the previous data to produce the following:

Project Server Business Intelligence Miscellanea

Introduction

Some miscellaneous thoughts and findings while exploring the business intelligence functionality of Project Server 2013

Excel 2013

In Excel 2010 it was possible to format a series in a chart separately from the other series to create a mixed column and line chart. This option to format each series separately has been removed from Excel 2013. However, it is still possible to replicate this effect by changing the whole chart type to Combo.

To change to a Combo chart type:

1. Select your chart
2. Click on the Design tab and then click on the Change Chart Type button

   

3. This will open the Chart Types dialogue box. At the bottom of the list of options on the left hand side, click on Combo to select it.

   

4. For each series in the chart, use the drop down lists to specify the chart type to use.
5. Check the box on the right to display a secondary axis (for example if there is a big difference between the values for different series).
6. Click OK

 

Creating Reports that use different data sources

I would like to create a report, using excel services that shows top level information about projects within selected programmes. To that end I create a report using the OlapPortfolioAnalyzer template and slice Baseline Work, Actual Work and Remaining Work by Programme and Project. I have also added slicers to enable me to filter by Year and Quarter if required. This works fine.

I can also create a data connection using oData to connect to the Project Web App database directly and pull back data related to calculated project fields, such as RAG indicators. I can add a slice to this to also filter by Programme.

There is a slicer in each case that enables the user to filter by the project level custom field: Programme.

However, presumably because one set of data comes from the OLAP cube and one set comes from oData I have not been able to find a way to link them together, so that both charts can be updated using the same slicer.

I need to investigate whether I can combine the data sources using PowerPivot.