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,555
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,082,246
Messages
5,363,993
Members
400,772
Latest member
solbebe

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top