Hi grant,
I have a solution for you.
Your problem can be resolved by using the following format:
Worksheet Foul runs
A B C ===> Columns,
vertical the rows ;-)
length size Length & size
1,25 150 1.501,25 (formula: =value(B2&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 & function, I had
to make it values by the function value.
Worksheet summary:
A B C D
E F ==> 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 B2&C2 by cell properties. I did this als for B3&C3,
D2&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 b2&c2) is as followed)
=SOM.IF('Foul Runs'!$C$2:$C$11;">"&VALUE($A4&B$3);'Foul
Runs'!$A$2:$A$11)-SOM.IF('Foul Runs'!$C$2:$C$11;">"&VALUE($A4&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 > 1501 (formula VALUE($A4&B$3));than take the sum of all
value which are bigger - (minus) check if column C >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
Newbie81 via OfficeKB.com said:
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$100>1)+('Foul runs'!$E$6:$E$100<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 said:
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
I am a surveyor and am currently working out quantities of pipework etc.
[quoted text clipped - 14 lines]
Thankyou
Grant
