I have a class of problem in which the total flow or cumulative pressure drop
in one pipe is the sum of flows and the maximum pressure drop in all
tributary pipes. I use the database sum and max functions to get the
tributary values. The criteria for tributaries is a part of the record for
that pipe. However, I find no way to put that criteria into one row, so each
pipe has to have two rows. That messes up a lot of editing fuctions such as
copying values or formulae to all pipes. The spreadsheet is also twice as
tall. I can't just transpose the spreadsheet because I am using the database
functions in the other orientation as well. Is there a way to have the
criteria vector horizontal instead of vertical?

Thomas,

Almost all of the Database functions can be replace with either sumproduct or array formulas...
post a small sample of your data, your formula usage, and what result you expect, and perhaps we can
come up with a single cell technique to return the desired data.

HTH,
Bernie
MS Excel MVP


"Thomas R. Glass" wrote in message
...
I have a class of problem in which the total flow or cumulative pressure drop
in one pipe is the sum of flows and the maximum pressure drop in all
tributary pipes. I use the database sum and max functions to get the
tributary values. The criteria for tributaries is a part of the record for
that pipe. However, I find no way to put that criteria into one row, so each
pipe has to have two rows. That messes up a lot of editing fuctions such as
copying values or formulae to all pipes. The spreadsheet is also twice as
tall. I can't just transpose the spreadsheet because I am using the database
functions in the other orientation as well. Is there a way to have the
criteria vector horizontal instead of vertical?

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.

"Bernie Deitrick" wrote:

Thomas,

Almost all of the Database functions can be replace with either sumproduct or array formulas...
post a small sample of your data, your formula usage, and what result you expect, and perhaps we can
come up with a single cell technique to return the desired data.

HTH,
Bernie
MS Excel MVP


"Thomas R. Glass" wrote in message
...
I have a class of problem in which the total flow or cumulative pressure drop
in one pipe is the sum of flows and the maximum pressure drop in all
tributary pipes. I use the database sum and max functions to get the
tributary values. The criteria for tributaries is a part of the record for
that pipe. However, I find no way to put that criteria into one row, so each
pipe has to have two rows. That messes up a lot of editing fuctions such as
copying values or formulae to all pipes. The spreadsheet is also twice as
tall. I can't just transpose the spreadsheet because I am using the database
functions in the other orientation as well. Is there a way to have the
criteria vector horizontal instead of vertical?

Thomas,

E-mail me a workbook privately by hitting reply, then fixing my address by taking out the spaces and
changing Dot to .

HTH,
Bernie
MS Excel MVP


"Thomas R. Glass" wrote in message
...
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.

"Bernie Deitrick" wrote:

Thomas,

Almost all of the Database functions can be replace with either sumproduct or array formulas...
post a small sample of your data, your formula usage, and what result you expect, and perhaps we
can
come up with a single cell technique to return the desired data.

HTH,
Bernie
MS Excel MVP


"Thomas R. Glass" wrote in message
...
I have a class of problem in which the total flow or cumulative pressure drop
in one pipe is the sum of flows and the maximum pressure drop in all
tributary pipes. I use the database sum and max functions to get the
tributary values. The criteria for tributaries is a part of the record for
that pipe. However, I find no way to put that criteria into one row, so each
pipe has to have two rows. That messes up a lot of editing fuctions such as
copying values or formulae to all pipes. The spreadsheet is also twice as
tall. I can't just transpose the spreadsheet because I am using the database
functions in the other orientation as well. Is there a way to have the
criteria vector horizontal instead of vertical?