I'm not sure how to post a sample. Ths is cut and pasted from a worksheet:
PIPE TREE SIZER
LOAD FROM bFLOW LEN K_ftg fCv SZ VEL VH F.R. bWPD bΣWPD
COIL
AHU-A1 ZPRV-A 30.0 15 2.00 10.00 2 3.1 0.15 2.01 10.59 10.6
COIL
SPARE A ZPRV-A 50.0 15 2.00 10.00 2 1/2 3.3 0.17 1.75 10.59 10.6
FROM
ZPRV-A Total 80.0 180 2.00 3 3.6 0.20 1.72 3.50 14.1
COIL
AHU-AB1 ZPRV-B 19.9 15 2.00 10.00 2 2.0 0.06 0.95 10.27 10.3
COIL
AHU-B4 ZPRV-B 39.3 15 2.00 10.00 2 1/2 2.6 0.10 1.12 10.37 10.4
COIL
SPARE B ZPRV-B 50.0 15 2.00 10.00 2 1/2 3.3 0.17 1.75 10.59 10.6
FROM
ZPRV-B Total 109.3 245 2.00 3 5.0 0.38 3.06 8.26 18.8
FROM
TOTAL 189.3 245 2.00 4 4.8 0.36 2.09 5.83 24.7
End of List
The rows starting with "Coil" are non-funcitonal, since "Coil" is not one of
the database headings. The rows starting with "From" are the first row of a
two-cell "Criteria", such as "From : Total"
DSUM formula, in column headed by cell "E$6" (bFlow), :
=DSUM(Grey,E$6,$C42:$C43)
"grey" is the whole database; everything pasted above is in it.
"$C42:$C43" is "From" : "ZPRV-A"
This formula looks through all pipe segments (rows) and finds the ones fed
"From" the pipe represented by this pipe (named "ZPRV-A", row 43). It adds
the flows ("bFlow", column E) of all the branches fed by ZPRV-A to determine
the flow in ZPRV-A.
DMAX formula, in column "bΣWPD", headed by cell "O$6":
=DMAX(Grey,O$6,$C42:$C43)+N43
This formula is very similar, but picks the maximum pressure drop in any
branch "From" the current pipe and adds it to the pressure drop of the
current pipe to determine the total pressure drop from the start of this pipe
to the end of the system.
I would be glad to post or e-mail the spreadsheet if you can tell me how.
The problem is that the Database functions and Criteria both assume that
records are rows and categories are columns. That is the structure of my
database, except that one of my categories is a Criteria, which also has to
be a vertical database, and thus takes at least two rows. If the Criteria
("my source is this other pipe in this datbase") could be horizontal, it
could be in one row with the other properties of each pipe.
What I want is:
=DSUM(database, property, hcriteria)
which sums the values in column "property" of each record in "database" that
match the "hcriteria". "hcriteria" would be just like criteria, but would be
a horizontal vector that could be included in one record.
In another form:
=DSUM(database, property, match, criteriacell, )
which sums the values in column "property" of each record in "database" in
which the value in column "match" matches the "criteriacell", which is a
single cell. (In my application, the "criteria" cell would be in the row
(record) that includs this function.)
For my specific application, the following function would be simpler:
=DROOTSUM(database, property)
which sums the values in column "property" of each record in "database" in
which the value in the second column (root name) matches the first column
(record name) in the row (record) that includs this function. The first two
columns in "Database" would be "record name" and "root name"
Thanks.
