Article View: comp.apps.spreadsheets
Article #49@DSUM, other database functions don't work in 123 2.2 - why?
From: Marshall Abrams
Date: Wed, 05 Aug 1992 00:11
Date: Wed, 05 Aug 1992 00:11
68 lines
3455 bytes
3455 bytes
I'm using Lotus 123 v. 2.2, and it would be really handy to use
the @DSUM function. (This is one of the "database" functions. You
set up a "database" by entering data in a particular configuration,
and then refer to with a range that includes the labels above
the columns, etc. @DSUM(input_range,field_number,criteria_range) is
supposed to return the sum of ever cell in field field_number in
input_range that meets the criteria in criteria_range.) But it
doesn't work right. (Or else I'm not using it right.)
I've tried all of the database functions except @DSTD and @DVAR, and
only @DCOUNT seems to work. (This counts the cells in the input_range
that meet the criteria in criteria_range.) @DCOUNT works fine, but
the other functions, including @DSUM, always return 0.
For example (actual contents of each cell are listed--not what's
displayed in the cell):
A B C D
1 'SALARY 'SALARY
2 90000 +A2>=83000
3 56000
4 25000
5 35500 @DCOUNT(A1..A19,0,C1..C2) <---- returns 3
6 22000 @DSUM(A1..A19,0,C1..C2) <---- returns 0 !?!
7 48000
8 23000
9 32500
10 85000
11 55000
12 83000
13 21000
14 42500
15 64500
16 65000
17 82500
18 56000
19 26800
(The next two paragraphs explain what's going on here. If it's
obvious to you what's going on here, you can skip them (although they
may explain my misunderstanding).)
A1..A19 is the input range. This is the database (with only one
field). The 0 in the @ functions refers to the first (and only)
field. C1..C2 is the criteria range. The @DCOUNT formula says "count
how many records there are in A1..A19 that meet the criterion in
C1..C2". (The field specifier, 0, doesn't make any differece in this
function, as far as I can see.) The criterion range says something
like "every row in the A column ("A2") in the input range which is
greater than or equal to 83000. (I think that the label "SALARY" in
the top cell in the criteria range isn't necessary--I'm not sure.)
The @DSUM formula is supposed to say, I think, "Sum every cell in
column 0 (column A) of the input range (A1..A19--well, A2..A19 are the
ones that contain data, and hence get checked) which meet the criteria
in C1..C2". That is, "Sum every cell in column A which in rows in
which the cell in column A is greater than or equal to 83000". (The
criterion in C2 is the part that says "rows in which the cell in
column A is greater than or equal to 83000".)
The @DCOUNT formula returns 3, which is right. There are 3 records in
which column A is greater than or equal to 83000. But the @DSUM
formula returns 0--at least, that's what it does with my version of
123.
Anybody know anything about this? Am I misunderstanding something?
Thanks very very much in advance.
Technical Details
Message-ID:
<1992Aug5.001115.21168@midway.uchicago.edu>Path:
arc1-ca.pugleaf.net!sparky!uunet!usc!wupost!uwm.edu!ux1.cso.uiuc.edu!uchinews!quads!ab2rShow Full Headers
Complete Article Headers
Newsgroups: comp.apps.spreadsheets Path: arc1-ca.pugleaf.net!sparky!uunet!usc!wupost!uwm.edu!ux1.cso.uiuc.edu!uchinews!quads!ab2r From: ab2r@quads.uchicago.edu (Marshall Abrams) Subject: @DSUM, other database functions don't work in 123 2.2 - why? Message-ID: <1992Aug5.001115.21168@midway.uchicago.edu> Summary: Only @DCOUNT works--the rest return 0 Keywords: 123 Lotus database Sender: news@uchinews.uchicago.edu (News System) Reply-To: ab2r@midway.uchicago.edu Organization: University of Chicago Computing Organizations Distribution: comp Date: Wed, 5 Aug 1992 00:11:15 GMT Lines: 68