🚀 go-pugleaf

thompson@atlas.socsci.umn.edu (T. Scott Thompson) writes:

>I only use Quattro for my own work, so my "dummy-proofing" is of a very
>personal variety!  However, the following has worked well for me:

>To protect the top and bottom of a COLUMN range, I simply add a cell
>to the top and bottom, include those cells in the range, and
>(optionally) insert something in them that the numeric functions will
>ignore (like a label).  

    Unfortunately, a rather large number of numerical functions do not
ignore labels: they treat them as if they are a valid number with a value
of zero. IMHO this is a major disadvantage of spreadsheets for any serious
numerical work. I would think that the only safe option in your solution
would be the completely blank cell. Is there another trick that you can
use to prevent this problem?

>                        Then I protect just the two "guard cells".  I
>end up with something like this:


>              1      2      3
>         A        -------
>         B          1.2
>         C          3.4
>         D          5.6
>         E        -------
>         F         10.2

>Here I would make A2-E2 be the named range. (Say "DATA") I put the
>labels "/-" in A2 and in E2, which are protected.  B2-D2 are not
>protected, so that they can be erased, deleted, etc.  Cell F2 might
>have something like @SUM(DATA), which simply ignores the labels in A2
>and E2.

   Yes, the @SUM would give the correct total, but @AVG or @COUNT will
think that 5 values are present.

>This scheme also makes it easy to add an extra cell to the top or
>bottom of the range and have it be included in the range
>automatically.

>The same scheme should also work for ROW ranges.  If you do this then
>you can evey use "Hide Column" to prevent the columns corresponding to
>A2 and E2 in the example from showing up.

    This would also make it very easy to leave the cell completely blank,
so all functions will work in the expected way.



Dave Halliwell
Department of Geography
University of Alberta
Edmonton, Alberta