OFFSET( ) in practice

1. OFFSET( ) in practice

Now that you’ve had your first hands-on experience with the OFFSET function, let’s see its applications together in practice! In the worksheet we’ve got a detailed data table of Quantity sold per day and country as well as the incomplete “Executive summary” table above. The goal is to build a dynamic tool which calculates the missing values based on the end-user selections of defined period and country. Notice the drop down lists here and here. Let’s start off by using OFFSET in a simple way. To find last day’s sales for the UK, we need to input an OFFSET function, based on a reference cell, e.g, B10. The desired value is one row down and one cell down, so the function is OFFSET B10, 1 and 1. Next, we will remove the hard coded reference to the row, and replace it by the value in cell C5. A little test. It works. Now, let’s prepare the sum for the last 90 days. We need to create a sum function, where the first ingredient is the first row, so OFFSET, B10 1 and 1, then we put a colon and we complete the sum with the final ingredient, the value offset by 90 rows: OFFSET, B10, 90, 1. Let’s replace the hard coded number 90 to refer to user populated cell C6 in the second ingredient of the sum. Let’s test it. It works as well! Now, we will make it even more dynamic. Let’s make sure that the calculation is correct for each selected country. Since the country names are in the columns, we will need to use the MATCH function to tell us in which column we need to look. Let’s start by giving these cells a Named Range, Countries. Now, we will have to replace the hard coded reference to the columns, with a Match function. Let’s start with the first calculation. We will replace 1 with MATCH, reference to cell D4 and Named Range Countries. By typing 0 we ensure the perfect match. Splendid. We will copy the calculation right, Hmm... not right. We forgot to lock the references of the critical cells B10 and C5. Let’s correct it. Great. We will now remove the hard coded references with MATCH functions also for the Last day's calculation. We will simply copy and paste the MATCH ingredient: to both elements of the sum calculation. This time we remember to lock in the critical cells B10 and C6 and we can drag the calculation right. Finally we see the calculation popping up. The contribution of the selected country’s sales in all sales. The UK accounts for a lot! Let’s see France over the last 100 days? Just 4 %! Itching to visualize this data? We will certainly do it in the following exercises.

2. Let's practice!

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.