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

Mr Engineer

New Member
Joined
Jan 7, 2015
Messages
1
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

Well-known Member
Joined
Sep 18, 2013
Messages
3,567
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
ABCDEFG
1Table 1Data Table
2StartEndLengthStartEndLength
35587320200
49095520400
5101102140605
61041062608020
711011448010012
8119132131001208
91481671912014012
10185187214016012
112003311311601807
121802002
1320920022020
1422024020
1524026020
1626028020
1728030020
1830032020
1932034011
203403600
213603800
22
23209

<tbody>
</tbody>
Sheet1


</table1[end])*(f3>
 

Forum statistics

Threads
1,085,863
Messages
5,386,395
Members
401,997
Latest member
cizwiz

Some videos you may like

This Week's Hot Topics

Top