🚀 go-pugleaf

------------------------- Original Article -------------------------
Path: news.univie.ac.at!hp4at!mcsun!uunet!dtix!darwin.sura.net!jvnc.net!yale.edu
From: qqc@mace.cc.purdue.edu (Dm Forsyth)
Newsgroups: comp.apps.spreadsheets
Subject: Re: making check catagory acc
Message-ID: <BuF9GM.3w5@mentor.cc.purdue.edu>
Date: 11 Sep 92 16:21:57 GMT
References: <31.97.uupcb@pcb.batpad.lgb.ca.us>
Sender: news@mentor.cc.purdue.edu (USENET News)
Organization: Purdue University
Lines: 60

In article <31.97.uupcb@pcb.batpad.lgb.ca.us> ken.mason@pcb.batpad.lgb.ca.us (Ke
>DF.I want to add additional columns (1 associated with each column of values)
>DF.in which to enter the expense catagory (probably numbers between 1 and 15).
>
>        I'm interested in your problem, but you lost me here.  Can you
>        post an extract of how you want it to look, then what you want
>        to do with your data?
>

Let me restate my question, hopefully more clearly:
  (forget for the moment that I do this in 4 columns instead of 1, just to
fit onto one screen; if necessary, I can repeat the procedure 4 times for that)

I want to have 3 columns:
	1 for a 0/1 value for checks not back from bank
		This is for "database extraction" for outstanding checks,
		and this works, no question here.
	1 for the value of the check
	1 for the catagory of the expense (values maybe 1 to 15)
		This is the part I have the question about.  How to sum
		catagories.

The data could look like this:
	IN	CHECK	CATAGORY
	1	12.00	1
		15.00	5
	1	18.47	1	etc.

It seems there should be some simple way of getting the sums by catagory.

	In this abbreviated example, the sum for catagory 1 should be 30.47
		sums for catagories 2-4 are 0
		sum for catagory 5 = 15
		etc.

I've used @dsum(), in this way:
	Make a criterion table with the titles on one row
		Then on consecutive following rows, 1, 2, 3, .. 15.

It looks something like this:  CHECK	CATAGORY
					1
					2
					3  etc

Then I have a column of @dsums-previous @dsum.
In other words, it looks like
	@dsum(b20.c30,0,k20.L21)
	@dsum(b20.c30,0,k20.L22)-@dsum(b20.c30,0,k20.L21)
	@dsum(b20.c30,0,k20.L23)-@dsum(b20.c30,0,k20.L22)

THIS WORKS!!!!
But, it takes an awfully lot of formulas and seems very messy to do something
that must have been done many times.

WHO HAS A BETTER WAY?

 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Dale M. Forsyth |  Assoc. Prof. | Purdue University Animal Sciences
qqc@mace.cc.purdue.edu | phone 49-44841 | campus address 2-111 Lilly
 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

if you are useing excel 4.0 you can enter a formula like

=sum(if(b1:b10=3),c1:c10,0))

and enter this as an array formula
(pressing shift-ctrl-return after entering it, ob macs it is
apple-return, i think)

NO ADDITIONAL ROWS AND COLUMNS NEEDED!
array formulas are an extremely useful tool only available in excel!