unofficial mirror of help-gnu-emacs@gnu.org
 help / color / mirror / Atom feed
* blank cells in ses range
@ 2004-11-26 21:11 rb
  2004-11-27 17:32 ` Alan Mackenzie
  2004-11-27 22:08 ` Stefan Monnier
  0 siblings, 2 replies; 8+ messages in thread
From: rb @ 2004-11-26 21:11 UTC (permalink / raw)


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
-- 

^ permalink raw reply	[flat|nested] 8+ messages in thread

* Re: blank cells in ses range
  2004-11-26 21:11 blank cells in ses range rb
@ 2004-11-27 17:32 ` Alan Mackenzie
  2004-11-27 21:49   ` rb
  2004-11-27 22:08 ` Stefan Monnier
  1 sibling, 1 reply; 8+ messages in thread
From: Alan Mackenzie @ 2004-11-27 17:32 UTC (permalink / raw)


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").

^ permalink raw reply	[flat|nested] 8+ messages in thread

* Re: blank cells in ses range
  2004-11-27 17:32 ` Alan Mackenzie
@ 2004-11-27 21:49   ` rb
  0 siblings, 0 replies; 8+ messages in thread
From: rb @ 2004-11-27 21:49 UTC (permalink / raw)


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
-- 

^ permalink raw reply	[flat|nested] 8+ messages in thread

* Re: blank cells in ses range
  2004-11-26 21:11 blank cells in ses range rb
  2004-11-27 17:32 ` Alan Mackenzie
@ 2004-11-27 22:08 ` Stefan Monnier
  2004-11-27 23:52   ` rb
  2004-11-28  0:54   ` rb
  1 sibling, 2 replies; 8+ messages in thread
From: Stefan Monnier @ 2004-11-27 22:08 UTC (permalink / raw)


> 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

^ permalink raw reply	[flat|nested] 8+ messages in thread

* Re: blank cells in ses range
  2004-11-27 22:08 ` Stefan Monnier
@ 2004-11-27 23:52   ` rb
  2004-11-28  0:54   ` rb
  1 sibling, 0 replies; 8+ messages in thread
From: rb @ 2004-11-27 23:52 UTC (permalink / raw)


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
-- 

^ permalink raw reply	[flat|nested] 8+ messages in thread

* Re: blank cells in ses range
  2004-11-27 22:08 ` Stefan Monnier
  2004-11-27 23:52   ` rb
@ 2004-11-28  0:54   ` rb
  2004-11-29 15:05     ` Stefan Monnier
  1 sibling, 1 reply; 8+ messages in thread
From: rb @ 2004-11-28  0:54 UTC (permalink / raw)


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
-- 

^ permalink raw reply	[flat|nested] 8+ messages in thread

* Re: blank cells in ses range
  2004-11-28  0:54   ` rb
@ 2004-11-29 15:05     ` Stefan Monnier
  2004-11-29 17:05       ` Jonathan Yavner
  0 siblings, 1 reply; 8+ messages in thread
From: Stefan Monnier @ 2004-11-29 15:05 UTC (permalink / raw)


> 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

^ permalink raw reply	[flat|nested] 8+ messages in thread

* Re: blank cells in ses range
  2004-11-29 15:05     ` Stefan Monnier
@ 2004-11-29 17:05       ` Jonathan Yavner
  0 siblings, 0 replies; 8+ messages in thread
From: Jonathan Yavner @ 2004-11-29 17:05 UTC (permalink / raw)


> 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.

^ permalink raw reply	[flat|nested] 8+ messages in thread

end of thread, other threads:[~2004-11-29 17:05 UTC | newest]

Thread overview: 8+ messages (download: mbox.gz follow: Atom feed
-- links below jump to the message on this page --
2004-11-26 21:11 blank cells in ses range rb
2004-11-27 17:32 ` Alan Mackenzie
2004-11-27 21:49   ` rb
2004-11-27 22:08 ` Stefan Monnier
2004-11-27 23:52   ` rb
2004-11-28  0:54   ` rb
2004-11-29 15:05     ` Stefan Monnier
2004-11-29 17:05       ` Jonathan Yavner

This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox;
as well as URLs for read-only IMAP folder(s) and NNTP newsgroup(s).