Excel is an incredibly powerful application that allows for data of different sizes to be stored in a spreadsheet that can then be analysed and reported. One of the most useful functions available in the application is that of formulas. You can take away the need for any manual calculations by using the formula function. This is particularly useful if you have imported data from a PDF to Excel converter like the one from PDF TABLES. There are occasions however, when errors in your formula coding can occur. Here we look at some of the most common ones to occur and their possible reasons. .
#DIV/0!
This error code means that your formula is trying to divide something by 0. This usually occurs if you have data that contains a 0 or an incomplete set of data. As you fill in your data fields you may find that the error code rectifies itself.
#N/A
This not applicable error can be infruitating and there are number of reasons that it can occur but the most common is as a result of a spelling mistake being made in the coding or a key component such as comma or colon having been left out, or a space in the wrong place.
Six formulas worth learning first
Many errors are easier to avoid once you know a handful of core formulas. SUM adds up values across a row, such as =SUM(A2:G2), or down a column, such as =SUM(A2:A8). COUNT tells you how many cells in a range contain numbers, ignoring blanks, which is handy in large sheets. MAX and MIN quickly find the largest and smallest values in a data set.
TRIM is particularly useful for preventing errors. Data pasted in from elsewhere often carries extra spaces or hidden characters that can stop formulas working, and TRIM strips out the unnecessary spaces to tidy it up.
IF applies simple logic. For example, =IF(C2=”Cat”,10,0) returns 10 if the cell contains the word Cat and 0 if not, while =IF(A2>=5,”yes”,”no”) checks whether a value is at least 5. TODAY inserts the current date and updates whenever the sheet recalculates, so there is no need to type the date in by hand. Getting comfortable with these makes it far easier to spot where a more complex formula has gone wrong.