A clinical center, asked us to help them make a spreadsheet book. This book contains the data of the patients visited, coming from different mutuals, through an import from their management database. The spreadsheets group together the number of visits made per month and within each month, by mutual and by services performed.
We proposed a book with thirteen spreadsheets, one for each month, plus a sheet to show the totals.
The difficulty of the calculation came because not every month, they presented values for all of the mutuals in the different services. In other words, there could be a lack of mutuals and there could be a lack of values in various services. There were empty or missing boxes.
We made the appropriate formulas, so that the totals could be executed, without the problems derived from the lack of values. We used conditional functions, lookup and reference functions, and information functions, to verify if the searched value gave an error (#N/A) or if the value was a number.
We show as an example one month, with similar data to those received in the import. We can see that some boxes appear empty and that the identifier of two mutuals is missing. In the header of each column, we have each of the services:
The total sheet, allows you to select the Service and then, makes the calculation of the Annual Total, for each Mutual and for each month:
We show an enlarged image in which you can see how the services can be changed. The totals change according to the selection made:
Application made with Excel. To use this application you need to have the MS Excel program. The application is made in Spanish language. Note: The data inserted in the images are fictitious.
