[Graham:] Right, next piece of advice.... er if you've got formulae, when you set them up, you should set them up so that you can copy them. Erm here I've got some formulae calculating production figures er we've got different quarters formulae found are somewhat similar er there's a [cough] so that I can copy one to any of the other adjacent cells outside will work I don't have to edit the formula or do anything to it soon as I copy it, it's alright for that cell. So it helps first of all when you're setting up the spreadsheet I suppose you can create one formula then copy it instead of having to edit each one individually, erm but later on you might accidentally or maybe deliberately in some cases overtype a cell and er... if you have to put it back again [sneeze] and what you probably do is copy it from the adjacent cell and then you have to study the formula to try and understand it, and then edit it if it wasn't the copyable formula and, er it may take a little time to edit it but it could take you a lot longer to understand it. At the time of creating the formula you're probably fairly clear about how it works, but several weeks or months later, [cough] you've forgotten... an awful lot. So er, it only takes you a second to er copy from an adjacent cell but it could take you a minute or two to get that formula working again if it's not copyable, erm, it would probably only takes you an extra few seconds er when you're actually building the formula to make it, copyable in the first place so it's well worth investing a few extra seconds up front to save you maybe a minute or so later on. Erm, something else that I may well do with this spreadsheet as well I can foresee is, insert an extra
[speaker002:] [sneeze]
[Graham:] product, we've just got one row for one product at the moment but in the future you might want to insert another row and er the product B and then all I would need to do is just copy the formula down and I know it would work cos I made it copyable down as well. I don't need to copy it down when initially build it but er I might just put a little bit of extra work in I can make so and can copy it down. So make all your formulae copyable left and right and up and down. How do you do that? Er in most cases, the vast majority of cases you erm need to use the absolutes. Are you familiar with using the dollars in the formulae? Are you familiar with er what this C dollar four means? And what does dollar G three mean? What does the dollar G three mean?
[speaker002:] I thought it was just an absolute on one of those not all.
[Graham:] So you put dollar in front of the column or the row or both?
[speaker002:] Yes.
[Graham:] Three options I suppose or neither, I suppose there's four options [LAUGHTER] er so what does the dollar G three mean?
[speaker002:] If it sticks with G then it might go down to G four or G five.
[Graham:] Yes, that's right so if you copy the formula then they erm the G will always stay as a G no matter where you copy it to, but if you copy the formula to another row you copy it from the cell below then the three becomes a four and er this one? Means the four never change if you copied the formula? The C can change if you copy it to another column and the C put it to the right the C changes to a D if you put a dollar in front of them both then neither can change. So for the majority of formulae that's all you need to do to make the formulae copyable, so when I got this extra product to insert just insert the rows, copy the formula down and that change is made very quickly indeed, I don't have to spend a lot of time understanding and editing the formulae.... Our little trap there there is important that you do all the inserts first. [cough] The natural tendency is probably to insert a row, copy it down, insert another row and copy it down, insert a row and copy it down, er, you need to do all your inserting first and then copy your formulae cos otherwise you end up with wrong... formulae. Erm, take this example here, er, I've got sales being calculated, say units, times the price, C four times C six, now I want to add a product B so I might, I might be here and think oh I'll insert a row here and copy the formula and go up to there and insert a row, and so on. But if I do that, well, I copy the C four, C six down have C five, C seven... and then when I go in and insert the extra rows then er, well that formula's not going to refer to those new [cough] rows that I insert it's still [cough] cell the right of price and the right of sales the wrong sales certainly. You must do all the inserts first and then copy down so that you end up with the right formula at the end of the day which is the C five, C eight. [blowing nose] Next topic is on memory. A lot of people are confused about memory as to what it is. They're not quite clear about, a bit vague about what it is. I get people calling me up saying erm they've got a memory call problem and er, I've deleted a number of files off the disk, and the problem didn't go away. Well the disk is quite a separate thing from the memory so let me er try and explain, what the... what the two are. I've got er a file named A B C dot dot U K One on a disk so it could be a hard disk or a floppy disk it doesn't matter and er when I retrieve the file, Lotus takes a copy of that file and places it, places it into what's known as the memory of the computer. Do you know what memory looks like? Have you seen memory, if you saw it would you recognize it?
[speaker002:] Yes.
[Graham:] Inside the computer if you open up the computer you see these little chips, silicon chips they're about a centimetre perhaps in length and you'll see a row of, nine or ten of them, that's physically what the memory is. You might see several rows, depends how much memory your machine's got in it. Erm that's where your file is... yo yo you're retrieving copies of them, removing copies that puts the copy into the memory. So when you're looking at a file on the screen, what you're looking at is the file as it is in the, in the memory of the computer, and if you change your spreadsheet, then all you're doing is changing what's in the memory. You're not changing anything that's on the computer at all. Sorry, not you changing anything that's on the disk. The only time you change what's on the disk, is when you save the file. Lotus then takes a copy of the file that's in the memory and overwrites the one that's on the disk, the only time you change what's on the disk. If you get a memory full error, it means you've run out of space here. If you get a disk full error message, it means that you've run out of space on your disk. So if you get a memory full error, deleting files off your disk, of course, is not going to make any space,. Memory is a bit of a misnomer erm, because if you switch the computer off and switch it back on again, the file won't be there any more, because it hasn't remembered it. The disk, however does remember keeping the file there, if you switch the computer off, your hard disks will keep the file there, floppy disks, of course, keep the files on the disk. So that's why you need to save frequently. There's all sorts of accidents not only having the computer switched off, you might accidentally mess the file up or erase it or whatever, er, you've still got the original copy on the disk so if you keep saving it every ten minutes or so, then you always lose more than ten minutes' work. How do you know how much memory you've got, on your P C?...
[speaker002:] I know I've got enough memory
[Graham:] Yes, so when you're getting low on memory the bottom of the screen the little red M E M appears, telling you you're very low, about to run out, er, so how can you check how much memory you have got when you're in One Two Three? Well if you do worksheet status, actually the screen is a little bit different from two point four, but it's looking similar like this. Er, you get two rows saying conventional memory and expanded memory, and two numbers. That's how much memory you had, to start with, three hundred and twenty thousand... bytes... and that's how much you've got left, two hundred and eight thousand bytes... i if you've got a spreadsheet in memory so it's using up some of the memory. Erm, what's a byte?... Or what can you put in a byte? How much can it hold? Sorry. How many bits?... So many. What's a bit then? You've heard it somewhere or other. Oh that's right, yes, well we'll come to that, yes er, well a byte is a bit not that that means a lot, er, what's a bit? It's a zero or a one, everything in computers is stored as zeros or ones. A bit is an abbreviation, of binary digits something about what it means, erm and eight of them together, makes a byte. A byte is enough space to store a single character, a letter. Now everything in computers is stored away as, er numbers and a certain set of numbers represent all the different characters. An A is a sixty five, and other numbers are sixty five. Er if I remember... erm... so erm, that gives you a indication of how much space you can provide, erm... we've also got some called expanded memory, expanded memory an I'll be explaining the difference between the two. Erm, oh you mentioned er, megabyte and things. What's er... K, you know the word K? What's K? K byte? Kilobyte? What is it? How many bytes does it... It's approximately a thousand.... It's not exactly is it? Does anyone know the precise number?
[speaker002:] A thousand and twenty four.
[Graham:] A thousand and twenty four, yes, and you want one K, equals a thousand and twenty four bytes. [clears throat] There's a strange number, one O two four. Not a strange number to a computer, though. Do you know what's special about one O two four?... It's er
[speaker002:] the power of two
[Graham:] Yes, it's the power of two. It's te it's two to the power of ten. Two times two time two times two. But to a computer it's a very round number. What's the next up from a K? A meg, which is an abbreviation of... megabytes, and i the size of it. What is it? Approximately, that is. Precisely, it's...
[speaker002:] one O two four.
[Graham:] one O two four times... one O two four. It's a bit. You know what the next level up is... after megabyte. It's usually referred to as neg...
[speaker002:] Computer bytes
[Graham:] Yes. Computer bytes. That's right. [cough] Which is what... one O two four. I don't suppose anyone knows the level up? Yes, that's right. How do you know that?
[speaker002:] [clears throat] twenty five years ago. used them.
[Graham:] Oh, right. Right, that's error bytes, yes. Er disks on your P Cs are known, normally measured in megabytes or your memory on your P C might be measured in megabytes. You're using up a lot more disk space than you are memory space. On a typical P C you might have a hundred megabyte disk. Do you know how big your disks are?... Do you know how to find out?... a memory. I don't know what machines you got. Four megabytes is typical these days for memories. They tend to have a lot more disk space than memory. Erm, to find out how much disk space you've got. How big your disk is. How much you've got left. You can type ch disk, C H K D S K er press enter. The disk will whizz around for a few seconds, and then you get the rows of statistics. disk is fifty six million bytes in size, and er you used most of them down to three million left available. Erm you might, can you do us. You might get a message like this telling you you've lost some of your clusters. Have you ever lost any of your clusters?... Er, if you've got DOS Five or Six, then er [cough] it doesn't say clusters, it says allocation units erm... it's bits of your disk that er been er reserved as unavailable by DOS. It's not being of any, of any er anything in there, that's of any use. So what you really need to do is to free it up. Make some free disk space available er yet if you haven't done this check the disk for er six months or more, you probably have quite a lot of disk space, I would imagine, that could be freed up. So what you do is, to type check disk space slash and then it asks you wh if it comes up with a question, which I expect it may well, if you've never done this before. Tells you about your chain and your clusters and things, and it says do you want to convert them into files. You say no. This fact sheet actually says yes here, but eh, if you say no, then er it will free up disk space. Could be a free megabytes of disk space, quite possibly.... Right... so. Let's now explain how a memory is used... within the spreadsheet. I've got this spreadsheet with the column matters across the top and the row numbers down the side. We've got this red grid over the top of the spreadsheet erm within a single column, covering er four rows you've got these little er blocks let's call them... erm... memory is used in, used in columns, so er, if you put an entry in any one of those four cells there, then er, within a, within a box, it will use sixteen bytes of memory. There's nothing else in this whole column, so that total column is using, using sixteen bytes. Erm... in this column B, you put an entry in this block, so that uses up sixteen bytes, you've also got an entry in this block and that uses sixteen bytes. How many entries in that block, but because it's between these other blocks that uses sixteen bytes as well. You've got nothing else in this column, so that whole column uses forty eight bytes.... That's quite significant. Now, let me go [cough] spreadsheet. Let's start off with all of them for your worksheet status, to see the memory. So, at the top there it says conventional memory, two seven four O four eight, of two seven four O four eight. We'll just ignore the expanded memory at the moment. Just look at the conventional memory er [cough] if I put an entry in a cell in the first block of this column. Then that should use sixteen bytes of your worksheet status. And that number on the left has gone by sixteen O three two sixteen there. Put an entry in the third block and er with the worksheet status again and the number has now gone down forty eight. So sixteen in the first block, sixteen for the third block and sixteen for the intervening block. Now if I go all the way to the very bottom of the spreadsheet, and put some er, an entry, somewhere near the margin there. Now its using sixteen bytes for that block, but also, sixteen bytes for all the intervening blocks, and there's an awful lot of intervening blocks between that and that in your worksheet status again. Er, you see the number on the left is two hundred and forty one thousand and two hundred and seventy four thousand so it's gone down by approximately thirty three thousand bytes. I have only got three cells with any entries. I've used up an awful lot of memory. And er, if I was to put entries all the way across the top of this spreadsheet like this, and then, all the way across the bottom of the spreadsheet, do you know how I got to the bottom so quickly, by the way?
[speaker002:] End and a down
[Graham:] End and a down. That's right. Erm and now very quickly again because, it's filling up all gaps between this bottom row and the top row of the spreadsheet. Go into worksheet status, and just eleven thousand bytes. I'm trying to put an entry in this cell, in I eight one nine two, but we can't because we were require thirty three thousand bytes and we only have eleven thousand bytes left. It only starts counting from the first row of an entry and stops at the last. So, if I've nothing in these first four cells, and something in that one, and something in that one and nothing below. So, for that whole column, it's still only using forty eight bytes. If I go back to the diagonal layer, quite often I get a question. Does the diagonal layer use up more memory. But as you can see now... it doesn't, because you're only using memory where you've got the entries. It stops counting there, and it stops there. Er, if you was to put everything on top of each other, for example, then erm, there's probably little gaps between one section and another, and those little gaps are using extra memory. So, that now I am releasing the amount of memory you could actually use. You can't use less than that... I suppose you could things going across, that reduces the amount of memory, not ruin the layout. Instead of deleting rows, you mess up all the others. Er, several entries will use up additional memory, as the minimum amount of memory they will get used erm, but certain entries don't use any extra memory, and in fact, in this column here I've put an entry, a whole number six, seven, eight, nine, ten etcetera, into each of these cells and er that [cough] column there, still only uses forty eight bytes of memory. In certain entries th tha which I'll come to shortly, do use extra memory, er, just say a few things about er, the one, three of these, three of the Windows products, which er, one of you is using, erm, it uses memory slightly differently, er, if I put an entry in row one, and a ro and an entry on row five one two, then yes, it er uses memory for that whole range there. But er, if I also put something of row fifteen thirty seven, then it doesn't use memory for all those rows between there and there. The way it works in three, is it only uses memory within these five hundred and twelve row boundaries, so any between one and five one two, five one three and one O two four, one O two five and one five three six and so on, it'll fill in the gaps within those ranges, but not outside of those ranges. So that spreadsheet is just sorting them all way across the bottom and all the way across the top. Er, we've not used very much memory at all. [cough] That's three of the Windows product. Erm, so other entries will use additional memory, if it's er, a whole number less than three two seven six eight, then no additional memory will be used. But if you put a number that's bigger than that, or it's got any decimals to it, then er, you'll use eight bytes for each number, additional eight bytes for each number. So if in A one to A four, you got the numbers one hundred thousand, that'll use sixteen bytes for the block, plus four times eight... forty eight a total for this cells. Erm, labels will use additional [cough] memory. Er, six bytes across the number of characters over the four characters. Er, [phone rings] er, the entry that uses the most memory is er, a formula. Even a very simple formula, a plus and a will use an additional thirty two bytes... and the longer the formula, more entries in the formula... The more, the more, the more you put in the formula, that more memory it takes, but even a very simple formula, uses up quite a lot of memory. So you'll tend to find on most of your spreadsheets, it's the formulae that use up most of the memory.... Now you're limited to about three hundred and three hundred three hundred and forty K of conventional memory, and you can't increase that, you can't,yo you can buy more memory, and instal it into the P C, but er, you can't increase your conventional memory available to your spreadsheet, much more than, let's say about three hundred and forty K approximately. Er, but what you can do is instal something called expanded memory... expanded memory, and you can have up to twelve megabytes, so that's er, [writing on board] twelve thousand K. This is not to scale, this is going to be much bigger than that. So, sounds fantastic, you could make spreadsheet enormous. But unfortunately, you can't use... more than about, probably five hundred K. Nearly a thousand K, but probably about five hundred K on its standard memory. Probably about the maximum you can use. Even if you've got twelve megabytes, you can't use them, let me explain why, erm, it uses the sixteen bytes per block, solely from conventional memory. It puts only certain things into expanded memory. It puts formulae in, that's very useful, as I say it's formulae uses up most memory, but it doesn't put everything. And, the more you put in a spreadsheet, the more and more of the conventional memory you are using up, and eventually when you fill up your conventional memory, you get a memory fault. Even if you've got lots of expanded memory left unused, you can't use it once your conventional memory fills up, that's it. You get a memory fault.... You don't have this problem with problem. Erm, if you erm, maybe erase part of entries of the spreadsheet, you think, oh that's going to free up a lot of memory, er, probably it won't. Once Lotus uses sixteen bytes for the block, it doesn't let go of it. If you put an entry in a cell, put a number one in the cell, then immediately erase it, it doesn't free up that sixteen bytes for that block. And if you're working on a spreadsheet, you may be working on a spreadsheet during the day, start in the morning, you're working several hours, you may find yourself running low of memory, then the memory light comes on or you get a memory full up message at some stage, and you think oh that's not fair, because I haven't made the spreadsheet any bigger. Well, er, all sorts of things cause memory to be used up. Er, one thing that tends to use up a lot of memory, is when you insert columns on the left of the spreadsheets. There's nothing in that new column that you insert, but nevertheless, it uses enormous amount of memory. It uses up as much memory as any maximum column on the right that uses memory. Erm, so what can you do? Well, the answer is pretty simple, you save the file, and you re-retrieve it, and when you re-retrieve the file, erm, it erases the memory to start with, and then erm, fills up the memory from scratch and if there's nothing there any more, then it won't use any memory for it. So you're simply saving. Find yourself running low on memory, save the file and retrieve it back again. Then you may well find a lot any extra memory. Er, when you use range erase to erase a cell, to erase the contents from the cell, so that's er, put the numbers in the cells here. I'll format out ones to the next two decimals, range formats, I'll make that unprotected cell the range protects. Ah, suppose I erase that cell there with range erase... then I get rid of the number of course, but the cell, the cell remains formatted, you can still see the F two at the top grid references. That means it er, formatted to fixed two decimals, and er, that counts as an entry as far as memory is concerned. That will cause sixteen bytes to be used in that block. Even if you save and retrieve the file, it won't free up the memory. And similarly, if you've got an unprotected cell, this one is an unprotected cell, if I erase it, range erase, then the number goes, but still the cell unprotected. How do I know it's still unprotected?
[speaker002:] There's a U.
[Graham:] There's a U in the top left hand corner, yes. Er, and that counts as an entry, as far as memory is concerned. Erm, so... you would leave what I call this deadwood lying on your spreadsheet, cells that are using up memory, they're not needed any more erm, on many people's spreadsheets I've discovered quite a lot of deadwood using up large amounts of memory, like in some people's cases two-thirds of the memory used by the deadwood. [cough] Certainly, you need to get rid of it, erm, you know how to erm, get rid of the F two don't you?... You gonna tell me? Yes, that's arrange format reset, could get rid of the F two, and you know how to get rid of the... I thought there was more people on that side of the room. [LAUGHTER] ask questions. Sorry, er, what would you do. Somebody say something. Er, changing the global protection doesn't affect the U. Range protection. You need to re-protect, the range protect. Erm, so, if you really want to erm, make sure there's no deadwood, what you would do is do a range format reset of the range you want to erase first of all. Do a range protect on that same range and then a range erase of that same range. You know, it's a lot of tedious having to do all three steps. That's what you would need to do. However, there is a tip to erm, you can do it in one operation. What you would do is copy a blank cell. So you got a cell that's not got any entry in it, is not formatted, so you're looking at the panel you can see that there's no, nothing in the parenthesis, and there's no U. So if you copy a blank cell, over what you want to erase. That's the best way to ensure that you've reset everything. When you've copied the blank, erm, it's not formatted, it's not unprotected, as well any more... So
[speaker002:] How can menu
[Graham:] You would still have to save and retrieve the file to create the memory, but yes, that woul would be the idea of it. You don't leave deadwood on your spreadsheets, cells that are formatted or unprotected, cos not only do they use memory where you've got the cells but [cough] between that and something else. So quite large areas of your spreadsheet could be using up memory unnecessarily. [cough]
[speaker002:] If the memory's full erm, is it to stop you putting up your as files, is that right?
[Graham:] You could save and file, yeah. Erm, that's all very well, er, saying it now, but you might have a lot of deadwood on your spreadsheets, er,ho how can you get rid of it? Well, the problem is, you don't know where it is. But it's not invisible unless you move the cell pointer around all over the place looking in the top left hand corner. You can't find these deadwood cells. There's not really a practical to move the cell point to around every single blank cell on your spreadsheet. Erm, one thing you can do is er, if you know that there's a certain range in the spreadsheet that's of any use. That's the only part of the spreadsheet that's of any use, then you can do file extract formulas. Highlight that range, and save it to a file. And er, into that file you save everything, that's within that range, so you save the of course, the numbers, the labels, er, the column widths, range names. So anything of any use, within that range, you save into the file you extract to. But anything outside of that range is not. So cells that are formatted with nothing in them, cells that are unprotected with nothing in them. Unless you know the type of deadwood that occurs in many people's spreadsheets is, the space character itself. No doubt, there's several of you here who are guilty of this. When er, when you delete, er instead of using range erase, you use something else, don't you? Sorry?
[speaker002:] is it the space bar?
[Graham:] The space bar, yes. That's right,s so there you go. I knew it would be the case. So as long as you get rid of that one, two, three, four, you can press the space bar and then enter. It looks like we've deleted it, doesn't it? Erm, what I've done is, I've put a label in the cell. See the label prefix in the top left, and following it, there's a space, that's a space character, which is invisible. But it looks like we've deleted it. I it is a character, as much as a another letter A is a character. Which is an invisible character, I thought. You wouldn't go typing Ass all over the place, would you, but er, spaces, no problem. And er, it's understandable why people do use the spaces, it's a nice big key. And, if you're just deleting the one cell, it's just the two keys. big space bar, the key at the front, and the big enter key. It's an easy, it's a lot less fiddly than slash and then trying to find the R and the E and then enter.... That's why people do it. Erm, you can other problems as well with the space characters, got no memory. And so I don't recommend you do it. Erm, in two point four, you just do it with one key, if you want to delete a cell out that's del. Just press the del key, and it's gone. You know about that one? It's a bit dangerous if there's two ways you can accidentally press them, and not realizing you've just deleted the contents of the cell. So, that's right. So watch out for the del key. Deletes straight away, with no warning. Gone. I've pressed it a few times by accident and then needed it. Nor normally I just type it in. Right, any questions on any of this so far? [LAUGHTER] [cough] Er, diagonal spreadsheets is also good for getting rid of any deadwood. Erm, cos what you would do is you would er, reset that whole range, that whole range, that whole range, that one and that one, that one, that one, that one. I know I would never put anything over there, or over there, or over there, over there. Knowing quite happily to just rid of all of that. Copy a blank erm, and that would get rid of any deadwood that there is on the spreadsheet, once you've done that, and you've got rid of any deadwood. If you hadn't been consistent in laying them out like this, then you can't so quite confidently go round, go round erasing large chunks of the spreadsheet. And you tend to have less deadwood on a diagonal spreadsheet. Instead of erasing, you can more often will delete, whole rows or columns. You don't need that row any more, so you delete it. And you do and then you not only remove the contents, but the cell formats and the unprotection... I'll ignore that, we any more. Two point four.... [cough] Right so, now you understand how the memory works, and you obviously realize not to leave big gaps between one thing and something below. Erm, when you start One Two Three, if you get er, the menu at the top saying One Two Three, think about and translate or you just go straight into the spreadsheet, you do get the One Two Three... you're starting er, from what's called the access menu. Er, in DOS, if you type Lotus, you get that access menu, with One Two Three translate, if yo in DOS, type one two three, the numbers one two three, you go straight into the spreadsheet, and you have eight K more [cough] conventional memory, if you do. You don't go through the access menu, an eight K conventional memory means could put more into expanded memory more conventional memory you've got, the more you can put into expanded memory. Right, any questions on that?
[speaker002:] that much expanded memory if you they can only use five hundred K?
[Graham:] Erm. Well, they probably don't have that much. Do you know anybody that's got that much? Erm, a lot of people have got, maybe, five thou three thousand. Depends how much ma if you, maybe new P C these days, it would normally have it already on it four thousand K of memory, and three thousand left could be converted to expanded memory, but it's not worth it only use five hundred K of it probably. And you might could try a thousand K, erm, with oth other programs, if you start using Windows,y you need to use the so software called Microsoft Windows if you're going to use one to four. And yet, that would use all of that memory.... But er, one two is that, you can't use all the memory you've got. [cough] Erm, the short section here on useful formulae, in fact, basically just one formula. It's useful in design. Erm, I've put a label A B C, in one cell, D E F in another cell, and in this cell B five, I've got the formula, plus the three ampersand B four, and th the result of it is this longer label, if you join the two labels together to make this label A B C D E F. Anybody using ampersand at all?... You have used it. Erm, what have you used it for?
[speaker002:] for that sort
[Graham:] That sort of thing, right, and why... did you want to use it? Erm, let me just demonstrate how it works. A B C D E F you put ampersand that. And it's a formula, so if you change those cells, the formula will be calculat no, let's type my first and second names in, and er, it's the two names together. It doesn't look very good, having erm, first and the second name without a space between the two, but I can actually put a space there if we entered a formula, by putting a double quote space, another double quote, and then another ampersand, filling the spaces of D six. There we go.... [cough] So why would you use it, er, that's have a look at.... So, I've got a parameters s section that we were talking about before, putting all the things that you might change into one part of the spreadsheet. So a department name, the admin department, a department code, one hundred, and as I was saying er, you wanna to get in the top left hand corner, any information that keeps you informed so I wanna get both the department code and the department name into that one cell. You've only got the one cell left available. I need to get both pieces of information into that one cell. And I can do that with this formula. I can do that... ampersand... that... or those two cells have got range names the range names. Erm, if I put a space in, it might look a bit better. You don't have to have a space, you can have anything you like. That, perhaps... [sneeze] and I change the name of the department, the er s marketing department, department, erm, one O two. Let's change that.... The one O two you might notice here, has been entered as a label. If I was to enter it a number, one O two.... It's a number now on a label, then, this formula doesn't like it. It says error. Cos you can't join a number to a label. By the way, you can convert a number to a label with a certain add function. I don't suppose anyone would know.
[speaker002:] a chart.
[Graham:] Er, it's something like that [blowing nose] er, is the word that two parts, two arguments to the string, there's the number B four comma, and then a number [cough] for the number of decimal places you want in your label, so zero, so I get a label with three characters here one O two. If I put a two there, I'll have an extra three characters and gives me a full stop and two zeros.
[speaker002:] Sorry, that change of one number to label.
[Graham:] That's a label now, yes. See, it's left align, whereas that's right align. That's a label. Now multiply that by two then er, zero on rather than the two. Erm, so what I can do is to put the B four here inside of that string comma zero. There we go.... So, that's the use of that. There are many other uses for these formulae. The formulae that work on labels, they're never string formulae. The ampersand joins one label to another, I use the word join erm, it's not the official technical word for what it does, does anybody know what the erm, official word for what this does is known as?
[speaker002:] Concatenate?
[Graham:] Yes, concatenate. It concatenates. Concatenation is the alteration that er, the ampersand does. Programmers concatenate a lot. They concatenate their variables. If you ever hear programmers talking, you might hear them say something like that. Have you concatenated your variables today?... But er, these the word joining spreadsheets terms. Bit shorter, not as funny. So, that's the er, I want cover in the next section. We'll now have er another test. I'll give you a few minutes to revise, then er, we can see if the er lesson... Does anyone know what time the coffee break is er?...
[speaker002:] The canteen's just opposite there
[Graham:] Right... Right, do you know where we er slot the last one? It was just after er, page thirty nine, so on page forty three onwards... Right, are you ready? Er, the left started last time, so the right can go this time. Erm, what was my convention for lines?
[speaker002:] table, erm, double dashes for the total and full stops for your sub total. Yeah.
[Graham:] Oh yes, quite right, across almost right, not quite. Ah, cheating.
[speaker002:] That is. double line at the bottom of the
[Graham:] Which side are you on?
[speaker002:] Total, yeah, he's on our side. That's what I said. Single data sub-total. Double for the total and a row of dots for your sub, sub-total. Your two lots of double lines. ...
[Graham:] Have you got any an answers on that on the right? One of you... left sorry, on the left, you're not on that side, are you?
[speaker002:] No. [LAUGHTER]
[Graham:] Erm, well erm, what you said was correct. Th the double line doesn't mean necessarily th that the total of it is the end of the sub-section, the dots,sub sub-section. So er, I won't give anybody any points.
[speaker002:] [LAUGHTER] Oh
[Graham:] Er, over to the er left this time. What was my invention for indenting. How did I do the indenting, with the sub-titles and totals?
[speaker002:] Well, you [LAUGHTER] you indented them [].
[Graham:] Oh no, no, no,
[speaker002:] You have them in the style of how you physically did it.
[Graham:] The style, yes.
[speaker002:] No, you had yours
[Graham:] I'm not sure
[speaker002:] Then, then all your erm, your data under underneath your heading you indented.
[Graham:] How much how much was various things indented?
[speaker002:] Two or three points. [LAUGHTER] So it looks good, but it looks alright.
[Graham:] Alright, erm, well I remember how do I get a sub-title and then a total at the bottom? You know, you had the sub-title and things indents below that, then a total. So, how did I indent the sub-title, the detail and th the total line?
[speaker002:] Your first title was indented the same amount as your first title.
[Graham:] Right. Yes.
[speaker002:] Then your grand total was right over to the left hand... side. Yes.
[Graham:] Er, oh in the example that I gave, yes, because it was totalling what... the grand total.
[speaker002:] Well, A B various sections.
[Graham:] Er... what was it lined up with?
[speaker002:] What? The grand total or...
[Graham:] Yes. The grand total. What was
[speaker002:] It was lined up with the erm, the title of that column, Which was the
[Graham:] Erm...
[speaker002:] the actual heading, yes.
[Graham:] So, in the example you're referring to, I had like a title and then I had a sub-title and further details and another sub- title and further details, and then I had a total line...
[speaker002:] Yeah.
[Graham:] sort of grand total here. So, what was that gr the total was totalling everything. The whole of that. So what was that? How here. What was that line here that one.
[speaker002:] With the top line. Top line
[Graham:] That's the one, yes. Right, that was my.
[speaker002:] Give me about two points for that actually.
[Graham:] Shall we give them two? Erm, in the titles of the spreadsheets, in the top you know the top rows of the spreadsheet. What did I put where? There was various types of information...
[speaker002:] Are, varied wannit. No, variable en
[Graham:] There's things. Yes that vary and things that don't vary. Where did I put them?
[speaker002:] Yeah, the thing that varied the less was the top and the most at the bottom and the middle was the middle. Yeah.
[Graham:] And things that don't vary?
[speaker002:] Bottom. Either in the top... Er, the... along the line at the top, you know
[Graham:] Not the top left.
[speaker002:] like the date and the time you know along the top right...
[Graham:] The right. Not, not yes, that's good. That's it. Right good. Over to this side now, erm, [cough] give me three things that you can do to make it easy t for the eye to see what er, particular number is refer w which rows is referring answer to?
[speaker002:] The shaded one
[Graham:] Yes, that's one and what every row?
[speaker002:] You know [cough] wouldn't it [LAUGHTER]
[Graham:] Yes. [LAUGHTER] Testing. Right.
[speaker002:] Inset row,
[Graham:] That's one, shading, every so many rows.
[speaker002:] Blank rows, every so many rows.
[Graham:] Inset what, sorry?
[speaker002:] A blank row.
[Graham:] A blank row, yes that's two yes.
[speaker002:] And put your titles on both sides.
[Graham:] Put your titles on both sides, right. Okey-doke. put the side
[speaker002:] [LAUGHTER] You've
[Graham:] How should you format your spreadsheets?...
[speaker002:] Could you rephrase that please. [LAUGHTER]
[Graham:] Erm
[speaker002:] On the large side [LAUGHTER]
[Graham:] erm I could guess.
[speaker002:] [LAUGHTER] [LAUGHTER]
[Graham:] I don't think I will
[speaker002:] Diagonally. [cough] If you were putting form, you wouldn't have to.
[Graham:] No, I'm talking about the cell formats, you know I can fix two decimal, the comma format. How, how do you... set the comma format?
[speaker002:] load Load
[Graham:] How do you do that?
[speaker002:] erm, range format comma two enter.
[Graham:] Erm, we've got several different answers to the same thing. What's the er... how do you do a global format?
[speaker002:] global formula in
[Graham:] Right, okay. Alright, I'll give you that then. On the other side, on a new spreadsheet what is the new global format called? Brand new spreadsheet. What is it set to?
[speaker002:] two points. [LAUGHTER]
[Graham:] graphs?
[speaker002:] General.
[Graham:] General! Right, yes.
[speaker002:] Which is what? Which is what? Indent.
[Graham:] It's called the general formula.
[speaker002:] Yes, but isn't the general formula got a
[Graham:] Er, it's, it fits the number you can, to be typed one, two three point one, you'll see one, two three point one.
[speaker002:] Oh, right.
[Graham:] Er, if you type a very big number, you might get the scientific, that is what the E thing. Erm, right. Okey-dokes. right again. Erm, erm, what's the advantages, two advantages of using these global formats?
[speaker002:] two erm,
[Graham:] That's right.
[speaker002:] And global. it, it's easier to have the global change things come out of the global and change things.
[Graham:] Yes. It's easy to change things if you say, I want, three decimals instead of two whenever. Change them over and everything. All the global settings change, yes. That's one reason. Two reasons?...
[speaker002:] It would be easier than a password if you need to check out changing the
[Graham:] Yeah. You don't have to format something and put them in. That's right. That's it. Right. Good... to the other side. Erm. Give me er, four pieces of advice in a bag.
[speaker002:] [LAUGHTER] Don't go out after nine o'clock. [LAUGHTER]
[Graham:] Don't do after nine o'clock. Erm. A wise advice about the typographical style, you might call it, er,
[speaker002:] Don't use more than
[Graham:] Don't use more than two, yes. Okay, that's one.
[speaker002:] Oh, this thing about landscape and style consisting.
[Graham:] Give 'em one, there, yes. Two.
[speaker002:] Don't use auto compress.
[Graham:] Don't use auto compress, yes. You need one more.
[speaker002:] Don't know, I can't think. You know the we got, you don't need it on. Or is that related to auto compress?
[Graham:] Sorry, what did you say?
[speaker002:] Don't attach with you. Because if you got auto
[Graham:] Don't erm, right now, now say erm, realize that when you are using it.
[speaker002:] Oh, right. Not too many lines
[Graham:] Yeah, okay, too many horizontal vertical lines I also mentioned er, not marrying the row heights, get more on a page. that. Okay. Erm, now over to the right. Erm, what should you do with the At Sum?
[speaker002:] below that row.
[Graham:] below, yes. Okay. Erm, on the other side. Why?
[speaker002:] So that if you don't. If you insert a line... At the bottom. Total. If you insert a line underneath...
[Graham:] Right. Okay. Certainly not right. Erm, what did I recommend you do with whizzy-wig. In the case out sum. Er, well draw a line, you do have to have an extra row put on it. So what you could do with it. With the bottom line. What did I say?
[speaker002:] to the top. [cough] at the top That's right, you completely... You can reduce that row And you could put a line in
[Graham:] At least there are you can about one and put a line on the top of the total line, er, afraid that's wrong.
[speaker002:] What was the question?
[Graham:] Erm, with, when you, when you drawing a line with whizzy-wig, and you got the At Sum below it, how should you do it?
[speaker002:] Oh, That's what she said.
[Graham:] That's what she said, yes, but er, she got it wrong.
[speaker002:] Yes, it they did. Oh, I don't know
[Graham:] Yes, that's right. That's one point. But what's the other point. There's also got wrong on this side. About the line. Where do you put the line?
[speaker002:] bottom of the line. On the bottom of the blank line.
[Graham:] Yes. Put on, on the bottom of the blank line, rather than putting it top of the total line. Yes, because if you do that, you know where you've put the insert. Right. It's the left's turn again, I think, isn't it? Right. Erm, what's a danger with long formulae?
[speaker002:] Chopping the
[Graham:] How can, how does the end get chopped off?
[speaker002:] [cough] If you insert more rows than the numbers...
[Graham:] Cell addresses
[speaker002:] Cell address get longer
[Graham:] Get longer. And they get chopped off where?
[speaker002:] About to two hundred and forty characters.
[Graham:] Erm, but what, what do you have to do to chop it off?
[speaker002:] Nothing. If you didn't say anything, if you didn't give a me editing for a formula... or copy it [cough] to somewhere else where the cell adjusting are longer, then it makes the formula longer and then you get
[Graham:] So the formula get longer, okay, but it er, works alright, until you do something. What's that thing, something you do that causes it to get chopped off?
[speaker002:] Call F two call it up again.
[Graham:] What did you say then.
[speaker002:] If you press F two...
[Graham:] F two, that's what I was after, yes. If you press F two, and then do what?
[speaker002:] It loses the chopped, the end of it.
[Graham:] Yes.
[speaker002:] Oh, so if you don't, if you don't call it up to edit it, you can keep changing it and it will still stay the same?
[Graham:] That's right.
[speaker002:] Oh, right... confusing
[Graham:] Er... yes, [cough] right erm. What did I say about how not to input certain numbers? Slightly vague question. What shouldn't you do when you're inputting several different numbers? Vague question. It's an easy answer... to a vague question. So it does look. You've got several numbers to input. What should you do? too vaguely.
[speaker002:] Several digits or several numbers?
[Graham:] Different numbers. You got sales figure and the unit sales. Sorry...
[speaker002:] Yeah, erm, down the individual figures.
[Graham:] Yes.
[speaker002:] of one.
[Graham:] Yeah, give you half.
[speaker002:] Oh, that's unreasonable. [LAUGHTER]
[Graham:] graphs.
[speaker002:] What was the, what was the answer? Yeah, but they didn't put it down individually on spreadsheet.
[Graham:] Yeah, then put all the, all the separate inputs in, in one cell.
[speaker002:] You remember when you did that formula hundred divided by an eight brackets, something, something? Oh, I see, oh right.
[Graham:] Right. Erm, how cross checks. I want precise details on this.
[speaker002:] Put F formula.
[Graham:] F formula, right.
[speaker002:] Underneath the two...
[Graham:] Where do you put your F formula?
[speaker002:] Up in the right hand side of the work in the bottom of your column.
[Graham:] Okay. Yes. And how do you...
[speaker002:] The... erm... er the question checking the answer. You put a test in, don't you? Test... Whether it's greater or less.
[Graham:] What's the test?
[speaker002:] If it, if it like this, then put this answer, if not... [tape change]