Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default Criteria for datbase function in one row?

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?
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,441
Default Criteria for datbase function in one row?

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?



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default Criteria for datbase function in one row?

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?




  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,441
Default Criteria for datbase function in one row?

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?






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
self-sizing adv.filter criteria range BorisS Excel Discussion (Misc queries) 3 September 24th 06 01:30 PM
criteria function using max scott Excel Worksheet Functions 5 September 19th 06 02:46 AM
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) [email protected] Excel Worksheet Functions 0 September 5th 06 03:44 PM
include criteria to 'rank based array function' TUNGANA KURMA RAJU Excel Discussion (Misc queries) 2 September 2nd 06 01:15 PM
Custom functions calculating time arguments Help Desperate Bill_De Excel Worksheet Functions 12 April 25th 06 02:22 AM


All times are GMT +1. The time now is 10:57 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"