Debugging for spreadsheet formulas

Talk about anything at all....

Debugging for spreadsheet formulas

Postby Lupp » Fri Mar 20, 2020 7:44 pm

Shouldn't there be a tutorial titled "How can I find possible causes of errors in spreadsheet formulas?" or alike?

There are lots of questions starting with reporting an error message concerning a formula in Calc.
Sometimes it's hardly to understand for what reasons the questioner didn't use some obvious means to get his(f/m) formula fixed.
In other cases the situation may be convoluted, and the less experienced questioner cannot be expected to find his way without the help of a mentor.

Anyway it would be helpful to be able to link to a guide concerning "error research".

Who might write such a tutorial/guide?

Well, I might try it, but there are some obstacles:
- My English is too poor. Concerning many aspects I'm not sure about how to express my thoughts.
- Related to the above: My wordings wouldn't be exactly concise. Too many and too wordy circumductions.
- As a (German) high-school teacher of math (and something) I'm infused with seeking completeness and precision.
-= This may not be highly appreciated by beginners.
On Windows 10: LibreOffice 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2896
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Debugging for spreadsheet formulas

Postby RoryOF » Fri Mar 20, 2020 7:54 pm

I encourage you to write it - at least to commence it, then circulate it privately; I have no doubt that English speakers will help with revision. When all are (reasonably) happy with it, it can then be posted as a tutorial.
Apache OpenOffice 4.1.7 on Xubuntu 18.04.4 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 31235
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Debugging for spreadsheet formulas

Postby keme » Sun Mar 22, 2020 2:36 pm

I find that, like Lupp, I have a tendency to favor "complete" over "concise". Both of us with background from school, we will have some added experience in finding what are the typical errors and what are the typical mistakes and misconceptions causing those errors.

Between us we may build something fairly complete, which we would then require help in shaving down to something concise, to avoid the TLDR trap.

Is that a workable framework for a plan?
User avatar
keme
Volunteer
 
Posts: 3358
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Debugging for spreadsheet formulas

Postby keme » Sun Mar 22, 2020 2:38 pm

Villeroy's attention to detail and practical sense would also be useful. I am sure that if the building is done in this forum, he will bring his hammer when required. ;)
User avatar
keme
Volunteer
 
Posts: 3358
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Debugging for spreadsheet formulas

Postby RoryOF » Sun Mar 22, 2020 2:45 pm

That was why I suggested it be circulated privately for revision; once there is a document, if necessary it can be split into a simple and a more complex form, perhaps hyperlinked.
Apache OpenOffice 4.1.7 on Xubuntu 18.04.4 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 31235
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Debugging for spreadsheet formulas

Postby RusselB » Sun Mar 22, 2020 2:50 pm

I will also provide any relevant input to helping make a complete, yet concise, document, with completeness taking priority over concisement.
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 6062
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Debugging for spreadsheet formulas

Postby John_Ha » Sun Mar 22, 2020 4:02 pm

I will be happy to help review any document.

The thing I find most useful it to build any formula in steps, starting with a small bit and adding another small bit, testing at each stage until it is complete. Also using one or more "helper columns" is very useful.

I also find that adding redundant spaces is very useful in clarifying a formula. I find
Code: Select all   Expand viewCollapse view
= ( (F49 + (F48 * F47) ) - SIN(F46) ) / 7

is easier to read than
Code: Select all   Expand viewCollapse view
= ((F49+(F48*F47))-SIN(F46))/7

I would like a way of using { } and [ ] as well as ( ) so matching brackets can easily be found just by examination as below rather than by clicking to the right to see the matching bracket bolded:
Code: Select all   Expand viewCollapse view
= { (F49 + [F48 * F47] ) - SIN(F46) } / 7

Strangely I have never mastered the Formula Function Wizard which I am sure would make things much easier. :crazy: I shall have to read Help!
Attachments
Clipboard01.gif
LO 6.4.4.2, Windows 10 Home 64 bit

See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
John_Ha
Volunteer
 
Posts: 7779
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: Debugging for spreadsheet formulas

Postby Villeroy » Sun Mar 22, 2020 5:07 pm

With sheets downloaded from here I tend to do the following:
1) Ctrl+A, Ctrl+M to remove all hard formatting. Since nobody uses any styles, this reveals the pure grid with raw data, numbers right aligned, text left aligned.
2) Ctrl+F8 to highlight distinct areas of interest (areas of numbers, text and formulas).
3) If there is a formula with an unexpected result, I focus the cell and hit F2 to highlight the referenced cells and ranges except of references to other sheets (unfortunately). Are the referenced cells and ranges adequate?
Before I try to understand the intention of a formula, I look for formal errors within the referenced input data and how operations are combined within a formula. As long there is a structural error, the intended use does not matter.

In pseudo-databases COUNT, COUNTA, COUNTBLANK, MIN, MAX, ROWS help to detect if a tall column is filled with consistent data.

I find the formula wizard useful when function calls are heavily nested. Playing with the position of the text cursor within the multi-line formula box, you can inspect single function calls with their arguments and intermediate results. This tool also helps to split nested formulas into simple ones: Cut a selected substring with consistent bracketing and replace it with a reference to the cell where you are going to paste the substring with a leading = .
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28434
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Debugging for spreadsheet formulas

Postby MrProgrammer » Sun Mar 22, 2020 8:18 pm

Lupp wrote:- My English is too poor. Concerning many aspects I'm not sure about how to express my thoughts.
- Related to the above: My wordings wouldn't be exactly concise. Too many and too wordy circumductions.
- As a (German) high-school teacher of math (and something) I'm infused with seeking completeness and precision.
I find Lupp's writing to be excellent. As a native English speaker I am happy to provide assistance with this project if he would like that. I often keep my posts short, replying with just a link to a solved topic or with a spreadsheet attachment, no text from me at all. My tutorials always go through a long editing period before I make them public, even for comments, as I try to present important material concisely. I am fortunate to have a personal friend who is a professional copyeditor and has shared many posts on Facebook about good (and bad) writing. I appreciate Lupp's completeness and precision, something I have tried to emulate myself in tutorials.

Lupp wrote:Shouldn't there be a tutorial titled "How can I find possible causes of errors in spreadsheet formulas?" or alike?
Many beginners may not know about using F9 to assist in debugging, though it has its limitations, especially with array formulas, a good enough reason to avoid them whenever possible. I used Excel at work many years ago and don't remember that facility there.

Tools → Detective → Trace error may help in some situations. I haven't used this feature much myself.

Many times I will copy a complicated formula to a plain text editor (the Mac equivalent of Notepad) so I can edit it to display as a nested, indented, set of functions. I've considered writing a tool, probably in Perl, to help with that. A more ambitious tool would be one which splits a formula into multiple cells, each containing just one function or one operator. Either of these tools requires a lexical analyzer and parser but I studied these subjects in college and have written some of those since then.
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3984
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Debugging for spreadsheet formulas

Postby John_Ha » Wed Mar 25, 2020 12:48 pm

MrProgrammer wrote:Many times I will copy a complicated formula to a plain text editor (the Mac equivalent of Notepad) so I can edit it to display as a nested, indented, set of functions. I've considered writing a tool, probably in Perl, to help with that.

See Easy way to make highly nested conditionals which mentions the use of Ctrl+Enter to split a formula into several lines. The split isn't "sticky" and the next time you examine the cell the formula is normal.

Clipboard01.gif
Using Ctrl+Enter to split a formula into several lines

The Calc Guide has sections entitled Strategies for creating formulas and functions and Finding and fixing errors.
LO 6.4.4.2, Windows 10 Home 64 bit

See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
John_Ha
Volunteer
 
Posts: 7779
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK


Return to General Discussion

Who is online

Users browsing this forum: No registered users and 5 guests