software

I am a surveyor and am currently working out quantities of pipework etc.

I have an electronic take off sheet that works out the various lengths and
depths and sizes of pipe. I am trying to create a summary page for this.
There are several different pipe sizes from 150, 200, 225, 250 etc.

But i need to summarise these to various lengths and pipe sizes into depth
ranges. So say for every length of 150 pipe at 1m to 1.5m in depth i need it
to sum up the total length on a separate sheet within the same workbook.

I have played about wth array formulas etc but with little success

Can anyone help?

Any help would be greatly appreciated

Thankyou
Grant

--
Message posted via www.officekb.com

Grant,

If i understand your question right, is your problem that you have several
lenght's of pipe's. And you want to know what the total size is of the used
pipes per pipe size.

I think that you can resolve this by using the formula (a) sumif() or (b)
countif() and multiply this with the size per pipe.

Example (a)
=Sumif(Range where you see the used size;the size u want to summarize;range
which can summarize)

Example (b)
=Countif(Range where you see the used size;the size u want to
summarize;range which can summarize)*1,50 or b1, etc

Good luck and if this doesn't solve your problem I'll read it here.

Greetings

Stefan

quot;Newbie81 via OfficeKB.comquot; wrote:

gt; I am a surveyor and am currently working out quantities of pipework etc.
gt;
gt; I have an electronic take off sheet that works out the various lengths and
gt; depths and sizes of pipe. I am trying to create a summary page for this.
gt; There are several different pipe sizes from 150, 200, 225, 250 etc.
gt;
gt; But i need to summarise these to various lengths and pipe sizes into depth
gt; ranges. So say for every length of 150 pipe at 1m to 1.5m in depth i need it
gt; to sum up the total length on a separate sheet within the same workbook.
gt;
gt; I have played about wth array formulas etc but with little success
gt;
gt; Can anyone help?
gt;
gt; Any help would be greatly appreciated
gt;
gt; Thankyou
gt; Grant
gt;
gt; --
gt; Message posted via www.officekb.com
gt;

The trouble is that there is a third argument to the formula i need to add in
and that is the depth range.

The average depth of each pipe is calculated and needs to be summarised not
only in size of pipe but also by the depth range it falls into. I need it to
look something like this:
Depth Ranges
Size of pipe 1.0 - 1.5 1.5 - 2.0 2.0 - 2.5
150 Length? Length? Length?
200 Length? Length? Length?

I have tried various formulas the one below is the best so far. Trouble is it
picks up all of the lengths at every depth range
{=SUM(IF(('Foul runs'!$E$6:$E$100gt;1) ('Foul runs'!$E$6:$E$100lt;1.5),'Foul
runs'!U$6:U$100))}

On the foul runs page i have got it to split out the various sizes into
different columns using an 'IF' formula ( Would be better if i could
incorperate this in the summary formula). So basically i need to add an
argument that says If the pipe is deeper then 1 but not deeper then 1.5 then
sum the length.

Can you help?

Thanks Grant
Stefan wrote:
gt;Grant,
gt;
gt;If i understand your question right, is your problem that you have several
gt;lenght's of pipe's. And you want to know what the total size is of the used
gt;pipes per pipe size.
gt;
gt;I think that you can resolve this by using the formula (a) sumif() or (b)
gt;countif() and multiply this with the size per pipe.
gt;
gt;Example (a)
gt;=Sumif(Range where you see the used size;the size u want to summarize;range
gt;which can summarize)
gt;
gt;Example (b)
gt;=Countif(Range where you see the used size;the size u want to
gt;summarize;range which can summarize)*1,50 or b1, etc
gt;
gt;Good luck and if this doesn't solve your problem I'll read it here.
gt;
gt;Greetings
gt;
gt;Stefan
gt;
gt;gt; I am a surveyor and am currently working out quantities of pipework etc.
gt;gt;
gt;[quoted text clipped - 14 lines]
gt;gt; Thankyou
gt;gt; Grant

--
Message posted via OfficeKB.com
www.officekb.com/Uwe/Forums.a...excel/200601/1

Hi grant,

I have a solution for you.
Your problem can be resolved by using the following format:

Worksheet Foul runs
A B C ===gt; Columns,
vertical the rows ;-)
lengthsizeLength amp; size
1,25 150 1.501,25 (formula: =value(B2amp;A2)
1,25 150 1.501,25
1,30 150 1.501,30
1,65 150 1.501,65
1,70 150 1.501,70
1,25 200 2.001,25
1,40 200 2.001,40
1,65 200 2.001,65
1,65 200 2.001,65
1,80 200 2.001,80
14,90

Explanation formula
I need to combine the numbers for making the formula.
Because the format of the numbers is text when you use the amp; function, I had
to make it values by the function value.

Worksheet summary:

A B C D
E F ==gt; Columns
1,00 1,50 1,50 2,00 Total
150 3,80 3,35 7,15
200 2,65 5,10 7,75
14,90

I have merge the cells B2amp;C2 by cell properties. I did this als for B3amp;C3,
D2amp;E2, etc.
I did this, because in mine formula I am going to use the headers of this
summary (rows and colums).
The Formula for cel B2 (merged cell b2amp;c2) is as followed)

=SOM.IF('Foul Runs'!$C$2:$C$11;quot;gt;quot;amp;VALUE($A4amp;B$3);'Foul
Runs'!$A$2:$A$11)-SOM.IF('Foul Runs'!$C$2:$C$11;quot;gt;quot;amp;VALUE($A4amp;C$3);'Foul
Runs'!$A$2:$A$11)

This formula can you copy into the summary thanks to the $-sign.
But I'll explain the formula also.

In this formula I say:
Check if column C gt; 1501 (formula VALUE($A4amp;B$3));than take the sum of all
value which are bigger - (minus) check if column C gt;1501,50; than take the
sum of all value which are bigger.

If you need more explanation I'll read it on the site.
Good luck

Stefan

quot;Newbie81 via OfficeKB.comquot; wrote:

gt; The trouble is that there is a third argument to the formula i need to add in
gt; and that is the depth range.
gt;
gt; The average depth of each pipe is calculated and needs to be summarised not
gt; only in size of pipe but also by the depth range it falls into. I need it to
gt; look something like this:
gt; Depth Ranges
gt; Size of pipe 1.0 - 1.5 1.5 - 2.0 2.0 - 2.5
gt; 150 Length? Length? Length?
gt; 200 Length? Length? Length?
gt;
gt; I have tried various formulas the one below is the best so far. Trouble is it
gt; picks up all of the lengths at every depth range
gt; {=SUM(IF(('Foul runs'!$E$6:$E$100gt;1) ('Foul runs'!$E$6:$E$100lt;1.5),'Foul
gt; runs'!U$6:U$100))}
gt;
gt; On the foul runs page i have got it to split out the various sizes into
gt; different columns using an 'IF' formula ( Would be better if i could
gt; incorperate this in the summary formula). So basically i need to add an
gt; argument that says If the pipe is deeper then 1 but not deeper then 1.5 then
gt; sum the length.
gt;
gt; Can you help?
gt;
gt; Thanks Grant
gt; Stefan wrote:
gt; gt;Grant,
gt; gt;
gt; gt;If i understand your question right, is your problem that you have several
gt; gt;lenght's of pipe's. And you want to know what the total size is of the used
gt; gt;pipes per pipe size.
gt; gt;
gt; gt;I think that you can resolve this by using the formula (a) sumif() or (b)
gt; gt;countif() and multiply this with the size per pipe.
gt; gt;
gt; gt;Example (a)
gt; gt;=Sumif(Range where you see the used size;the size u want to summarize;range
gt; gt;which can summarize)
gt; gt;
gt; gt;Example (b)
gt; gt;=Countif(Range where you see the used size;the size u want to
gt; gt;summarize;range which can summarize)*1,50 or b1, etc
gt; gt;
gt; gt;Good luck and if this doesn't solve your problem I'll read it here.
gt; gt;
gt; gt;Greetings
gt; gt;
gt; gt;Stefan
gt; gt;
gt; gt;gt; I am a surveyor and am currently working out quantities of pipework etc.
gt; gt;gt;
gt; gt;[quoted text clipped - 14 lines]
gt; gt;gt; Thankyou
gt; gt;gt; Grant
gt;
gt; --
gt; Message posted via OfficeKB.com
gt; www.officekb.com/Uwe/Forums.a...excel/200601/1
gt;

som = sum

quot;Stefanquot; wrote:

gt; Hi grant,
gt;
gt; I have a solution for you.
gt; Your problem can be resolved by using the following format:
gt;
gt; Worksheet Foul runs
gt; A B C ===gt; Columns,
gt; vertical the rows ;-)
gt; lengthsizeLength amp; size
gt; 1,25 150 1.501,25 (formula: =value(B2amp;A2)
gt; 1,25 150 1.501,25
gt; 1,30 150 1.501,30
gt; 1,65 150 1.501,65
gt; 1,70 150 1.501,70
gt; 1,25 200 2.001,25
gt; 1,40 200 2.001,40
gt; 1,65 200 2.001,65
gt; 1,65 200 2.001,65
gt; 1,80 200 2.001,80
gt; 14,90
gt;
gt; Explanation formula
gt; I need to combine the numbers for making the formula.
gt; Because the format of the numbers is text when you use the amp; function, I had
gt; to make it values by the function value.
gt;
gt; Worksheet summary:
gt;
gt; A B C D
gt; E F ==gt; Columns
gt; 1,00 1,50 1,50 2,00 Total
gt; 150 3,80 3,35 7,15
gt; 200 2,65 5,10 7,75
gt; 14,90
gt;
gt; I have merge the cells B2amp;C2 by cell properties. I did this als for B3amp;C3,
gt; D2amp;E2, etc.
gt; I did this, because in mine formula I am going to use the headers of this
gt; summary (rows and colums).
gt; The Formula for cel B2 (merged cell b2amp;c2) is as followed)
gt;
gt; =SOM.IF('Foul Runs'!$C$2:$C$11;quot;gt;quot;amp;VALUE($A4amp;B$3);'Foul
gt; Runs'!$A$2:$A$11)-SOM.IF('Foul Runs'!$C$2:$C$11;quot;gt;quot;amp;VALUE($A4amp;C$3);'Foul
gt; Runs'!$A$2:$A$11)
gt;
gt; This formula can you copy into the summary thanks to the $-sign.
gt; But I'll explain the formula also.
gt;
gt; In this formula I say:
gt; Check if column C gt; 1501 (formula VALUE($A4amp;B$3));than take the sum of all
gt; value which are bigger - (minus) check if column C gt;1501,50; than take the
gt; sum of all value which are bigger.
gt;
gt; If you need more explanation I'll read it on the site.
gt; Good luck
gt;
gt; Stefan
gt;
gt; quot;Newbie81 via OfficeKB.comquot; wrote:
gt;
gt; gt; The trouble is that there is a third argument to the formula i need to add in
gt; gt; and that is the depth range.
gt; gt;
gt; gt; The average depth of each pipe is calculated and needs to be summarised not
gt; gt; only in size of pipe but also by the depth range it falls into. I need it to
gt; gt; look something like this:
gt; gt; Depth Ranges
gt; gt; Size of pipe 1.0 - 1.5 1.5 - 2.0 2.0 - 2.5
gt; gt; 150 Length? Length? Length?
gt; gt; 200 Length? Length? Length?
gt; gt;
gt; gt; I have tried various formulas the one below is the best so far. Trouble is it
gt; gt; picks up all of the lengths at every depth range
gt; gt; {=SUM(IF(('Foul runs'!$E$6:$E$100gt;1) ('Foul runs'!$E$6:$E$100lt;1.5),'Foul
gt; gt; runs'!U$6:U$100))}
gt; gt;
gt; gt; On the foul runs page i have got it to split out the various sizes into
gt; gt; different columns using an 'IF' formula ( Would be better if i could
gt; gt; incorperate this in the summary formula). So basically i need to add an
gt; gt; argument that says If the pipe is deeper then 1 but not deeper then 1.5 then
gt; gt; sum the length.
gt; gt;
gt; gt; Can you help?
gt; gt;
gt; gt; Thanks Grant
gt; gt; Stefan wrote:
gt; gt; gt;Grant,
gt; gt; gt;
gt; gt; gt;If i understand your question right, is your problem that you have several
gt; gt; gt;lenght's of pipe's. And you want to know what the total size is of the used
gt; gt; gt;pipes per pipe size.
gt; gt; gt;
gt; gt; gt;I think that you can resolve this by using the formula (a) sumif() or (b)
gt; gt; gt;countif() and multiply this with the size per pipe.
gt; gt; gt;
gt; gt; gt;Example (a)
gt; gt; gt;=Sumif(Range where you see the used size;the size u want to summarize;range
gt; gt; gt;which can summarize)
gt; gt; gt;
gt; gt; gt;Example (b)
gt; gt; gt;=Countif(Range where you see the used size;the size u want to
gt; gt; gt;summarize;range which can summarize)*1,50 or b1, etc
gt; gt; gt;
gt; gt; gt;Good luck and if this doesn't solve your problem I'll read it here.
gt; gt; gt;
gt; gt; gt;Greetings
gt; gt; gt;
gt; gt; gt;Stefan
gt; gt; gt;
gt; gt; gt;gt; I am a surveyor and am currently working out quantities of pipework etc.
gt; gt; gt;gt;
gt; gt; gt;[quoted text clipped - 14 lines]
gt; gt; gt;gt; Thankyou
gt; gt; gt;gt; Grant
gt; gt;
gt; gt; --
gt; gt; Message posted via OfficeKB.com
gt; gt; www.officekb.com/Uwe/Forums.a...excel/200601/1
gt; gt;