How to check whether data is outside of the range of the excel calculations
When you are using spreadsheets, you often import data by copying a section & pasting it in to a specified area. However with spreadsheets you have to be careful that you don’t go outside the calculated area otherwise those cells don’t fall into the calculation.
So in this example, what we have is a very simple spreadsheet & we’ve indicated with a color where the end of our calculation range is. One trick is to put an alpha letter in this border row & then to perform a simple check so see that these haven’t been overwritten. So for example, in this cell I’m going to do an IF statement, & all it’s going to say it’s going to look at one of the key columns & say if that cell there equals the alpha we’ve pit in, in this case an X. If that’s the case then we can say ok, however if that’s not the case we have to assume that it’s been overwritten, we’ll maybe put “error”. When I say ok, you’ll see at the moment it’s ok, let’s pretend we go here, & we copy this section & we come here, & we paste it in, you’ll see that this overwrites our border, immediately our error check picks it up. The problem with this is that typically, your error check can only focus on one column, & what happens if only this column is overwritten. So another way to do it is in the boarder cell is to actually put some numbers, in this case I’ve put a one in each cell. And now for my error check I actually some up these & compare them to what I expect them to be. So for example I ‘m going to use the IF function & say if the logical test is of the sum, this area, & I expect it to equal to one, two, three, four. If that is the case, then I want it to say ok, if not I want it to say error. When I say ok, you’ll see in this case that it’s fine, & again if we copy this area here & we paste it in, because we overwrite these boarders we are immediately warned. And in this case we could have multiple violations of it or only one, either way we’ll pick it up because we are addressing all the columns.
Advanced Excel Course – June 2013 in South Africa Our next advanced excel course is running during June 2013 in Johannesburg. During the week of the 10th June we offer the following options. You can either attend the full 5 … Continue reading
A very popular chart is the waterfall chart which shows the impact of various items on a total e.g. the various components that explain the move from budgeted profit to actual profit. A common way to build this is using … Continue reading
“Data accuracy and consistency has improved dramatically across Absa Capital, so much so that it has become compulsory within certain areas for all staff to complete Adrian’s Data Analyses Spreadsheet Techniques course.”– Absa Capital