blank cells in ses range

blank cells in ses range

[rb]

I'm having difficulty understanding how to eliminate "empty" or "blank"
cells when performing a simple sum calculation on a range in
ses21-031130, and I would be very grateful for any assistance.

What I would like is the sum of, say, Column "E", which has rows 1 - 10,
of which approx. half are empty. 

        E
1       41
2       11
3       
4       9
5       
6       20
7       
8       
9       15
10      96

What I would like is for E10 to be (apply '+ (ses-range E1 E9)), with
the empty cells simply eliminated from the calculation. I can't figure
out how to make (ses+ &rest ARGS) work with the range calculation.

Thanks very much for any help,

rb
-- 

Re: blank cells in ses range

[Alan Mackenzie]

rb <rb@panix.com> wrote on 26 Nov 2004 16:11:39 -0500:
> What I would like is the sum of, say, Column "E", which has rows 1 -
> 10, of which approx. half are empty. 

>         E
> 1       41
> 2       11
> 3       
> 4       9
> 5       
> 6       20
> 7       
> 8       
> 9       15
> 10      96

> What I would like is for E10 to be (apply '+ (ses-range E1 E9)), with
> the empty cells simply eliminated from the calculation. I can't figure
> out how to make (ses+ &rest ARGS) work with the range calculation.

> Thanks very much for any help,

Does it have to be an Emacs solution?

I would suggest you use AWK here.  AWK is designed to cope with awkward
things (hence the name ;-) like missing fields, and so on.  The following
script would do the right thing:

# awk '{tot += $2}; END {print tot}' foo.txt

> rb
> -- 

-- 
Alan Mackenzie (Munich, Germany)
Email: aacm@muuc.dee; to decode, wherever there is a repeated letter
(like "aa"), remove half of them (leaving, say, "a").

Re: blank cells in ses range

[rb]

Alan Mackenzie <acm@muc.de> writes:

> rb <rb@panix.com> wrote on 26 Nov 2004 16:11:39 -0500:
> > What I would like is the sum of, say, Column "E", which has rows 1 -
> > 10, of which approx. half are empty. 
> 
> >         E
> > 1       41
> > 2       11
> > 3       
> > 4       9
> > 5       
> > 6       20
> > 7       
> > 8       
> > 9       15
> > 10      96
> 
> > What I would like is for E10 to be (apply '+ (ses-range E1 E9)), with
> > the empty cells simply eliminated from the calculation. I can't figure
> > out how to make (ses+ &rest ARGS) work with the range calculation.
> 
> > Thanks very much for any help,
> 
> Does it have to be an Emacs solution?

Thank you for the suggestion. Yes, though, I am trying to figure out how
to make this work within ses/emacs. The example was extracted from what
would be a much more extensive spreadsheet. My workaround is to
specifically list all the cells: (ses+ E1 E2 E3 E4 E5 E6 E7 E8 E9)
works, but I would like to find a way to exclude the empty cells from an
ses-range. I believe this is meant to be possible, I simply fail to
understand how to go about it.

Thanks again,

rb
-- 

Re: blank cells in ses range

[Stefan Monnier]

> I'm having difficulty understanding how to eliminate "empty" or "blank"
> cells when performing a simple sum calculation on a range in
> ses21-031130, and I would be very grateful for any assistance.

> What I would like is the sum of, say, Column "E", which has rows 1 - 10,
> of which approx. half are empty. 

>         E
> 1       41
> 2       11
> 3       
> 4       9
> 5       
> 6       20
> 7       
> 8       
> 9       15
> 10      96

> What I would like is for E10 to be (apply '+ (ses-range E1 E9)), with
> the empty cells simply eliminated from the calculation. I can't figure
> out how to make (ses+ &rest ARGS) work with the range calculation.

Have you tried (apply 'ses+ (ses-range E1 E9)) ?


        Stefan

Re: blank cells in ses range

[rb]

Stefan Monnier <monnier@iro.umontreal.ca> writes:

[...]

> > What I would like is for E10 to be (apply '+ (ses-range E1 E9)), with
> > the empty cells simply eliminated from the calculation. I can't figure
> > out how to make (ses+ &rest ARGS) work with the range calculation.
> 
> Have you tried (apply 'ses+ (ses-range E1 E9)) ?

no, I hadn't - lots of other combinations, but not the correct one!
thank you very much for solving this problem.

rb
-- 

Re: blank cells in ses range

[rb]

Stefan Monnier <monnier@iro.umontreal.ca> writes:

[...]

> Have you tried (apply 'ses+ (ses-range E1 E9)) ?

Thank you again for solving this. 

One other problem I've had using ses is that I don't know how to specify
a printer function which will insert a comma separator. In other words,
which will print 116789 as 116,789. If you can help clarify how to do
this, I would be grateful.

rb
-- 

Re: blank cells in ses range

[Stefan Monnier]

> One other problem I've had using ses is that I don't know how to specify
> a printer function which will insert a comma separator.  In other words,
> which will print 116789 as 116,789.  If you can help clarify how to do
> this, I would be grateful.

I don't know of any such thing either.


        Stefan

Re: blank cells in ses range

[Jonathan Yavner]

> how to specify a printer function which will insert a comma separator.

(defun format-with-commas (x)
  (setq x (number-to-string x))
  (while (string-match "\\([0-9]\\)\\([0-9][0-9][0-9]\\)\\([,.]\\|\\'\\)" x)
    (setq x (replace-match "\\1,\\2\\3" t nil x)))
  x)

It would be nicer if one could just say (format "%'d" x), but that's a can
of worms since the \' operator doesn't do anything in the POSIX locale.