1,000 missing survey data points to be fixed by interpolation

K

kashifjillani

New Member
Joined
Jul 26, 2011
Messages
47
Hello experts,

Tried all methods without success

I have large data of around 10,000 drainage pipe segments and 10,000 junctions in ArcGIS layer

The problem is: due to some reason certain junctions could not be accessed for survey, thus 1,000+ entries are missing for pipes (up stream and d/s invert elevation) and junctions' surface elevation

Logically, interpolation should work here. (based on slope, u/s tag invert level or two junction u/s tag invert level or likewise)

Tried to do it manually in excel, but quite tiring and with less confidence (unprofessional too)

Any clue how to fix it?



e.g.
<table border="0" cellpadding="0" cellspacing="0" width="531"><tbody><tr style="height:12.75pt" height="17"><td style="height:12.75pt;width:48pt" height="17" width="64"> <table border="0" cellpadding="0" cellspacing="0" width="960"><colgroup><col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:2560;width:53pt" width="70"> <col style="mso-width-source:userset;mso-width-alt:3291; width:68pt" span="2" width="90"> <col style="mso-width-source:userset;mso-width-alt:2816;width:58pt" width="77"> <col style="mso-width-source:userset;mso-width-alt:2560; width:53pt" span="2" width="70"> <col style="width:48pt" span="2" width="64"> <col style="mso-width-source:userset;mso-width-alt:3620;width:74pt" width="99"> <col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:5046;width:104pt" width="138"> </colgroup><tbody><tr style="height:12.75pt" height="17"> <td style="height:12.75pt;width:48pt" height="17" width="64">ASSETID</td> <td class="xl24" style="width:53pt" width="70">LENGTH</td> <td class="xl24" style="width:68pt" width="90">US_INVERT</td> <td class="xl24" style="width:68pt" width="90">DS_INVERT</td> <td class="xl24" style="width:58pt" width="77">PIPE_DIA</td> <td class="xl24" style="width:53pt" width="70">US_TAG</td> <td class="xl24" style="width:53pt" width="70">DS_TAG</td> <td style="width:48pt" width="64">TYPEID</td> <td style="width:48pt" width="64">PIPEMATERI</td> <td style="width:74pt" width="99">US_PIT_NAM</td> <td style="width:48pt" width="64">TYPEDESCRI</td> <td style="width:104pt" width="138">ASSETDESCR</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">196934</td> <td align="right">11.13</td> <td align="right">37.46</td> <td align="right">37.34</td> <td align="right">300</td> <td align="right">7798</td> <td align="right">7797</td> <td align="right">1505</td> <td>RCP Class (2)</td> <td align="right">17</td> <td>RCP Class (2) 300 mm diameter</td> <td>Pipe - 7798 - 7797</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">196935</td> <td align="right">5.29</td> <td align="right">37.34</td> <td align="right">37.32</td> <td align="right">300</td> <td align="right">7797</td> <td align="right">7722</td> <td align="right">1505</td> <td>RCP Class (2)</td> <td align="right">16</td> <td>RCP Class (2) 300 mm diameter</td> <td>Pipe - 7797 - 7722</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">196936</td> <td align="right">12.95</td> <td align="right">40.25</td> <td align="right">40.2</td> <td align="right">300</td> <td align="right">7642</td> <td align="right">7641</td> <td align="right">1505</td> <td>RCP Class (2)</td> <td align="right">16</td> <td>RCP Class (2) 300 mm diameter</td> <td>Pipe - 7642 - 7641</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">196937</td> <td align="right">25.93</td> <td align="right">39.61</td> <td align="right">0</td> <td align="right">375</td> <td align="right">7821</td> <td align="right">7846</td> <td align="right">1506</td> <td>RCP Class (2)</td> <td>D</td> <td>RCP Class (2) 375 mm diameter</td> <td>Pipe - 7821 - 7846</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">196938</td> <td align="right">10.45</td> <td align="right">40.61</td> <td align="right">40.46</td> <td align="right">375</td> <td align="right">7783</td> <td align="right">7771</td> <td align="right">1506</td> <td>RCP Class (2)</td> <td align="right">22</td> <td>RCP Class (2) 375 mm diameter</td> <td>Pipe - 7783 - 7771</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">196939</td> <td align="right">76.51</td> <td align="right">0</td> <td align="right">40.42</td> <td align="right">375</td> <td align="right">7772</td> <td align="right">7771</td> <td align="right">1506</td> <td>RCP Class (2)</td> <td align="right">2</td> <td>RCP Class (2) 375 mm diameter</td> <td>Pipe - 7772 - 7771</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">196940</td> <td align="right">8.26</td> <td align="right">39.88</td> <td align="right">39.84</td> <td align="right">300</td> <td align="right">7735</td> <td align="right">7734</td> <td align="right">1505</td> <td>RCP Class (2)</td> <td align="right">11</td> <td>RCP Class (2) 300 mm diameter</td> <td>Pipe - 7735 - 7734</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">196941</td> <td align="right">10.11</td> <td align="right">49.48</td> <td align="right">49.42</td> <td align="right">300</td> <td align="right">8023</td> <td align="right">8022</td> <td align="right">1505</td> <td>RCP Class (2)</td> <td>SE</td> <td>RCP Class (2) 300 mm diameter</td> <td>Pipe - 8023 - 8022</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">196942</td> <td align="right">7.41</td> <td align="right">41.56</td> <td align="right">41.47</td> <td align="right">300</td> <td align="right">7710</td> <td align="right">7709</td> <td align="right">1505</td> <td>RCP Class (2)</td> <td align="right">16</td> <td>RCP Class (2) 300 mm diameter</td> <td>Pipe - 7710 - 7709</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">196943</td> <td align="right">9.54</td> <td align="right">0</td> <td align="right">40.62</td> <td align="right">300</td> <td align="right">7659</td> <td align="right">7658</td> <td align="right">1505</td> <td>RCP Class (2)</td> <td align="right">40</td> <td>RCP Class (2) 300 mm diameter</td> <td>Pipe - 7659 - 7658</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">196944</td> <td align="right">28.87</td> <td align="right">46.64</td> <td align="right">0</td> <td align="right">450</td> <td align="right">70108</td> <td align="right">8004</td> <td align="right">1507</td> <td>RCP Class (2)</td> <td align="right">28</td> <td>RCP Class (2) 450 mm diameter</td> <td>Pipe - 70108 - 8004</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">196945</td> <td align="right">37.83</td> <td align="right">40.96</td> <td align="right">40.66</td> <td align="right">375</td> <td align="right">7912</td> <td align="right">7911</td> <td align="right">1506</td> <td>RCP Class (2)</td> <td align="right">11</td> <td>RCP Class (2) 375 mm diameter</td> <td>Pipe - 7912 - 7911</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">196946</td> <td align="right">21.53</td> <td align="right">40.28</td> <td align="right">0</td> <td align="right">375</td> <td align="right">7645</td> <td align="right">7644</td> <td align="right">1506</td> <td>RCP Class (2)</td> <td align="right">23</td> <td>RCP Class (2) 375 mm diameter</td> <td>Pipe - 7645 - 7644</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">196947</td> <td align="right">48.5</td> <td align="right">38.99</td> <td align="right">38.3</td> <td align="right">450</td> <td align="right">7907</td> <td align="right">7906</td> <td align="right">1507</td> <td>RCP Class (2)</td> <td align="right">6</td> <td>RCP Class (2) 450 mm diameter</td> <td>Pipe - 7907 - 7906</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">196948</td> <td align="right">80.65</td> <td align="right">0</td> <td align="right">39.88</td> <td align="right">375</td> <td align="right">7823</td> <td align="right">7822</td> <td align="right">1506</td> <td>RCP Class (2)</td> <td>F</td> <td>RCP Class (2) 375 mm diameter</td> <td>Pipe - 7823 - 7822</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">196949</td> <td align="right">37.62</td> <td align="right">39.19</td> <td align="right">0</td> <td align="right">450</td> <td align="right">7633</td> <td align="right">7632</td> <td align="right">1507</td> <td>RCP Class (2)</td> <td align="right">8</td> <td>RCP Class (2) 450 mm diameter</td> <td>Pipe - 7633 - 7632</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">196950</td> <td align="right">18.29</td> <td align="right">0</td> <td align="right">40.28</td> <td align="right">375</td> <td align="right">7881</td> <td align="right">7880</td> <td align="right">1506</td> <td>RCP Class (2)</td> <td align="right">5</td> <td>RCP Class (2) 375 mm diameter</td> <td>Pipe - 7881 - 7880</td> </tr> </tbody></table></td><td class="xl22" style="width:53pt" width="70">
</td><td class="xl22" style="width:68pt" width="90">
</td><td class="xl22" style="width:68pt" width="90">
</td><td class="xl22" style="width:58pt" width="77">
</td><td class="xl22" style="width:53pt" width="70">
</td><td class="xl22" style="width:53pt" width="70">
</td></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17" 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></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17" 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></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17" 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></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17" 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></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17" 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></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17" 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></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17" 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></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17" 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></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17" 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></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17" 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></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17" 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></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17" 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></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17" 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></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17" 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></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17" 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></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17" 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></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17" 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></tr></tbody></table>

Can send the table if required

Thanks in advance,

regards,

Kashif
:confused:
 
Sort by date Sort by votes
Is it to be assumed that Asset 196934 relates to 196935 via junction?

Could be that your interpolations aren't working because the inputs are bad
i.e.
Asset 196944 has a USTAG (I assume to be an elevation value) = 70108, well out of the norm and inconsistent with its relative segments (196943 , 196945).
Asset 196937 has a DSTAG (again, assumed to be an elevation) that is higher value than the USTAG.
 
Upvote 0
Hi Kashi,

I don't understand the technical details of what these numbers represent but it looks to me as though you are trying to calculate a new table where the zeros are replaced by information from the line above or below them.

Assuming that this is accurate, you can construct a table that refers to the old table using the following formula adjusted so the cells make sense for the info you are trying to get at.

=IF(C2=0,D1,C2) and copy down .

This formula is for the US INVERT and infilling the data from the DS invert from the above line. (Using your columns from above and pasting into a spreadsheet).(this formula checks whether the cell in column C is 0, if it is it replaces that value with the value one cell up and one over).

Try it out and see whether this is what you are looking for.

Cheers, :)
 
Upvote 0
<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> <table class="MsoNormalTable" style="width:348.6pt;margin-left:5.05pt;border-collapse:collapse;mso-padding-alt: 0cm 5.4pt 0cm 5.4pt" border="0" cellpadding="0" cellspacing="0" width="465"> <tbody><tr style="mso-yfti-irow:0;mso-yfti-firstrow:yes;height:12.75pt"> <td style="width:53.6pt;padding:0cm 5.4pt 0cm 5.4pt; height:12.75pt" nowrap="nowrap" valign="bottom" width="71"> ASSETID
</td> <td style="width:53.0pt;padding:0cm 5.4pt 0cm 5.4pt; height:12.75pt" nowrap="nowrap" valign="bottom" width="71"> LENGTH
</td> <td style="width:68.0pt;padding:0cm 5.4pt 0cm 5.4pt; height:12.75pt" nowrap="nowrap" valign="bottom" width="91"> US_INVERT
</td> <td style="width:68.0pt;padding:0cm 5.4pt 0cm 5.4pt; height:12.75pt" nowrap="nowrap" valign="bottom" width="91"> DS_INVERT
</td> <td style="width:53.0pt;padding:0cm 5.4pt 0cm 5.4pt; height:12.75pt" nowrap="nowrap" valign="bottom" width="71"> US_TAG
</td> <td style="width:53.0pt;padding:0cm 5.4pt 0cm 5.4pt; height:12.75pt" nowrap="nowrap" valign="bottom" width="71"> DS_TAG
</td> </tr> <tr style="mso-yfti-irow:1;height:12.75pt"> <td style="width:53.6pt;padding:0cm 5.4pt 0cm 5.4pt; height:12.75pt" nowrap="nowrap" valign="bottom" width="71">
*196939​
</td> <td style="width:53.0pt;padding:0cm 5.4pt 0cm 5.4pt; height:12.75pt" nowrap="nowrap" valign="bottom" width="71">
76.51​
</td> <td style="width:68.0pt;padding:0cm 5.4pt 0cm 5.4pt; height:12.75pt" nowrap="nowrap" valign="bottom" width="91">
0​
</td> <td style="width:68.0pt;padding:0cm 5.4pt 0cm 5.4pt; height:12.75pt" nowrap="nowrap" valign="bottom" width="91">
40.42​
</td> <td style="width:53.0pt;padding:0cm 5.4pt 0cm 5.4pt; height:12.75pt" nowrap="nowrap" valign="bottom" width="71">
7772​
</td> <td style="width:53.0pt;padding:0cm 5.4pt 0cm 5.4pt; height:12.75pt" nowrap="nowrap" valign="bottom" width="71">
7771​
</td> </tr> <tr style="mso-yfti-irow:2;height:12.75pt"> <td style="width:53.6pt;padding:0cm 5.4pt 0cm 5.4pt; height:12.75pt" nowrap="nowrap" valign="bottom" width="71">
*197048​
</td> <td style="width:53.0pt;padding:0cm 5.4pt 0cm 5.4pt; height:12.75pt" nowrap="nowrap" valign="bottom" width="71">
34.77​
</td> <td style="width:68.0pt;padding:0cm 5.4pt 0cm 5.4pt; height:12.75pt" nowrap="nowrap" valign="bottom" width="91">
42.98​
</td> <td style="width:68.0pt;padding:0cm 5.4pt 0cm 5.4pt; height:12.75pt" nowrap="nowrap" valign="bottom" width="91">
0​
</td> <td style="width:53.0pt;padding:0cm 5.4pt 0cm 5.4pt; height:12.75pt" nowrap="nowrap" valign="bottom" width="71">
7773​
</td> <td style="width:53.0pt;padding:0cm 5.4pt 0cm 5.4pt; height:12.75pt" nowrap="nowrap" valign="bottom" width="71">
7772​
</td> </tr> <tr style="mso-yfti-irow:3;height:12.75pt"> <td style="width:53.6pt;padding:0cm 5.4pt 0cm 5.4pt; height:12.75pt" nowrap="nowrap" valign="bottom" width="71">
196938​
</td> <td style="width:53.0pt;padding:0cm 5.4pt 0cm 5.4pt; height:12.75pt" nowrap="nowrap" valign="bottom" width="71">
10.45​
</td> <td style="width:68.0pt;padding:0cm 5.4pt 0cm 5.4pt; height:12.75pt" nowrap="nowrap" valign="bottom" width="91">
40.61​
</td> <td style="width:68.0pt;padding:0cm 5.4pt 0cm 5.4pt; height:12.75pt" nowrap="nowrap" valign="bottom" width="91">
40.46​
</td> <td style="width:53.0pt;padding:0cm 5.4pt 0cm 5.4pt; height:12.75pt" nowrap="nowrap" valign="bottom" width="71">
7783​
</td> <td style="width:53.0pt;padding:0cm 5.4pt 0cm 5.4pt; height:12.75pt" nowrap="nowrap" valign="bottom" width="71">
7771​
</td> </tr> <tr style="mso-yfti-irow:4;height:12.75pt"> <td style="width:53.6pt;padding:0cm 5.4pt 0cm 5.4pt; height:12.75pt" nowrap="nowrap" valign="bottom" width="71">
196939​
</td> <td style="width:53.0pt;padding:0cm 5.4pt 0cm 5.4pt; height:12.75pt" nowrap="nowrap" valign="bottom" width="71">
76.51​
</td> <td style="width:68.0pt;padding:0cm 5.4pt 0cm 5.4pt; height:12.75pt" nowrap="nowrap" valign="bottom" width="91">
0​
</td> <td style="width:68.0pt;padding:0cm 5.4pt 0cm 5.4pt; height:12.75pt" nowrap="nowrap" valign="bottom" width="91">
40.42​
</td> <td style="width:53.0pt;padding:0cm 5.4pt 0cm 5.4pt; height:12.75pt" nowrap="nowrap" valign="bottom" width="71">
7772​
</td> <td style="width:53.0pt;padding:0cm 5.4pt 0cm 5.4pt; height:12.75pt" nowrap="nowrap" valign="bottom" width="71">
7771​
</td> </tr> <tr style="mso-yfti-irow:5;mso-yfti-lastrow:yes;height:12.75pt"> <td style="width:53.6pt;padding:0cm 5.4pt 0cm 5.4pt; height:12.75pt" nowrap="nowrap" valign="bottom" width="71">
196940​
</td> <td style="width:53.0pt;padding:0cm 5.4pt 0cm 5.4pt; height:12.75pt" nowrap="nowrap" valign="bottom" width="71">
8.26​
</td> <td style="width:68.0pt;padding:0cm 5.4pt 0cm 5.4pt; height:12.75pt" nowrap="nowrap" valign="bottom" width="91">
39.88​
</td> <td style="width:68.0pt;padding:0cm 5.4pt 0cm 5.4pt; height:12.75pt" nowrap="nowrap" valign="bottom" width="91">
39.84​
</td> <td style="width:53.0pt;padding:0cm 5.4pt 0cm 5.4pt; height:12.75pt" nowrap="nowrap" valign="bottom" width="71">
7735​
</td> <td style="width:53.0pt;padding:0cm 5.4pt 0cm 5.4pt; height:12.75pt" nowrap="nowrap" valign="bottom" width="71">
7734​
</td> </tr> </tbody></table> Drainage pipes data

Hello experts,

Thanks for having looked at my query; I know it is difficult to understand as it is a bit complicated and challenging too. I worked to make it simple in a bit longer way.

Above, I have put * to just two rows which are involved in calculations out of the whole data from 10,062 rows (Row 3 to Row 10,064)


  • To calculate US_INVER (US_TAG) elevation we need to find an asset where 7772 is under column heading DS_TAG
  • That is ASSETID 197048
  • After finding it (through some function/formula), we have got on US_INV and one DS_INV to point in question
  • Now we want to know elevation of that point in between two known elevations: which are +42.98 and +40.42 and total distance b/w these two points is length of Asset ID 196939(76.51m) and 197048(34.77m)=111.28m
  • The point is located 34.77m downstream of +42.98 and 76.51m upstream from of +40.42
  • We have just created a right angle triangle where Perpendicular is 42.98-40.42=2.56m and Base is 34.77+76.51=111.28
  • To calculate the angle of drainage pipe tan Ѳ = 2.56/111.28, Ѳ=1.317
  • After knowing the angle of pipe we can easily know the elevation of any point along the hypotenuse
  • So, US_INVERT (US_TAG) = 42.98 - tan (1.317) * 34.77 = +42.18

Alternatively

  • After step 5. we have another choice; by dividing value of perpendicular with base and multiply with distance from upstream and subtracting from upstream elevation i.e. 42.98 - (2.56/111.28)*34.77 = +42.18 again

This is the value we require at US_INVER (US_TAG) of Asset ID 196939

And

DS_INVERT (DS_TAG) of ASSETID 197048

Also, something similar would be require to fill in the missing data under column DS_INVERT

There are around 1000 missing values, if we know the formula we Ctrl+H (find and replace “0”) to save time and effort

Also, sometime one asset upstream we wont get any value (“0”again), in that case we can go two steps up (but, if it is not possible we can leave it for manual calculation)

The main challenge here, in my understanding is finding a function which could find US_TAG value of row (in question) in a DS_TAG column and start calculating by taking values from that row and row in question.

Note: the DS_TAG value could be found in multiple rows, in that case priority should be given to a row with least value in LENGTH column. (again if it is difficult, any of row could be taken)

It seems we are pushing MSEXCEL to its limits from where C++ programming starts,

The complete file in excel format is uploaded at

http://www.mediafire.com/?uniy9cic1gvxytp

http://www.mediafire.com/?uniy9cic1gvxytp

Thanks again for your time reading and efforts to solve it

Regards to Shawnhet and Tweedle and all others out to help others,

Kashif
 
Upvote 0
shawnhet said:
Hi Kashi,

I don't understand the technical details of what these numbers represent but it looks to me as though you are trying to calculate a new table where the zeros are replaced by information from the line above or below them.

Assuming that this is accurate, you can construct a table that refers to the old table using the following formula adjusted so the cells make sense for the info you are trying to get at.

=IF(C2=0,D1,C2) and copy down .

This formula is for the US INVERT and infilling the data from the DS invert from the above line. (Using your columns from above and pasting into a spreadsheet).(this formula checks whether the cell in column C is 0, if it is it replaces that value with the value one cell up and one over).

Try it out and see whether this is what you are looking for.

Cheers, :)
Click to expand...
Hi Shawnhet,

Plz have a look at the question explained in detail
 
Upvote 0
tweedle said:
Is it to be assumed that Asset 196934 relates to 196935 via junction?

Could be that your interpolations aren't working because the inputs are bad
i.e.
Asset 196944 has a USTAG (I assume to be an elevation value) = 70108, well out of the norm and inconsistent with its relative segments (196943 , 196945).
Asset 196937 has a DSTAG (again, assumed to be an elevation) that is higher value than the USTAG.
Click to expand...
Hi Tweedle,

Plz have a look at the question detailed further
 
Upvote 0
Hi Kash,

I still don't really follow what you are asking for exactly, but I am still assuming that you are trying to fill in the amounts that are 0 to start with. Try these expressions out to see if they give meaningful answers.

=IF(ISERROR(INDEX($A$2:$F$6,MATCH(E2,$F$2:$F$6,0),3)),C2,(INDEX($A$2:$F$6,MATCH(E2,$F$2:$F$6,0),3)))

=IF(ISERROR(INDEX($A$2:$F$6,MATCH(F2,$E$2:$E$6,0),4)),D2,(INDEX($A$2:$F$6,MATCH(F2,$E$2:$E$6,0),3)))

Cheers, :)
 
Upvote 0
A couple of other expressions that may get you where you need to be:

Given that the above two lines lookup the relevant info from the same tag lines I believe that this should look up an additional length.

=IF(ISERROR(INDEX($A$2:$F$6,MATCH(E2,$F$2:$F$6,0),2)),IF(ISERROR(INDEX($A$2:$F$6,MATCH(F2,$E$2:$E$6,0),2)),B2,INDEX($A$2:$F$6,MATCH(F2,$E$2:$E$6,0),2)),INDEX($A$2:$F$6,MATCH(E2,$F$2:$F$6,0),2))

This should allow you to do your last calculation.

=I2-J2*(I2-H2)/(B2+G2)

I'm not sure if this works though - I am still having a hard time figuring out the logic behind your calculations.

Cheers, :)
 
Upvote 0
there are a number of problems you need to consider in this scenario which you are going to have to tackle to get good results...

1 how acurate do you require the results to be? ie should you be using linear , exponential or quadratic etc

2 what distance lies between the known points

3 how many points do you want to interpolate between known points

4 do you need to extrapolate data also

5 does the data even follow a path which can be modelled?

6 will the end user be aware that the data is interpolated?

you need a mathematical approach to this problem to give a reliable (within acceptable error) solution

until you can answer thse issues, you cant invent a spreadsheet to give a result.
 
Upvote 0
Its appreciated that you came back to fully explain the data, however I just don't see enough consistency in the relationships from one asset to the next to accurately produce results.
The first illustrated step is to find a corresponding record based on finding the subject USTAG in the DSTAG list: There are 900+ records where USINV = 0 and there is no such match.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
926
smozgur
smozgur
D
  • Question Question
Problems Encountered When Generating VBA Excel Charts with 15-Minute Interval Data.
Replies
0
Views
405
dilshod_k
D
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C
B
  • Question Question
Webscrapping Loop to get table data
Replies
1
Views
722
John_w
J
M
  • Question Question
Help with FilterXML formula
Replies
0
Views
2K
montex01
M

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top