Welcome Guest! To enable all features please Login. New Registrations are disabled.

Notification

Icon
Error

Login


Options
Go to last post Go to first unread
Offline ndtma  
#1 Posted : 18 February 2018 18:15:39(UTC)
ndtma


Rank: Advanced Member

Groups: Registered
Joined: 05/06/2014(UTC)
Posts: 348
Man
Sri Lanka
Location: Colombo

Was thanked: 124 time(s) in 82 post(s)
An automatic electronic device had been installed on the bank of a stream to record and transmit Water Levels (at half hour intervals) to a control center, where the readings were converted to discharge values, for further analysis, using an already established Rating Table. These data are given in an Excel file.

The attached SS file takes nearly 2 minutes to convert 1 month data values (48x31=1488).
I have tried this with several previous SS versions also.
Can this be improved, as we have to deal with data recorded for over 5 years?
My system: Win 10 Pro, 64 bit, Core i5, 6 GB Ram Laptop.
Any help will be much appreciated please

WL To Q.sm (19kb) downloaded 28 time(s).
1.July WL&RT.xlsx (30kb) downloaded 47 time(s).

vlookup delay1.jpg vlookup delay2.jpg

Edited by user 18 February 2018 18:20:49(UTC)  | Reason: Not specified

Look within!... The secret is inside you.
Best Regards
Eng. NDTM Amarasekera - Sri Lanka

Wanna join the discussion?! Login to your SMath Studio Forum forum account. New Registrations are disabled.

Offline Andrey Ivashov  
#2 Posted : 18 February 2018 21:42:00(UTC)
Andrey Ivashov


Rank: Administration

Groups: Developers, Registered, Knovel Developers, Administrators, Advanced Member
Joined: 11/07/2008(UTC)
Posts: 1,616
Man
Russian Federation

Was thanked: 1978 time(s) in 666 post(s)
Hello.

First impression is that you should try using findrows(...) function (built in into the program) in this case.

findrows_1.PNG

Code:
vlookup(value,table,column):line(findrows(table,value,column-1),1,1)


Before change:
2 min. 14 sec.

After change:
2.4 sec.

But I see different results of using vlookup implemented in your worksheet (returns 1488 rows vector) and findrows (returns 2976 rows vector). Unfortunately I have no chance to deep into the logic, but this may be a good starting point.

Best regards.
Offline Andrey Ivashov  
#3 Posted : 18 February 2018 22:23:08(UTC)
Andrey Ivashov


Rank: Administration

Groups: Developers, Registered, Knovel Developers, Administrators, Advanced Member
Joined: 11/07/2008(UTC)
Posts: 1,616
Man
Russian Federation

Was thanked: 1978 time(s) in 666 post(s)
Did find 5 minutes more to look into it and finally I did it:

WL To Q_mod1.sm (17kb) downloaded 46 time(s).
Takes 1.7 sec. on my environment.

Best regards.
Offline Jean Giraud  
#4 Posted : 18 February 2018 23:19:13(UTC)
Jean Giraud

Rank: Guest

Groups: Registered
Joined: 04/07/2015(UTC)
Posts: 6,868
Canada

Was thanked: 980 time(s) in 808 post(s)
Originally Posted by: Andrey Ivashov Go to Quoted Post
First impression is that you should try using findrows(...) function (built in into the program) in this case.


Maybe the other way around => vlookup needs be doctored.
Mine works fine 0.06 s scanning 9 rows for one value
FindRows => 2.4 s scanning 31 rows ... for ONE value, is it ?

Utilities Matrix lookup.sm (12kb) downloaded 35 time(s).

Offline ndtma  
#5 Posted : 19 February 2018 05:16:33(UTC)
ndtma


Rank: Advanced Member

Groups: Registered
Joined: 05/06/2014(UTC)
Posts: 348
Man
Sri Lanka
Location: Colombo

Was thanked: 124 time(s) in 82 post(s)
Originally Posted by: Andrey Ivashov Go to Quoted Post
Did find 5 minutes more to look into it and finally I did it:

<a class="attachedImageLink {html:false,image:false,video:false}" href="/forum/resource.ashx?a=22240


Thank you very much Andrey!. It's wonderful.

Edited by user 19 February 2018 05:23:16(UTC)  | Reason: Not specified

Look within!... The secret is inside you.
Best Regards
Eng. NDTM Amarasekera - Sri Lanka
Users browsing this topic
Forum Jump  
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.