Aug 20 2023 06:00 PM
Aug 20 2023 06:00 PM
My place of business uses multiple pre-built large-scale spreadsheets to enter data that helps us evaluate how things are going. For example, the largest spreadsheet is 63 columns wide with monthly pages. There is space to include daily data in most of those columns which are calculated for average, minimum, and maximum at the bottom of the sheet. In addition, a number of columns have their own calculations from data drawn from the other columns. Almost all of these calculation columns have division by zero errors. Other spreadsheets are built in other ways with their own division by zero issues.
Anytime my mouse crosses one of these cells with this error, I get a pop-up which interferes with my ability to continue working. This can be quite the hassle. While I know how to create equations which account for division by zero, I am not authorized to do so. And given the sheer volume of spreadsheets and cells that have this problem, I don't want this responsibility anyway.
I would like these error messages to go away so I can do my job quickly and efficiently. However, I cannot find any setting which will stop these pop-ups. Is there anything I can do to stop this from happening?
Thanks in advance.
Aug 20 2023 06:12 PM
Quite frankly, what you describe sounds like its ripe for total redesign. I'm attaching a slightly dated article on design.....among other things, you will notice a strong emphasis on separate sections, if not separate tabs, for (a) input (b) logic or processing, and (c) output or reports or summaries. It sounds very much as if you're mixing all those.in on each sheet.
Other than that, to answer your question of what you can do about all those Division by Zero error messages: talk with the person who IS responsible for maintaining it.
Aug 20 2023 07:02 PM
Thank you for your response. Unfortunately, nobody maintains these spreadsheets. They're simply copied with dates updated year after year. This has been going on since before I came to work here ten years ago. The original author is long gone.
Other than myself, I know of nobody here with the skill set to correct this. Given that we have 5,000+ employees, I'm certain there are people in other departments that do have such skills but this is not their responsibility either. Sadly, the desktop version of Excel, which we used up until two years ago, doesn't have this issue. I don't know why they introduced this pop-up in the web based version but since it wasn't needed before, they certainly should have given us an option to shut it off.
Once again, thanks for your assistance.
Aug 21 2023 08:03 AM
Some workaround could be to select entire sheet, or any range you wish (range shall include at least one error), from right click menu select Ignore Errors.