Locked lesson.
About this lesson
Layout Tips for using the RANDBETWEEN function to generate random numbers for testing and labelling units.
Exercise files
Download this lesson’s related exercise files.
Layout Tips Part 4.xlsx10.3 KB Layout Tips Part 4 - Solution.xlsx
11.4 KB
Quick reference
Layout Tips Part 4
Layout tips explained using the CRaFT methodology
When to use
Layout tips can assist in building a financial model in excel which is easy to understand and navigate through
Instructions
Layout tips and the use of RANDBETWEEN
- To enter random data in cells J15 – N20 the RANDBETWEEN function can be used
- Highlight the group of cells where the data will be placed
- Type the formula =RANDBETWEEN(2,99) – the numbers can be whatever you chose
- Hold CTRL + ENTER to get a random number in each cell that is highlighted
- RANDBETWEEN is known as a volatile function and therefore excel will recalculate the numbers every time you do something elsewhere in the workbook
- Enter the Units in column F i.e. a dollar sign ($)
- Format these labels to be a lighter colour to the data
- This column can also be widened if necessary
- 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.