Stella, the manager of a section within one of the major accounting firms happened to be passing young Fred's desk on her way to the coffee machine. Fred was the most junior member of her team.
'Stella, excuse me but how do you edit the contents of a cell in Excel,' Fred asked just loud enough so that she could hear.
'Well there are two main ways,' Stella said as she wondered round Fred's desk so she could see his computer screen. 'With your cursor pointing to the cell you can press the F2 key, or you can edit the text by clicking in the area that covers most of the row just below the icons at the top of the screen. Why do you want to know?'
'This pro-forma fixed asset spreadsheet I have is depreciating the category of assets I am working on at 20% but Pete my supervisor wants these ones depreciated at 15%. So I have to go through all of the formulas and change '.2' to '.15'' Fred replied.
'Who designed the pro-forma spreadsheet?' Stella asked.
'Susan designed it just after she came back from her three day Excel course, the one I am due to attend next year,' Fred replied. Susan was sitting at her desk only a few metres away. 'Sue, can you come over here for a minute, please,' Stella asked, looking in Sue's direction.
'How can I help?', Sue asked as she approached Fred's desk.
Stella continued. 'Sue, at your course, did the instructor not tell you to provide specific cells for users to put things that could vary? Things like depreciation, gross and net profit percentages, and many more variables that could crop up in spreadsheets designed for various different purposes. If you do that, and include the address of the variable in formulas where necessary, the user only needs to change the contents of one cell and all relevant formulas will automatically be adjusted, thus saving the user a lot of work. In addition a user with no detailed knowledge of Excel will be able to tailor the spreadsheet to suit their individual requirements.'
'Yes, I remember him telling us that,' Sue replied.
'Well, why haven't you done that here, in the case of the depreciation calculations?'
'When I copied the formula down the page, the address of the cell containing the depreciation rate changed and so, after the first one, the formula did not work. I reckoned that it would be easier for the user to change the rate in the first formula and for him or her to copy the new formula down the page. The formula would work all the time then.'
'And what if the user did not know how to edit the first formula or how to copy it down the screen?' Stella asked. 'Fred was planning to change every individual formula.'
'I guess I did not consider that possibility,' Sue replied with a sheepish grin.
Stella continued. 'Did the instructor mention 'relative references' versus 'absolute' and 'mixed references'?'
'I think so, but I did not really understand that bit.' Sue replied with a grimace.
'Fred, give me a sheet of paper,' Stella said, forgetting all about the coffee she had been on her way to get. Fred did as he was told and Stella produced a pen from somewhere before scribbling down four cell addresses. They were 'A1', '$A1', 'A$1', and '$A$1. Beside 'A1' she wrote the word 'Relative', beside '$A1' she wrote 'Mixed', she did the same with 'A$1', and beside '$A$1' she wrote 'Absolute'. 'Right,' she continued. 'The first address, 'A1' is the simple column and row address that beginners are taught in their first lesson. If, say cell 'C3' refers to that in its simplest form. '=A1', whatever is in cell 'A1' will appear in cell 'C3'. If the formula in cell 'C3' is copied to cell 'D3' the formula that appears in cell 'D3' will change to '=B1', and whatever is in cell 'B1' will appear in cell 'D3'. If the copying process is continued 'E3' will equal 'C1', 'F3' will equal 'D1' and so on. Similarly if cell 'C3' is copied downwards, 'C4' will equal 'A2', 'C5 will equal 'A3' and so on. This basic formula in cell 'C3', '=A1" is called a 'relative reference'. However you can stop both the row and the column changing by putting a Dollar sign in front of either the column letter or the row number. This can be achieved both by directly typing in the Dollar sign or signs, or by toggling between the four possibilities by pressing 'F4'. Both '=$A1' and '=A$1' are called 'mixed references'.'
Stella carried on. 'If '=$A1' in cell 'C3' is copied to the right, all of the cells it is copied into will read '=$A1' and whatever is in 'A1' will appear in all these cells. However the row number has not been held, and so, like in the example of the 'relative formula', if the contents of cell 'C1'is copied downwards,'C4' will equal 'A2', 'C5 will equal 'A3' and so on. Similarly if '=A$1' in cell 'C3' is copied to the right, the cells it is copied into will read '=B$1', '=C$1' and so on, just like in the example of the 'relative reference'. However, this time, the row number has been held, and so, if the contents of cell 'C1' is copied downwards,'C4' will read '=A$1', 'C5' will also read '=A$1' and so on, and whatever is in 'A1' will appear in these cells.'
'Now it must by obvious what is achieved by the 'absolute reference',' Stella carried on.
'Can either of you guess?'
''=$A$1' will appear in all cells 'C3' is copied into whether it be to the right or downwards,' Sue suggested with a grin.
'Good girl,' said Stella. 'And don't forget this also extends to copying to the left and copying upwards,' she added. 'Now Fred do you have any other work you can do for the rest of the afternoon?'
'Yes, plenty,' Fred replied.
'Well leave your spreadsheet until tomorrow. Sue, I want you to re-do your fixed asset spreadsheet, if necessary taking it home, so that tomorrow, Fred can fill in the details without adjusting any of your formulas; understood?'
'Yes,' the two trainees said in unison.
Stella left them to get organised. Goodness I really need that cup of coffee now, she thought to herself, as she headed towards the kitchen.
Phil Ramage CA (Scot) CA (Aust)
For more information and, if needed, assistance, go to: www.philipramage.com
Phil also has a very informative and active blog at www.phil-ramage.com
(C) 2011 Philip Ramage All rights reserved
No comments:
Post a Comment