Thread View: comp.apps.spreadsheets
3 messages
3 total messages
Started by david.b.wood
Wed, 26 Aug 1992 17:07
help: 123 data table, @dsum()
Author: david.b.wood
Date: Wed, 26 Aug 1992 17:07
Date: Wed, 26 Aug 1992 17:07
12 lines
871 bytes
871 bytes
I have a small database of expenses listed by date, and I want to generate a data table of per-month totals (actually, from the 15th of one month to the 14th of the next, although I can't make it work even for calendar months). When I set up the data table, the criterion range doesn't seem to take its date input as variable: I get the same sum for every month. (123 version 2.01) How do I set up such a table? (I want to use data table to compute the table only as necessary, since it takes forever.) Thanks, Dave Wood dbw@germany.att.com
Re: help: 123 data table, @dsum()
Author: Maria Spicer
Date: Wed, 26 Aug 1992 21:28
Date: Wed, 26 Aug 1992 21:28
43 lines
2340 bytes
2340 bytes
Hi Dave! I have run up against this problem in the past when trying to make data tables of SEX vs. AGE, where I wanted AGE to be say, <18, 18-25, 26-30, etc., much as you want your dates to be 15Jun92-14Jul92, 15Jul92-14Aug92, etc., as I understand it. The only solution I was ever able to come up with was to create another column next to the AGE column, called "AGEGRP", where I used a lookup table to assign values based on which range the age fell into. For example: | A | B | C | D | E | --|------|-------|-------|-------|-------| 1 | AGE | AGEGrp| | lookup table | 2 | 15 | 1 | | 17 | 1 | 3 | 22 | 2 | | 25 | 2 | 4 | 21 | 2 | | 30 | 3 | 5 | 43 | 6 | | 35 | 4 | 6 | 17 | 1 | | 40 | 5 | 7 | 39 | 4 | | 45 | 6 | Where the formulas in column B would be fashioned after B2: @vlookup(A2,$table,1), and the range named "table" is D2..E7. Having done this, you can run /Data Table on the values in column B rather than column A, knowing that AgeGrp=1 means Age<18, AgeGrp=2 means Age is 18-25, etc. This same idea could be applied to your date dilemma. Use a formula to create a column of date values equal to the 15th of every month for the given year. Then use the same sort of scheme to look up a DateGrp variable and run your table on it. There may be a more succinct solution out there, but I haven't found it. Send me E-Mail if you want more help with this one. Have fun! Maria -- ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ^ Maria Spicer, DP Specialist maria@soc.duke.edu ^ ^ 140 Soc-Psych Bldg 919-660-5602 ^ ^ Duke University, Durham, NC 919-660-4317 ^
Re: help: 123 data table, @dsum()
Author: R. J. Glew
Date: Thu, 27 Aug 1992 17:15
Date: Thu, 27 Aug 1992 17:15
82 lines
2804 bytes
2804 bytes
In article <1992Aug26.170745.29793@cbnewsj.cb.att.com> dbw@cbnewsj.cb.att.com (david.b.wood) writes:
>I have a small database of expenses listed by date, and I want to
>generate a data table of per-month totals (actually, from the 15th of
>one month to the 14th of the next, although I can't make it work even
>for calendar months). When I set up the data table, the criterion
>range doesn't seem to take its date input as variable: I get the same
>sum for every month. (123 version 2.01)
>How do I set up such a table? (I want to use data table to compute the
>table only as necessary, since it takes forever.)
>Thanks,
>Dave Wood dbw@germany.att.com
In response to your question:
A B C D E F G
1 Date Cash
2 --------------------
3 25-May-92 608.65
4 29-May-92 627.17
5 01-Jun-92 935.74
6 03-Jun-92 150.94
7 08-Jun-92 230.04
8 22-Jun-92 603.97
9 22-Jun-92 888.00
10 28-Jun-92 984.42
11 08-Jul-92 576.67
12 11-Jul-92 341.09
13 12-Jul-92 107.34
14 15-Jul-92 216.70
15 18-Jul-92 985.83
16 25-Jul-92 676.99
17 02-Aug-92 46.07
18 05-Aug-92 728.39
19 08-Aug-92 402.64
20 20-Aug-92 291.04
21 22-Aug-92 873.09
22 24-Aug-92 937.98
23 26-Aug-92 584.15
24 27-Aug-92 147.53
25 --------------------
26 Total 11,944.44
27 ====================
28
29
30 Date Date Date Date
31 May-Jun 2,552.54 0 0 0 0
32 Jun-Jul 3,501.49
33 Jul-Aug 3,056.62
34 Aug-Sep 2,833.79
35 --------------------
36 Total 11,944.44
37 ====================
A B C D E F G
The working formulae follow:
D30: 'Date
E30: 'Date
F30: 'Date
G30: 'Date
A31: 'May-Jun
B31: @DSUM(A1..B25,1,D30..D31)
D31: +A2>=@DATE(92,5,15)#AND#A2<@DATE(92,6,15)
E31: +A2>=@DATE(92,6,15)#AND#A2<@DATE(92,7,15)
F31: +A2>=@DATE(92,7,15)#AND#$A2<@DATE(92,8,15)
G31: +A2>=@DATE(92,8,15)#AND#A2<@DATE(92,9,15)
A32: 'Jun-Jul
B32: @DSUM(A1..B25,1,E30..E31)
A33: 'Jul-Aug
B33: @DSUM(A1..B25,1,F30..F31)
A34: 'Aug-Sep
B34: @DSUM(A1..B25,1,G30..G31)
Richard Glew
Lakehead University
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