Thread View: comp.apps.spreadsheets
2 messages
2 total messages
Started by Marshall Abrams
Wed, 05 Aug 1992 00:11
@DSUM, other database functions don't work in 123 2.2 - why?
Author: 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.
Re: @DSUM, other database functions don't work in 123 2.2 - why?
Author: Sparky
Date: Wed, 05 Aug 1992 14:27
Date: Wed, 05 Aug 1992 14:27
56 lines
2525 bytes
2525 bytes
In article <1992Aug5.001115.21168@midway.uchicago.edu> ab2r@quads.uchicago.edu (Marshall Abrams) writes: >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 > [STUFF DELETED] The problem also occurs in Excel 3.0. Try changing your criterion to exclude the cell reference. (As shown below). It should then work. SALARY >=83000 My guess why: Your criteria as entered is a formula which evaluates to true or false. (Cell A2>=83000 is either a true statement or a false one. TRUE in this case.) Lotus/Excel then uses TRUE as a criterion and sums the values in column A that contain the value TRUE. None do, so zero is the sum. I have no idea why DCOUNT works. (it doesn't work in Excel with the example you've given.) -- Sparky
Thread Navigation
This is a paginated view of messages in the thread with full content displayed inline.
Messages are displayed in chronological order, with the original post highlighted in green.
Use pagination controls to navigate through all messages in large threads.
Back to All Threads