[Excel] How to Exclude Blanks and Errors in Function References

When processing raw data with Excel functions for analysis, the presence of blank cells or errors can lead to incorrect displays.

For example, suppose you use the INT function to convert random numbers in column C into integers. In this case, any blank cells become 0.

If you use the same method (converting to integers with the INT function) but replace the numeric data with text, the processed results in column D will show an error.

While it might be acceptable to ignore these issues in some cases, problems arise when:

• Aggregating the data: Functions like SUM or AVERAGE might produce errors or yield incorrect totals.

• Creating graphs: Both zeros and error values are treated as 0, which can result in non-zero values being plotted incorrectly or cause trend lines to be computed inaccurately.

To overcome these issues, you can modify your functions to include conditions:

1. Handling Blank Cells (Case 1):

Use a function that checks whether the referenced cell is blank, and if so, return a blank. (You might use the ISBLANK function to perform this check.)

2. Handling Errors (Case 2):

Use the ISERROR function to check not only the referenced cell but also any subsequent function that uses its result. If an error is detected, return a blank.

With these adjustments, aggregation functions will compute the correct results. If you plan to use this data for graphing, keep in mind that Excel treats blank cells as 0. In that scenario, instead of outputting a blank for errors, consider outputting #N/A. This change will exclude the erroneous data from your graphs.