Pick Names at Random from a List – Excel VBA

In this blog post we will explore how to pick names at random from a list using Excel VBA.

Suppose we were picking the names for a draw or competition and needed to generate a list of maybe 3, 5 or any number of names from a list.

These names must be selected at random and be unique. You cannot select the same name more than once.

The macro code shown below can be copied and pasted into a VBA module and adapted for your use. Comments have been used to identify the key and more complex parts of the macro.

This macro uses an array variable to store the names being randomly selected from the list. Every time a name is selected, it is checked against this array to ensure that it has not already been selected. If it has, then another name is randomly selected. Continue reading

Extract the Domain from a List of URL’s

If you are responsible for analysing a list of URL’s in Excel, then you may need to extract the domain from each URL. After extracting the domain you can perform useful reporting tasks such as grouping the domains, or counting the number of unique domains in the list.

This can be quite a difficult task especially if the URL’s are not consistent. In this article we explore a few different approaches to extract the domain from a URL. The method you choose will depend on the appearance of the URL’s in the list and the format you would like to extract the domain in.

This article covers;

  1. Extract the domain including the URL protocol.
  2. Extract the domain ignoring URL protocol.
  3. Extract the domain without the www.
  4. Extract root URL’s that don’t end with a slash (/).
  5. Using helper columns to break up complex formulae.

Continue reading

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.

Continue reading

How to Use the TEXT Function in Excel

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

Date loses formatting when concatenating 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.

Prefer a video tutorial? Skip to the video.

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.

Excel timesheet with different rates for shift work

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.

(1+D5-B5)*24 Continue reading

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.

Download the Womens World Cup spreadsheet.

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.

Womens World Cup Group Stage Tables

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.

Scheduling the best four third-placed teams

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.

Continue reading

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.

Sum 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

5 Advanced PivotTable Techniques

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

Continue reading

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.

Line graph without drop lines

Continue reading