Answering AAT spreadsheet questions – everything you should know
PART II
Table of Contents
In Part 2 of our series on AAT spreadsheet questions, we are looking at more formulas you need to be familiar with using. In Part 1 (which can be found here), we looked at using:
- DAYS
- SUM
- AVERAGE
- MINIMUM
- MAXIMUM
In Part 2, we will be explaining the following formulas:
- ROUND
- ROUNDUP
- ROUNDDOWN
- SUMIF
- COUNT
- COUNTA
- COUNTIF
- IF
- VLOOKUP
- HLOOKUP
You will also have the chance to use these formulas with our free practice questions, which are available at the bottom of the page.
1. ROUND, ROUNDUP, ROUNDDOWN
The concept of these is straightforward, they will round off, up or down a number to the level you want it. The format for the formula is:
=ROUND([cell reference], [level of rounding])
The level of rounding will be a number. If you wish to round to a certain number of decimal places, the number will be negative. If you wish to round to the nearest whole number, the level of rounding will be zero. If you want to round to the nearest ten, hundred etc. then the number will be positive. The level of rounding is displayed in the diagram below:
Cell B4:
Therefore, if you need to round off Cell B4 to the nearest one decimal place (£0.10), you would enter:
=ROUND(B4,-1)
If you need to round up Cell B4 to the next whole number (£1), you would enter:
=ROUNDUP(B4,0)
If you need to round down Cell B4 to the nearest £1,000, you would enter:
=ROUNDDOWN(B4,3)
Try using the ROUND & ROUNDUP functions with our free spreadsheet practice questions.
2. IF, SUMIF
The IF formula is a simple logic function, commonly used in programming. The formula looks to see if a certain criteria is fulfilled, and if so it will perform a certain action, if not it may perform a different action (or none at all).
The format for the IF formula is:
=IF([condition to be satisfied],[value to display if true],[value to display if false])
In the example below, several students have taken a test. If they score above 69 then they have passed.
The condition to be satisfied is the Test score being greater than 69. If the test score is greater, the cell in column C should display a ‘Yes’, if not, it should display a ‘No’.
The formula to use in Cell C2 would be:
=IF(B2>69, “Yes”, “No”)
The SUMIF formula works in a similar way, but it adds together all of the instances in a defined range that meet the criteria. The format to use is:
=SUMIF([range to be searched], criteria, [range to be summed])
Imagine a greengrocers has a list of customer orders and wishes to calculate the number of each type of fruit it has sold:
To show the number of apples ordered, the formula in F2 would be:
=SUMIF(B2:B8, “Apples”, C2:C8)
The spreadsheet will search B2 to B8 for the text “Apples” and all the numbers in column C corresponding to “Apples”.
Note that instead of putting “Apples” in the formula, you could simply refer to Cell E2. This would have the advantage of making the formula easier to auto-fill Column F for the other fruits. Most spreadsheets these days would also have an option to make a pivot table of this data, which would be easier than writing a formula.
3. COUNT, COUNTA, COUNTIF
The COUNT formula counts how many cells in a given range have a numerical value entered into them. The format to be used is:
=COUNT([starting cell of range]:[ending cell of range])
The COUNTA formula counts how many cells in a range are not completely blank. COUNTA includes numbers, text, formulas, even error messages. The format is:
=COUNTA([starting cell of range]:[ending cell of range])
The COUNTIF formula counts how many times a certain criteria is met in the range. Using the greengrocers example, if you wanted to know how many times Apples were purchsed, you would enter:
=COUNTIF(B2:B8, “Apples”)
You can practice using COUNT, COUNTA and COUNTIF with our free practice questions.
4. VLOOKUP, HLOOKUP
VLOOKUP and HLOOKUP are useful in finding a particular value in a large amount of data and returning a value corresponding to it. VLOOKUP and HLOOKUP are probably the most complicated of the formulas that AAT require you to use, but it can be very useful.
Imagine you received a number of orders and need to look up their prices against a list:
Whilst the example above is quite simple, when you have hundreds of products and thousands of orders, entering prices manually would be impractical. VLOOKUP can be used to enter the prices automatically.
The format of VLOOKUP is:
=VLOOKUP([lookup value), (table array), (column index number), (TRUE or FALSE])
Explaining each in turn:
Lookup value – this is the thing you are searching for. So in the case of Cell B2, you are looking for the item code 1003 in the price list. However, to enable you to autofill the other cells, you would use the reference A2 instead of typing out “1003”.
Table array – this is where you are looking. In the example you will always be looking in the price list. The array for this is D1:F11. In order to stop the search array from drifting as you autofill to other rows, you can enter the array as D$1:F$11. The dollar signs tether the search array to the specified rows.
Column index number – The number of the column you want to draw the results from. You want to return the price of the item you have just looked up. The price is found in the third column of the search array (D1:F11), so you would enter the number 3 here.
TRUE or FALSE (range_lookup) – enter FALSE if you are looking for an exact match, or TRUE if you are looking for an approximate match. At this level of complexity, you will almost always be looking for an exact match, so the word FALSE is entered here.
This gives the formula for Cell B2 as:
=VLOOKUP(A2,D$1:F$11,3,FALSE)
With a couple of clicks, you can autofill this to the rest of the rows in Column B (see part 1 of this series of posts) for instructions on how this can be done.
HLOOKUP works the same way as VLOOKUP, but looks across rows for corresponding values, rather than columns.
You can practice trying out using these commands in our free spreadsheet question practice downloadable here. Further exam style spreadsheet questions can be found in our AAT Level 3 Management Accounting Techniques Question Pack.
Click for Part 1 of this article
