Abstract:

Numerous companies are using the MS Office package at different levels of their corporate hierarchy, including MS Excel spreadsheet, which is a tool for data processing in the form of tables. Data are usually imported directly in MS Excel environment, often from a file with a different data format. It frequently happens in practice that as a result of such data import a problem may occur that can have fatal consequences for the company. The problem is that some data may have different data format in the imported file than it is expected in the table after importing. Internal MS Excel functions for mathematical operations ignore cell values that are not numeric and do not enter into the function calculation. Though internal functions ignore cells whose data type is not numeric, they do not inform the user about this fact. This paper therefore aims at not only informing about this adverse effect of certain internal mathematical functions, but also at explaining the way to eliminate the problem. The Visual Basic for Application tool (VBA), which is an MS Office internal tool, will be used for it. The outcome of the paper will be the design, analysis and creation of a sample function in the VBA environment that would warn the user of the input data inconsistency, thus eliminating irrelevant results. The paper uses examples of common methods of mathematical operations for data processing in MS Excel, and provides their comparison including measures to eliminate the undesirable effect.