Excel Formula to Auto-populate Start & End Values in Datasheet

[Mr Engineer]

Hi there

Context of problem: I am trying to quantify lengths of fencing along a road into excel. I type the extents of the fencing into Table 1.

Note that Start & End represents the location on the road. So for example, a fencing which starts at location "90" and runs to location "95" is 5 meters long.

I have a data table, which represents the area in 20 meter blocks.

Problem: I am trying to auto populate the total lengths of fencing into Column G. In the picture, I have manually entered these in, but I need a formula which can automate this.

Note I need to account for the overlap in the fencing between locations "80" to "100".

Can anybody give me a hand?

Thanks
Mr. Engineer

 

[StephenCrump]

Like this perhaps:

<table1[end])*(f3>G3 (array entered): {=SUMPRODUCT((E3< Table1[End])*(F3>Table1[Start]),IF(F3>Table1[End],Table1[End],F3)-IF(E3>Table1[Start],E3,Table1[Start]))}

Table1 is A2:C11 (with headers)


Excel 2010

	A	B	C	D	E	F	G
Table 1	Data Table
Start	End	Length	Start	End	Length

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]87[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]95[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]102[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]104[/TD]
[TD="align: right"]106[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]20[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]114[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]12[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]119[/TD]
[TD="align: right"]132[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]120[/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]148[/TD]
[TD="align: right"]167[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"][/TD]
[TD="align: right"]120[/TD]
[TD="align: right"]140[/TD]
[TD="align: right"]12[/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #DCE6F1, align: right"]185[/TD]
[TD="bgcolor: #DCE6F1, align: right"]187[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]140[/TD]
[TD="align: right"]160[/TD]
[TD="align: right"]12[/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #DCE6F1, align: right"]200[/TD]
[TD="bgcolor: #DCE6F1, align: right"]331[/TD]
[TD="align: right"]131[/TD]
[TD="align: right"][/TD]
[TD="align: right"]160[/TD]
[TD="align: right"]180[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]180[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]209[/TD]
[TD="align: right"][/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]220[/TD]
[TD="align: right"]20[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]220[/TD]
[TD="align: right"]240[/TD]
[TD="align: right"]20[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]240[/TD]
[TD="align: right"]260[/TD]
[TD="align: right"]20[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]260[/TD]
[TD="align: right"]280[/TD]
[TD="align: right"]20[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]280[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]20[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]320[/TD]
[TD="align: right"]20[/TD]

[TD="align: center"]19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]320[/TD]
[TD="align: right"]340[/TD]
[TD="align: right"]11[/TD]

[TD="align: center"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]340[/TD]
[TD="align: right"]360[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]21[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]360[/TD]
[TD="align: right"]380[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]22[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]209[/TD]

</tbody>

Sheet1​

</table1[end])*(f3>