# Pick a Name at Random from a List

In this blog post we look at how to pick a name at random from a list. There are many reasons you may need this kind of random selection from a list.

It may be a neat way of deciding who is next to do the washing up, or to make the tea :). Or it is useful for randomly drawing teams for a knockout cup competition.

To perform this random selection, we are going to use two different functions together. They are INDEX and RANDBETWEEN.

# TEXT Function Examples and Description

The TEXT function is used to convert a numeric value into text in a specified number format. This function is really useful when concatenating strings that will contain a numeric value.

Take the example below, where the following formula has been entered into cell C2. The date formula loses its format when concatenated. This is now useless.

`=A2&" - "&B2`

# 10 Great Word Productivity Hacks

This blog post looks at some productivity hacks for creating professional, consistent and awesome MS Word documents.

These 10 skills are sure to save time and improve overall efficiency in MS Word.

## 1. Quick Parts and Building Blocks

Building Blocks is a fantastic feature in MS Word where you can store frequently used content for quick insertion to a document.

There are many galleries to the Building Blocks feature including cover pages, tables, headers, footers and text boxes. Quick Parts is the gallery for miscellaneous content that does not fit any of the other galleries.

For example, say I work for a company with 4 different offices. I frequently have to enter the address of these offices into documentation. This entails looking up the correct postcode etc. and then typing and formatting it. With Quick Parts these can be stored for easy re-use. Continue reading

# Excel Timesheet with Different Rates for Shift Work

When creating a timesheet in Excel you may need to calculate different rates of pay. This could be because of overtime, or the type of work being done.

In this tutorial we create a timesheet that uses different rates of pay for working weekends, and also night shifts.

## Calculating Hours Worked

In this timesheet, the day and night shifts are entered on separate rows. This will make it easier for our calculations. A column (column E) is also used to record whether it is a day or night shift.

Our first task is to calculate the number of hours worked on the shift. To do this the following formula can be used. This formula finds the difference between the two times as a decimal, and then multiplies by 24 to convert it to hours.

(D5-B5)*24

If the shift is worked overnight, so you started work on one day but finished the next, then the formula below is used.

# Women’s World Cup 2015 Schedule – Excel Spreadsheet

The women’s World Cup is currently underway in Canada and at the time of writing have just finished the group stages of the competition.

This blog post provides a Women’s World Cup Schedule spreadsheet to automatically calculate the group stage rankings, and knockout stage schedule as the results are entered.

This spreadsheet is ready for use. All you need to do is enter the fixtures and results and it will take care of the rest.

## What’s in the Workbook

The workbook is made up of 7 worksheets. Some of the worksheets are hidden as they are not needed when using the workbook. You can unhide these sheets by right clicking a sheet tab and selecting Unhide.

The following worksheets can be found in this World Cup schedule.

Teams – Stores the list of teams competing in the competition split into their groups.

Data – The number of points awarded for a win and draw in the groups stages.

Group Fixtures – The fixtures list and results during the group stages of the competition.

Calculations – Calculations for the group stage league table statistics such as number of wins, draw, losses goals for etc. It also calculates the teams league table rankings.

Third Place Rankings Table – Used to calculate the four best third placed teams during the group stages. It also calculates which teams they will face in round 16. This was quite complicated calculations and an explanation of how the schedule works can be seen below.

Group League Tables – The final group stage league tables. The VLOOKUP function is used to pull the information from the Calculations sheet, and use the ranking to list the teams in the correct order.

Knockout Stages – The fixtures and results for the knockout stage of the tournament. Formulas are used to determine which team progresses to the next round as results are entered.

## How the Women’s World Cup Schedule Works

The first round is a group stage. Twenty four teams are divided into six groups of four teams. Each team plays one match against the other teams of the group. The group winners and runners-up progress along with the best four third-placed teams.

The ranking for each team in the group is determined by points, then by goal difference and then by goals scored.

The spreadsheet also contains a table to calculate the best four third-placed teams. They are ranked in their own table using the same criteria.

This is similar to other sports tournament schedules I have created in Excel. You can learn all the skills used for this in our online course.

## Scheduling the Best Four Third-Placed Teams

What really made this tournament schedule exciting to create were the tournament regulations for who the best four third-placed teams play in the next round.

These four teams are to be matched with the winners of groups A, B, C and D. However the group winners they play is dependent on which group the best four teams are from.

The image below of the ‘Third Placed Ranking Table’ sheet shows the rank order of the best four third-placed teams and the group winners they are paired against. This schedule is from section 28 of the tournament regulations.

Row 18 displays the top four third-placed teams. A formula was used in column F to then calculate from this the correct schedule to use. This formula made use of the IF, NOT, ISERROR and FIND functions.

This combination was then extracted to a different area of the sheet using INDEX and MATCH. The ‘Knockout Stage’ schedule sheet then pulled the relevant team name across.

# 5 Alternative Reasons to Use COUNTIF Function

The COUNTIF function is one of the most useful functions in Excel. Its job is to provide conditional counting. This is primarily used for analysing data and producing reports and dashboards.

In this blog post will look into 5 more unorthodox but useful scenarios for the COUNTIF function to be used. The 5 COUNTIF examples we look at are;

1. Prevent duplicates in a range.
2. Uniquely rank items in a list.
3. Count the unique entries in a list.
4. Compare two lists.
5. Identify names that occur 3 times or more.

# Sum Negative Values Only in a List

You may have scenario on a spreadsheet where you only want to sum the negative values in a range.

Take the example below for instance, it contains a list of incoming and outgoing payments. If we needed the total for outgoings, we would need to sum the negative values only.

This can be done using the SUMIF function. This function allows us to sum only the values that meet a specific condition (new to SUMIF? find out more).

The formula below will total the outgoings in the list by summing only the values less than 0.

=SUMIF(B2:B12,”<0″)

The criteria has been entered as text in double quotes. When entering criteria like this into the formula it needs to be entered as text.

The criteria specifies that only values less than 0 should be summed.

## Watch the video

PivotTables are one of the most useful tools in Excel. They allow you to easily summarise, examine and present a complex list of data.

This blog post explores 5 advanced PivotTable techniques.

1. Grouping fields by month and year
2. Calculating data as a percentage of the total
3. Using Slicers
4. Applying Conditional Formatting to PivotTable data
5. Creating calculated fields

# Add Drop Lines to a Line Graph in Excel

Add drop lines to a line graph in Excel to connect the data point to its label on the axis. This extra visual chart element can make it easier to view the data on busy charts.

Take the line graph below for instance that shows the call volume for 14 hours of a day. It can be awkward on this chart to quickly view the time of day for a specific data point.