Countif and Countifs

I thought I would share some details about the Countif function that I learnt today.

The COUNTIF function counts the number of cells in a single range that meet a single criteria. It takes the form

=COUNTIF(range, criteria)

range can be a single cell, a single column, a single row or a range of x cells by y cells.

criteria can be a number (e.g. 3), a comparison (e.g. <=3), a cell (e.g. C3) or a word (e.g. Alan).

So, for example if we have a list of goods types, the dates each one was sold, the number of items sold and the total cost, we could create a table such as this one;

In order to count on how many days Beverages were sold we could create a Countif function like this:

Where the Range is the column labelled Good Type in the Table named Table2. The Criteria is the value shown in cell I4 (i.e. Beverage).

We may want to count how many days we sold more than 30 of an item. So we would want to count how many times the number in the Items column was more than 30. In this case the function would be.

Here the criteria is written as “>=30”.

Choosing a value

But what happens if we want to change the value 30 to something else?

We may want the function to refer to a value in a cell and use the cell reference rather than the value.

If we write the criteria as “>=J8” then the result is zero.

In order for the function to work properly we need to concatenate the operator with the cell reference:

“>=” & J8

This allows the value in cell J8 to be changed so that the result of the function updates cell L8 automatically.

COUNTIFS

Where COUNTIF allows you to use one criteria and one range, the function COUNTIFS enables you to check several criteria against several ranges. Note that each subsequent range must have the same dimensions as the first one.

So, for example, to count the number of times when more than 30 items were sold for a cost of more than £1000 it is possible to create a function like this:

However, if we want to change the number of items and the total cost then we could create a small table.

We would then need to modify the function to concatenate the operators with the cell references.

The High Item and High Cost values could then be updated to change the Count value.

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.

MCT Renewal. It it worth it?

After several reminders from Microsoft, I have spent the last few minutes going through the process to renew my MCT status. Despite my long term plans to move into environmental science I was planning to keep my MCT status active as training on the Office products has been a big part of my life for the last 15 years or so, and after all it means that I am a Microsoft Certified Trainer, surely that must count for quite a bit?

I’m on a sort of sabbatical at the moment, and have not very much money coming in, so when I got to the screen that told me it would cost £455 to renew my MCT I had to decide; is it worth it?

Well is it?

The short answer is no.

If I thought I was going to spend the next six months delivering Microsoft courses on a regular basis then the answer might be yes.

However, any training that I do deliver is more likely to be a bespoke course, tailored to meet the demands of the client. The Microsoft Office Specialist qualification that I am working towards should be enough to demonstrate to a client that I know what I am talking about.

So, sorry Microsoft, but I will not be renewing my MCT at this point. (Unless any clients want to pay the fee for me so that they can say they have an MCT on their books.)

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.

Changing the image used in EPTs

A question that arises quite regularly on the Project Server configuration courses, is:

Is it possible to change the icon used for the Enterprise Project Types?

These icons are the ones that appear next to the name of each EPT on the New button in Project Center.

The out of the box EPTs have the following image URL:

The folder that stores these images may be accessed by connecting to the server itself, by remote desktop connection, and then navigating to the following address:

C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\TEMPLATE\LAYOUTS\INC\PWA\IMAGES

This folder contains a number of images, three of which begin with the word “Center”:

It is therefore possible to copy one of these images, rename it and edit the image, using something like Paint.

The new image can then be used for existing and new EPTs.

An error occurred. You cannot edit the team at this time

The full text of this message is:

“An error occurred.

You cannot edit the team at this time. Verify that Project is still connected to the server and try again”

We have a training environment set up in the office, running on a separate sub-domain. This means that we can access the training environment easily enough using the training machines, but we need to connect via remote desktop from our own laptops, whether we are working in the office or from home. I was working from home yesterday, connected to the training environment using remote desktop connection.

I could open Microsoft Project Professional 2013, create tasks, link them, save, publish and close the project, all with no problems at all. However, as soon as I tried to build a team of resources the above message appeared. I tried to search on the web for it but with no luck.

In the office today I raised it with our support team, who were able to come up with a solution:

Create another connection to the Project Server from Microsoft Project Professional.

So using my original account, TCPS, I get the message as shown above.

However, by adding another account, using exactly the same details;

It works perfectly:

It is then possible to remove the first account, that was causing the problems, and to change the properties of the second account. For example, deleting TCPS, then renaming TCPS2 to TCPS.

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:

The Importance of Consistency

This post comments on a number of elements of Project Professional 2013:

• Finish Variance
• Resource Substitution Wizard

Finish Variance

If I describe a number, x, as being the difference between two number, A and B, then I take it to mean that:

x = A – B

Finish Variance is defined in Microsoft Project Professional as being the difference between the Baseline Finish date of a task or assignment and the current Finish date:

In my mind that implies that:

Finish Var = Baseline Finish – Finish

However, the bottom of the tool tip shows:

Which I would describe as the difference between the Finish date and the Baseline Finish for a task or assignment.

Which is it?

A quick check of a Gannt chart shows that it is, in fact the second option:

Finish Variance = Finish – Baseline Finish

Why does it matter?

It matters because it is important that everyone understands terms like this to mean the same thing. Does a positive finish variance mean that a task is ahead of schedule or behind schedule?

It is apparent from the Gantt chart above that a positive finish variance means that a task is behind schedule, and that a negative finish variance means that a task is ahead of schedule.

Its when questions around this sort of thing appear in tests and exams that it becomes important to remember the definition.

Finish Variance = Finish – Baseline Finish

If Finish Variance > 0 then task is delayed

Resource Substitution Wizard

In Project Professional 2010, connected to Project Server 2010, it was possible for project managers to assign resources to tasks and then specify whether that assignment was a Request or a Demand, depending on their view of which resources could carry out that task. Running the Resource Substitution Wizard would then attempt to level resources.

The Resource Substitution Wizard is no longer available in Project Professional 2013, so one would assume that there would be no need to be able to specify a Request or Demand field for resource assignments. However, if we look at a Task Form this is exactly what we see:

The assignment of a named resource (e.g. Chris, or PM2) can be set to Request or Demand. However, the assignment of the generic Business Analyst can only be left blank.

It seems that selecting any of the options under the R/D column has effect on anything in Project Server 2013.

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.

Project Server Plus–a new offering from CPS

The company I work for, CPS, has launched a new product: “Project Server Plus”.

This is a new, low cost, preconfigured solution designed to get an organisation up and running with Project Server as quickly as possible.

The structured, user friendly platform quickly drives greater project visibility, improves efficiency and enhances reporting.

For a low initial investment an organisation gets a short engagement that will result in an environment that provides a fully configured project server environment.

For more information please visit http://www.cps.co.uk