Excel Quiz Answers
Answers for the quiz on our 26th Nov 2014 Newsletter. To subscribe to the newsletter visit the subscribe page.
Excel Questions from the Newsletter
- What is the easiest way to set up a range so that it automatically grows as you add more data for things like graphs, pivot tables etc
- Use the Excel TABLES tool
- Use a NAMED RANGE with an Offset
- Leave extra cells open for more information
- Which of the following actions are NOT already available as a formula or can be achieved by combining some formula in Excel
- Find the position of a certain character in a cell e.g. a ‘#’
- Find the position where the font size is different in a cell e.g. AB
- Find the position where the capital letters are in a cell e.g. AdrianMiric
- If you are calculating VAT, which is the best formula to use assuming that cell A1 has the selling price excluding VAT and cell A2 has the VAT rate of 14%
- =A1*A2
- =A1*14%
- =A1*$A$2
Learn more about all these features and issues at the Excel courses we offer
Answers:
- A- Although you can use an OFFSET within a NAMED RANGE to achieve the same thing, the Tables feature in Excel is the easiest way to do it.
- B- You can use the FIND function in Excel to find the position of any character in a cell and by combining CODE and MID you can work out where the capital letters are. To determine where the font size changes though you need to use some VBA
- C- The first one will work fine except if you need to copy it elsewhere. Option B is bad practice and you should avoid including numbers in a formula (what happens if the VAT rate changes?). Option C applies good practice as the VAT rate is in a separate cell and absolute referencing is used to ensure that no matter where the formula is copied to it will always look at that rate.