• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Seeking help _ excel errors

hannahd87

New Member
Hi Everyone.

I am trying to create an error inclusive training in excel for my research, and need your help if you can with beginners errors you see the most often - whether it is with formulas, functions or else. The below list is what I compiled based on online resources. These are mostly related to formulas but any thought, idea is welcome.

- errors that are an outcome of wrong data type (eg. text is mistaken as number)
- errors due to not understanding or using absolute vs relative reference
- errors due to (Mathematical and else) operators and order of operations
- errors due to inputs and the order of those any formula syntax require (order of arguments, grouping (matching brackets), separators (comma etc.), typo errors, range (correct range type), reference etc.)

Could you please share any error issue you often see newbie users doing, explain the reason behind and may provide a concrete example?

Thank you so much in advance !!!
Hanna
 

Chihiro

Excel Ninja
Not directly related to formula and isn't error per se.

But one of most common mistake is to use cross-tab structure table as data entry sheet. This should be avoided whenever possible (i.e. alwyas ;)).
Cross-tab structure is easier for human eyes to follow data flow, but not for formula or any other analysis tools (Pivot Table etc).

Flattened data structure is key to subsequent data analysis and reporting needs. Otherwise, you'll need additional steps to transform data and/or complex formula to deal with specialized data structure.
 

Peter Bartholomew

Well-Known Member
Not directly related to formula and isn't error per se.

But one of most common mistake is to use cross-tab structure table as data entry sheet. This should be avoided whenever possible (i.e. alwyas ;)).
Cross-tab structure is easier for human eyes to follow data flow, but not for formula or any other analysis tools (Pivot Table etc).

Flattened data structure is key to subsequent data analysis and reporting needs. Otherwise, you'll need additional steps to transform data and/or complex formula to deal with specialized data structure.
I don't see it as quite that absolute. If the data fits into a relational data table for 'slicing and dicing' then I would raise no objection. Even then PQ does a good job of unpivoting before adding to a data model so other options exist. If the problem is essentially a matrix problem, considerably less common agreed, then the cross-tab works well. The addition of matrices can be performed with either data structure but it is easier to see what is going on with 2D arrays. In the cases where matrix multiplication is called for it is the 2D array that wins hands down.
 

Chihiro

Excel Ninja
If numeric matrix yes. But then, most often I find cross tab text columns.

But to be more precise, I should have said, start from flat table. Going from there to any other data structure is relatively easy. The other direction, isn't always that simple.

Even though PQ/PP is able to transform cross-tab (or most other types) to flat table... their engine are specifically designed to efficiently compress data vertically. Having cross tab as starting point will significantly impact query/refresh performance (i.e. having 5 columns & 500k rows is preferable to 250 columns & 10k rows).
 

Peter Bartholomew

Well-Known Member
Agreed; I have seen a number of workbooks where the nightmare starts with the chosen layout of the input data. The first major calculation is simply to normalise the data into a set of helper ranges / tables. That at least allows the downstream formulae to be constructed in an understandable and, hopefully, efficient manner.
 

Peter Bartholomew

Well-Known Member
@hannahd87
You may find some of Ray Panko's work of interest. The following was published by EuSpRIG and he also publishes a web site linked to his former employment at the University of Hawaii.

When thinking of spreadsheet errors one tends to focus upon the immediate errors of execution (e.g. striking the wrong key or not selecting the correct region with the mouse) but planning errors (incorrect implementation of business logic or misunderstanding Excel functionality) can be even more important and are harder to find. Besides these quantitative errors there are also qualitative errors (eg hardwired values that may be forgotten when change comes and one-off 'corrections' that may not be appropriate if the workbook were reused).

The other thing to bear in mind is that 'errors' are not the product of carelessness; they are an inherent part of human evolution. Survival requires precision to be sacrificed in order to achieve a fast response to danger. As yet, the requirements of Excel have had a limited impact on evolution :).
 
Top