Finding the typical IF errors in MS Excel spreadsheets in order of likelihood of a spreadsheet error
на канале: AuditExcel Advanced Excel and Financial Model Training and Consulting
Download the software used in this video- https://online-excel-training.auditex...
Part of the Excel Spreadsheet Best Practice Playlist- • Spreadsheet Best Practice for Financi...
See full free course on with downloads on https://online-excel-training.auditex...
When reviewing an Excel spreadsheet it is important to understand the typical errors people make. Below are ideas on easy ways of finding the IF errors, in order of riskiness, when auditing an Excel Spreadsheet.
IF is a very popular function, unfortunately too popular. In many cases a simpler function would do the same thing but the user knows IF well and is not sure about the other function. It is also very difficult to review. Have a look at how we explain how you can build an IF function to get ideas of ways of reviewing complex IF functions by drawing a decision tree diagram.
It is useful to have a spreadsheet error add in to find all the risky formulas but if you don’t have one you can do a manual search using the FIND tool.
Using “TRUE” or “FALSE” instead of TRUE or FALSE
TRUE and FALSE are often used within IF functions. There is a user risk when it comes to these. In all other cases, when you enter a word into Excel, you must have inverted commas around it e.g. “OVERTIME”. However, TRUE and FALSE are different. Although the users type in a word, Excel sees it as a 1 (True) or 0 (False). If a user accidently enters it as “TRUE”, Excel will assume you are looking for the word TRUE and not the logical result True.
In this case, instead of searching for all the IF’s in the formula report listing, rather use the ‘Formula with Text’ and search for the ‘text’ “TRUE” and “FALSE”. If they are in an IF function there is a good chance it is an error. As shown below we can easily see all these instances and investigate immediately.
Multi Nested IF functions
IF is often used as a replacement for other formula which users may not be aware of. A common one is where a multi nested IF is used to work through some sort of sliding scale. As shown below, this is a fairly complex IF formula. In reality it would be better built with a VLOOKUP- true, but you have to review what you have been given. The risks include whether the logical test is correct (less than or less than or equal to), whether the correct cells were referenced for each band, and whether a new band has been added to the sliding scale and incorrectly updated in the IF (or not updated at all).
Complex IF formula
As shown below, IF is often part of complex formula. In the normal spreadsheet review you will cover these in detail, but it may be useful to spot check some of these combinations, especially when they involve some of the other risky functions we have highlighted. In the listing below we would look at any that have more than one IF (more about that elsewhere) and the ones that include some sort of error suppression like IFERROR or ISERROR.
Logical Tests e.g. bigger than 0 or bigger than or equal to 0?
Spreadsheet errors often happen around the border of a calculation. With IF functions it is often to do with the logical operator used especially with the bigger than and less than (bigger than and less than). Should the test be bigger than 100 or bigger than or equal to 100. The problem will only be at that point but it is surprising how often these are the errors that slip through.
As shown below, we can use the Formula Report to list all the IF functions and then see which ones may need this type of investigation.
Смотрите видео Finding the typical IF errors in MS Excel spreadsheets in order of likelihood of a spreadsheet error онлайн без регистрации, длительностью часов минут секунд в хорошем качестве. Это видео добавил пользователь AuditExcel Advanced Excel and Financial Model Training and Consulting 29 Декабрь 2020, не забудьте поделиться им ссылкой с друзьями и знакомыми, на нашем сайте его посмотрели 56 раз и оно понравилось 1 людям.