spreadsheet subtotals

Forum: Apps
Topic: spreadsheet subtotals
started by: dbickin

Posted by dbickin on July 01 2005,15:37

I am looking to insert subtotals into a table of values. On other spreadsheets, I'd use the tool called "sub-totals." As near as I can tell, SIAG doesn't have any such tool. Am I right? (Or is it there, just under a different name?)

Assuming it doesn't, what other options do I have? The Open Office DSL says it needs 384MB of ram, and my laptop has 32! (Which is a shame as OpenOffice's subtotal blows Micorsoft's out of the water.)

Are there decent instructions on how to use SIAG? I went to the SIAG home page, and their instructions left me even more clueless. I think I can build my own subtotal routine, but I would need a decent tutorial on how to create a scheme routine, since I don't know that language and SIAG's examples are largely uncommented.

Thanks,
David

Posted by Greg on July 06 2005,01:49

David,

Let's say you wanted to sum "B6 through B13". You would type the following:

r_sum(B6..B13)

This should help you out.

Greg

Posted by SaidinUnleashed on July 06 2005,02:37

SIAG uses pretty much the same commands, functions, etc as every spreadsheet ever created.

Even the routines I used to use Lotus123 with work fine.

Hunt for a generic spreadsheet manual, if you really need it.

-J.P.

Posted by dbickin on July 06 2005,23:23

I'm talking about the subtotal tool that takes a list, finds all row with the same key value, then inserts a subtotal command below that group to show the sum or count or average or whatever. Yes, you can manually do that, but if you have thousands of rows, that becomes tedious.

So the function I would need to create, assuming it doesn't already exist, would have to sort the table, then examine each line to see if the key value has changed, inserting a row when necessary.

Preferable would be to use another spreadsheet program that already includes such functionality. Open Office is out because it is too big. Is there another spreadsheet that would do what I want to do?

Thanks,
David

Posted by clacker on July 07 2005,14:14

dbickin, I found the < Online Siag Docs > very helpful. Is the function you are looking for countif:

countif(A:1..B:7,"=10")

Counts all of the cells that contain 10. You can also look for ranges like < or >.

I also found siag difficult to use and non-intuitive. It's good points are it's free and it's small.

EDIT:

It also uses scheme (similar to lisp) as it's macro language. I like lisp a lot (autocad) but getting any clues how to use siag was very frustrating to me. I was eventually able to find that typing a ( character opens up a dialog where you can write scheme code. Even the basic scheme commands like set-data and get-point were undocumented. Once you get past that, I think that the scheme lets you accomplish many very wild things. You could do what you want to by running the sort command, and then looping through every line and seeing if it changed from the line above it. If it did, you could use the (insert-line) command to insert a line and the countif command to get a count of those values.

The best I could do was create a text file in beaver called test.scm which I loaded using the (load "test.scm") command. I was able to define a function to do things, but I wasn't able to get the where loop to increment. My frustration at the poor documentation finally made me give up. Something like:

Code Sample

(define (tt)
(while (not (null? (href -1)))
(next-line)
)
)

fails but this works to find blank lines:

Code Sample

(define (tt)
(if (not (null? (href -1)))
(next-line)
)
)

Why, I have no idea. Is it a bug? A feature? Poor documentation really detracts from what feels like it could be a great little spreadsheat.

If anyone has a siag cheatsheet, I'd be very interested.

Posted by dbickin on July 07 2005,18:10

I'm having a really bad morning, and even on my best day I tend to be too blunt. So let me type really slow. (So my brain can keep up with my fingers. No insult intended to the reader.)

First off, Clacker, SaidInUnleashed and Greg, thanks for your answers. From them, I am guessing what I want to do with SIAG is simply not something that it is intended to do.

Secondly, thanks to Clacker for giving the link to the SIAG documents. What I need, I could not find, and that is how to use the scheme language to emulate subtotals..... which is a program, not a function! I.e. the outcome is modifying the underlying buffer by inserting rows and calculating values and putting those values at the correct spots in the buffer. Unless I am being really dense, r_sum and countif only return a single value in the cell in which you place it.

In my more ambitious days, I'd probably have taken the function prototypes found in the documentation and played with them till I figured out how to combine them to get the results I wanted. I am not that ambitious anymore. I was hoping to find something simliar to what I wanted, that I could then modify.

Anyway, thanks for trying.
David

Posted by clacker on July 07 2005,18:20

dbickin, I'm sorry, but I edited my previous post while you were posting the above.

You're correct that you need to write the function in scheme, if I understood what you were asking. You can try it, but I had problems with the current line not incrementing.

Posted by clacker on July 07 2005,21:35

dbickin, I found a ton of undocumented function in the /KNOPPIX/usr/local/share/siag/ directory and especially in /KNOPPIX/usr/local/share/siag/siag/siag.scm. There are usefull things like llpr to write to the status bar and number->string to do conversions.

The power is in there, but the extreme lack of documentation means you have to hunt around a bit.

Here is some scheme code I wrote for siag to take a sorted column of numbers and add a blank line between the groups. You might be able to use it as a start for what you want to do:

Code Sample

Posted by dbickin on July 07 2005,23:25

clacker, thank you!!!! That is exactly the kind of code sample I was looking for. The only thing missing is actually inserting the r_sum or count function where it needs to go.

Again,
Thanks.

David

Posted by clacker on July 08 2005,12:48

dbickin, try this version. I added another function to do the counting. It creates a new row and places the sum in the next column. You should be able to see what's going on with siag from this example so that you can modify it to be what you realy wanted. set-data is the function that puts values into cells. You can look at some of the scm files on the dsl CD to see more examples of its use. I'm hacking and slashing here, I'm sure you can do this better.

Code Sample

(define (ttt)
(set! myrow R) ;; set our counter to the current row
(while (not (null? (@ myrow C))) ;; check that the value exists
(next-line) ;; move to the next line
(if (not (= (@ myrow C) (@ (+ myrow 1) C)));; are the 2 cells =
(prog1
(set! groupsum (sub-count))
(insert-line) ;; add extra counting and summing here
(next-line) ;; go to the next line
(set-data nil (number->string groupsum) 0 LABEL (make-position (+ R myrow)(+ C 1)))
(set! myrow (+ myrow 1));; increase the row counter
)
)
(set! myrow (+ myrow 1)) ;; increase the row counter
)
)

(define (sub-count)
(set! mycount 0)
(set! mytarget (@ myrow C))
(while (and (> (- myrow mycount) 0) (= mytarget (@ (- myrow mycount) C)))
(set! mycount (+ mycount 1))
)
mycount
)

Posted by dbickin on July 08 2005,18:01

I modified it to add the values in a second column passed as an argument

Code Sample

(define (ttt mytotalcolumn)
(set! myrow R) ;; set our counter to the current row
(while (not (null? (@ myrow C))) ;; check that the value exists
(next-line) ;; move to the next line
(if (not (= (@ myrow C) (@ (+ myrow 1) C)));; are the 2 cells =
(prog1
(set! groupsum (sub-total mytotalcolumn))
(insert-line) ;; add extra counting and summing here
(next-line) ;; go to the next line
(set-data nil (number->string groupsum) 0 LABEL (make-position (+ R myrow) mytotalcolumn ))
(set! myrow (+ myrow 1));; increase the row counter
)
)
(set! myrow (+ myrow 1)) ;; increase the row counter
)
)

(define (sub-total mytotalcolumn)
(set! mytotal 0)
(set! mycount 0)
(set! mytarget (@ myrow C))
(while (and (> (- myrow mycount) 0) (= mytarget (@ (- myrow mycount) C)))
(set! mytotal (+ mytotal ( @ (- myrow mycount) mytotalcolumn) ) )
(set! mycount (+ mycount 1))
)
mytotal
)

(Hopefully I transcribed the changes correctly. my DSL laptop is not networked!)

I'm going to have to see if I can figure out how to get it to work when the key column has text instead of numbers in it. And see if I can figure out how to make the totals show in bold. (I hate that Excel doesn't do that! OpenOffice got it right.)

Clacker, in your searches through the files, have to run into anything like an Outline in OpenOffice Calc. That is... [+] or [-] button to the left of the spreadsheet tht either shows or hides the detail of each subtotal function?

H'm, I suppose I should look to see if siag CAN hide or show rows before I ask a question like that..... Oh well. Much to learn.

Thanks for all your help.

David

Posted by clacker on July 08 2005,21:16

Showing things in bold using the scheme interface is accomplished with a (toggle-format "bold") command. I searched the /KNOPPIX/usr/local/share/siag/siag/siag.scm file for that.

No clue how to hide rows like excel can. I tried setting the row height to 1 pixel, but that didn't work.