CRaFT - Consistency

Quick reference

• 00:05 And so we begin with the first of our four key qualities consistency.
• 00:12 You may recall from last time out, we talked about the idea of CRaFT, C-R-a-F-T.
• 00:21 CRaFT is made up of four key qualities.
• 00:24 The first of which is consistency which is what the session is about.
• 00:28 So, what do I mean?
• 00:31 Consistency well, I won't get any marks for
• 00:34 writing a dictionary, but it's the aim of being consistent.
• 00:39 Trying to write as few formulae as possible.
• 00:42 Professor Raymond Panko from the University of Hawaii, actually proved that
• 00:47 no matter how experienced we are, we keep a rather consistent error rate going.
• 00:52 Therefore, the fewer formulae we write, the few mistakes we'll make.
• 00:55 And that will promote model integrity and make it easier for developers and
• 00:58 then users alike to understand models more quickly, as they're fewer errors in them.
• 01:04 In order to help us with this, we could actually look at structured models,
• 01:07 using predefined formats and
• 01:09 styles, looking at formula to be copied uniformly across ranges.
• 01:14 I present periodic data in a even spread.
• 01:17 What do I mean?
• 01:19 Well, for formula you have a calculation going consistently across the row.
• 01:23 You type in the first column and then you just copy it across.
• 01:27 It's easy to change and even easier to review.
• 01:31 If you've got different worksheets with time series on there, so,
• 01:35 I will see examples here of historical data.
• 01:38 Notice here that my dates start in column C for both Sheet 1 and Sheet 2.
• 01:43 That will mean if I link from one sheet to another,
• 01:46 I'm more likely to reference the correct column.
• 01:49 Notice as well that I use months in each one.
• 01:51 I don't know months, quarters, years, but
• 01:54 as this would mean different formulas, you don't want that.
• 01:58 Let's have a look at some examples it shows more ideas about consistency.
• 02:04 Let's consider the following examples of consistency.
• 02:07 It's sort of consistent as it looks so ready.
• 02:10 Notice you can easily identify where inputs are.
• 02:14 They're the cells colored yellow with blue font.
• 02:17 It's such an intuitive, that's where you're going to type your numbers.
• 02:21 I'm going to assume that periods 1 to 8, are equal in length.
• 02:25 And you can see that for products A, B, and C in rows 5 to 7,
• 02:29 10 to 12 and 15 to 17.
• 02:31 I've actually gots those consistencies as well.
• 02:34 You know what?
• 02:34 I've made them consistent by not typing them in twice.
• 02:38 Look, we inspecting that I've made them equal to the original references up here.
• 02:45 Don't type things in more than once, makes it easier.
• 02:49 Let's imagine, I want to calculate the sales here in cell 15.
• 02:53 I want a consistent formula.
• 02:56 Well, that's nice and easy.
• 02:58 I'm going to go equals that, the unit price of product A in Period 1,
• 03:02 multiplied by that, the volume for Period 1 for product A too.
• 03:08 Press enter, and I can copy that across on them in the usual way.
• 03:14 That's probably how you do it, want to see a way high can force consistency, nice and
• 03:19 easily, in case you accidentally make a mistake?
• 03:23 Highlight the range first.
• 03:26 So, I'll click off just to show you can highlight the range.
• 03:29 And then type in the formula as if you were in the top left hand corner.
• 03:33 So, it's equals that multiplied by that.
• 03:37 And just press Control Enter, holding the Control button down.
• 03:42 Nice and easy, one consistent formula copied down and across.
• 03:46 See, it's easy to spot if I made a mistake.
• 03:49 And here, I can sum just highlight these.
• 03:53 And I can copy those across too, consistent formula.
• 03:59 How can I check that they're consistent?
• 04:01 I can just highlight to all this, and I can go control-backslash as a keyboard.
• 04:08 No cells were found, what does that mean?
• 04:12 Well, that actually means if I put something else in here,
• 04:15 let's say I made that equal to that, so it's different.
• 04:19 You may not notice that's a different formula.
• 04:21 If I highlight this this range now, control backslash will look for
• 04:26 inconsistent formulas across a row.
• 04:30 Control Shift backslash will offer inconsistent formulas down each column.
• 04:35 But either way, I can spot if there's an inconsistency.
• 04:39 So, ways to make sure you're consistent.
• 04:42 Let's have a look at the next example.
• 04:45 To be consistent if you're going to copy across and
• 04:48 down you need to get the idea of dollar signs, that is anchoring.
• 04:53 I'm going to do a simple times table, where I'm going to
• 04:56 actually multiply the numbers in this row here by the numbers in this column.
• 05:02 And then multiply it by this multiplier.
• 05:05 If I just go equals that, times that, times multiplier,
• 05:12 as I copy it down and across, it will actually go wrong on me when I go through.
• 05:17 What will actually happens I copied across and
• 05:19 down, is it doesn't work because the multiplier.
• 05:22 So look at this, you'll see it's linking to these numbers, It's not anchored them.
• 05:27 A way around this if I just undo, it's when I've actually written
• 05:32 the formula in the first place equals c5 times, b6 times, c2.
• 05:36 They're correct, but I need to anchor them.
• 05:40 Now, I can use either the F4 function key or just type dollar signs in,
• 05:44 dollar signs is a syntax in Excel to make things anchored.
• 05:49 So by pressing the F4 function key here, do you see it toggling the dollar signs?
• 05:55 Everything to the right of the dollar sign is kept constant.
• 05:58 So that means keep column C and row 5 constant that means keep row 5 constant.
• 06:03 That means keep column C constant, that means everything is relative.
• 06:08 So if I go, equals C dollar 5, it won't move from row 5.
• 06:14 Similarly, if I make that dollar B6, it won't move from column B.
• 06:18 If I make that, $c$2, it's always going to link to cell C2.
• 06:24 Then when I copy this across and down,
• 06:28 It doesn't matter where I am, they've always linked to the right place.
• 06:33 This is a key skill you need.

Lesson notes are only available for subscribers.