2012/2/14 PrIyAnKa <p.dhamsa...@gmail.com> > Dear Yogananda > > Instead of sharing such details kindly send a input or output sheet > > As your details are too long to read and understand and then provide u any > answer > > SO kindly share an excel with group > > On Tue, Feb 14, 2012 at 6:40 AM, yogananda muthaiah < > yogananda.mutha...@gmail.com> wrote: > >> >> Hello experts, >> >> 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) >> >> ASSETID LENGTH US_INVERT DS_INVERT US_TAG DS_TAG >> *196939 76.51 0 40.42 7772 7771 >> >> *197048 34.77 42.98 0 7773 7772 >> >> 196938 10.45 40.61 40.46 7783 7771 >> >> 196939 76.51 0 40.42 7772 7771 >> >> 196940 8.26 39.88 39.84 7735 7734 >> >> Drainage pipes data >> >> >> 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) >> >> 1. To calculate US_INVER (US_TAG) elevation we need to find an asset >> where 7772 is under column heading DS_TAG >> 2. That is ASSETID 197048 >> 3. After finding it (through some function/formula), we have got on >> US_INV and one DS_INV >> 4. Now we want to know elevation of a 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 >> 5. The point is located 34.77m downstream of +42.98 and 76.51m upstream >> from of +40.42 >> 6. 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 >> 7. To calculate the angle of drainage pipe tan Ѳ = 2.56/111.28, Ѳ=1.317 >> 8. After knowing the angle of pipe we can easily know the elevation of >> any point along the hypotenuse >> 9. So, US_INVERT (US_TAG) = 42.98 - tan (1.317) * 34.77 = +42.18 >> >> Alternatively >> 1. 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 for your time reading and efforts to solve it >> >> >> Regards >> Yogananda Muthaiah >> Ph : 973 123 7267 >> >> -- >> FORUM RULES (986+ members already BANNED for violation) >> >> 1) Use concise, accurate thread titles. Poor thread titles, like Please >> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice >> will not get quick attention or may not be answered. >> >> 2) Don't post a question in the thread of another member. >> >> 3) Don't post questions regarding breaking or bypassing any security >> measure. >> >> 4) Acknowledge the responses you receive, good or bad. >> >> 5) Cross-promotion of, or links to, forums competitive to this forum in >> signatures are prohibited. >> >> NOTE : Don't ever post personal or confidential data in a workbook. Forum >> owners and members are not responsible for any loss. >> >> >> ------------------------------------------------------------------------------------------------------ >> To post to this group, send email to excel-macros@googlegroups.com >> > > -- > FORUM RULES (986+ members already BANNED for violation) > > 1) Use concise, accurate thread titles. Poor thread titles, like Please > Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice > will not get quick attention or may not be answered. > > 2) Don't post a question in the thread of another member. > > 3) Don't post questions regarding breaking or bypassing any security > measure. > > 4) Acknowledge the responses you receive, good or bad. > > 5) Cross-promotion of, or links to, forums competitive to this forum in > signatures are prohibited. > > NOTE : Don't ever post personal or confidential data in a workbook. Forum > owners and members are not responsible for any loss. > > > ------------------------------------------------------------------------------------------------------ > To post to this group, send email to excel-macros@googlegroups.com >
Check your links, no download file is -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ------------------------------------------------------------------------------------------------------ To post to this group, send email to excel-macros@googlegroups.com
