Layout Tips Part 4

Quick reference

• 00:04 And so we move on to part four of Layout Tips.
• 00:10 We've put headings and subheadings in and
• 00:12 now what I'm looking at doing is putting some data in like in the example here.
• 00:19 So this session's all about data and I'm gonna put the data in this range here.
• 00:26 Notice I've highlighted already from date one, to date five, for
• 00:28 the data here underneath heading three in this first section.
• 00:32 That's all highlighted, so
• 00:34 I can enter a formula consistently in that range, in one go.
• 00:39 And I'm going to show you a little trick here for testing models sometimes,
• 00:43 that sometimes is useful to actually have random numbers in a model so that you can
• 00:48 just see how that affects the model and whether your logic actually works.
• 00:53 Now the first of the two random number generator functions I'm going
• 00:57 to use is not to be confused with South African currency.
• 01:01 If I go up here to the formula bar equals rand open brackets, close brackets.
• 01:07 Rand is the random number generator,
• 01:08 it generates a number between zero and something slightly less than one.
• 01:12 And it requires the open and closed brackets known as parenthesis.
• 01:16 So to distinguish it from a range name that we'll talk about later.
• 01:21 A range names don't have brackets.
• 01:24 Notice there's nothing inside the brackets, they're called arguments.
• 01:28 So, here you go, you're having no argument from me for
• 01:30 the time being, drum roll please.
• 01:33 This has to be put in here, If I press the control button down, keep that completely
• 01:39 held down while I press the enter, it will populate that entire range, voila.
• 01:44 Now, if you do this, you won't get the same numbers funny enough
• 01:48 because it's a random number generator.
• 01:50 So, tonight's homework is to keep generating random numbers until you get
• 01:54 exactly the same numbers as me, you could be a while.
• 01:59 Now, the problem is that if this were revenue or something like this,
• 02:02 having a random number generator between zero and
• 02:05 one is probably not a good thing for a business planning perspective.
• 02:09 As it looks like your business is in trouble unless you're importing in
• 02:13 trillions.
• 02:14 So, often what we might do is we might multiply it by a scaler.
• 02:17 We might go for something large like that and hold Control and
• 02:22 press Enter that looks a little bit more reasonable for example.
• 02:25 That's one way you can do it, if you want to generate a random whole number between
• 02:30 two variables, there's another function you can use instead.
• 02:34 And that's equal to The randBetween function,
• 02:39 and you have to put the two numbers in you want it to randomize between.
• 02:44 Obviously, the smaller number goes first,
• 02:46 this is going to generate a random whole number between 10 and 99.
• 02:49 Hold the control button down, press enter, voila, I have got some numbers.
• 02:55 Now, if I were just working normally here,
• 02:58 I could press the F9 function key here and those numbers would change.
• 03:04 Because of the recording software I'm using here, that's not going to
• 03:08 work here cuz the function key is used for something else, alas.
• 03:12 But if I just click on this cell here, and
• 03:14 press Go into the formula bar and press enter, watch what happens.
• 03:19 Not just the 70, but to the numbers around it.
• 03:22 Do you see they all change?
• 03:24 This is what we call a volatile function.
• 03:28 It means every time you do something, the formula will update and
• 03:32 it will recalculate.
• 03:34 Now, Excel doesn't normally work like that, it works out what you've typed in,
• 03:38 works out what that affects and only calculates those things.
• 03:42 Volatile functions think even though I may not be near this cell,
• 03:46 if I've done something somewhere else I better recalculate this, because I'm
• 03:50 a little schizophrenic, apologies to any schizophrenics in the audience.
• 03:54 So, this is a way of re-generating random numbers to test.
• 03:59 Now one of the things I often see in a model is stuff like this, and
• 04:03 as it's an input I should perhaps formatting that we're going to talk about
• 04:08 that in a minute or two, in the next session rather.
• 04:11 What I'm going to do there, I'm going to put some labels in.
• 04:14 So I might want to widen this column here, so I just want a bit more space.
• 04:19 How often do you see numbers and you don't know what it's in?
• 04:22 This could be in dollars, dollars thousand, yen, pounds.
• 04:25 Sterling, kilograms, maybe slashed dolphins,
• 04:29 I don't know what it is, so we should put the units in.
• 04:33 I'm gonna put the units in here, let's be unimaginative and
• 04:35 use dollars here, notice how that's changed in the numbers over here.
• 04:40 And what I'm going to do is, I'm gonna color it a slightly different color.
• 04:43 Maybe that's a little too light, maybe a little darker like that.
• 04:47 So, you can see what the units are, though it's not detracting from the rest.
• 04:51 I can copy this whole block down a couple of times,
• 04:55 this is the idea of consistent layout.
• 04:57 Make it easy to do this.
• 05:01 And I have got a something that's starting to look more like a model.
• 05:05 Look at that, and if I want to select the numbers in here, well.
• 05:12 I can do CtrlShift right arrow, CtrlShift down does that or,
• 05:16 as I said before, Ctrl A sets the whole range.
• 05:20 That's why I have got this moat here around it,
• 05:22 that will make sure that I don't select something else instead.
• 05:27 This is the whole idea of layout in a model,okay.
• 05:31 So we come back next time I am gonna talk about
• 05:33 the importance of putting formats in here and some of the alternatives to formatting

Lesson notes are only available for subscribers.