Re: Criteria for datbase function in one row?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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" <ThomasRGlass@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:FB448F08-2A40-47F1-B116-ED95F38B5B08@xxxxxxxxxxxxxxxx
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?



.

Relevant Pages

  • Re: Read from database, write to another database, simultaneously
    ... interacting with the database not streaming large quantities from one ... All you need is a simple way to pipe the output from Oracle into ... Just run the oracle client and start dumping to the ...
    (comp.lang.python)
  • Custom pipe script failure code
    ... I have a quick question regarding the pipe function in Postfix and the use of PHP as a mail sorter/parser. ... I've looked around and see many people have used PHP as a quick and dirty solution for putting mail data into a database. ... What I want is to have this message return back into the normal mail queue with a temporary failure or something so it can retry at a later time without disappearing into never never land. ...
    (php.general)
  • Cannot backup WMSDE through pipe
    ... I have a backup application that has the option to backup SQL databases through pipe. ... On the same SBS 2003 the SBSMonitoring instance is fine too. ... DATABASE is terminating abnormally." ...
    (microsoft.public.sqlserver.msde)
  • Re: help with looking up in a table - all varaibles
    ... I have the nominal pipe diameter, this is the size of the ... The pipe schedule is what kind of pipe you are ... I have been trying to ask the database to look ...
    (microsoft.public.access.formscoding)
  • Re: FIFO file objects
    ... Also there is no real guarantee between invocations of fileno that ... Select will also return with EINTR ... > pipe and just print the data to a file. ... the database would be constanly hammered). ...
    (comp.unix.programmer)