Dealing with constraints of Excel for Android

[IDidNotDoIt]

Hi, all!

I have a workbook that solves the problem of finding the optimal way to go thru a group of addresses identified by their geographic coordinates, in order to minimize the distance.

Technically speaking, It is developed using VBA and array formulas, as it was originally intended to be used on a single computer at the back office of the main warehouse. Later it shifted to several computers at each warehouse branch, and after that to notebooks and tablets distributed on the delivery vehicles. In both cases there were no major changes to be made to adapt to those new scenarios.

Now a new shift is being analized where data processing should be done at cell phones too. So considering the constraints imposed by the Android version of Excel, there is a lot of work to do. Work mainly related to VBA code replacement and eliminating array formulas, which is yet done exception made of the following problem:

Let's say that we have distances to 5 points at B2:F2 (where 99 means same source and target point or point yet used), 5 indicators of points already used at A3:A7 (where 0 means point not reached and 1 point yet reached), and the goal is to obtain the number of the point not used with the minimum distance to it.

The goal is to obtain a value of 2 (2nd position) at B8, that represents the point at C2 (or E2 in distinctly, but I prefer C2) that has a distance of 2 units which is the shortest distance to not used points, i.e., the point with a distance of 1 unit at B2 is yet used as per the value of 1 at A3.

At C8 there is a first approach using SUMPRODUCT which works as expected, but at D8 there is an intent of fine tuning OF previous SUMPRODUCT which does not work, due to the constraints of mobile Excel version. I tried to use MMULT too but with unsatisfactory results.

The idea is to obtain kinda this:
a) start with B2:F2 values
{1,2,11,99,2}
b) update according with A3:A7 values
{1,0,0,1,0}
{99,2,11,99,2}
x) get the order of min value
2, at 2nd or 5th position.

Playing maths:
=MATCH(MIN(SUMPRODUCT((B2:F2)<>99*,(TRANSPOSE(A3:A7))<>1);SUMPRODUCT((B2:F2)<>99*,(TRANSPOSE(A3:A7))<>1))

Something alike with these conditions:
- no helper cells allowed
- no array formulas allowed
-... that's to say, just a non-array formula

Attached file with sample values.

Thank you so much for your time and help.
Best regards.

 

[IDidNotDoIt]

Hi, all!

I have a workbook that solves the problem of finding the optimal way to go thru a group of addresses identified by their geographic coordinates, in order to minimize the distance.

Technically speaking, It is developed using VBA and array formulas, as it was originally intended to be used on a single computer at the back office of the main warehouse. Later it shifted to several computers at each warehouse branch, and after that to notebooks and tablets distributed on the delivery vehicles. In both cases there were no major changes to be made to adapt to those new scenarios.

Now a new shift is being analized where data processing should be done at cell phones too. So considering the constraints imposed by the Android version of Excel, there is a lot of work to do. Work mainly related to VBA code replacement and eliminating array formulas, which is yet done exception made of the following problem:

Let's say that we have distances to 5 points at B2:F2 (where 99 means same source and target point or point yet used), 5 indicators of points already used at A3:A7 (where 0 means point not reached and 1 point yet reached), and the goal is to obtain the number of the point not used with the minimum distance to it.

The goal is to obtain a value of 2 (2nd position) at B8, that represents the point at C2 (or E2 in distinctly, but I prefer C2) that has a distance of 2 units which is the shortest distance to not used points, i.e., the point with a distance of 1 unit at B2 is yet used as per the value of 1 at A3.

At C8 there is a first approach using SUMPRODUCT which works as expected, but at D8 there is an intent of fine tuning OF previous SUMPRODUCT which does not work, due to the constraints of mobile Excel version. I tried to use MMULT too but with unsatisfactory results.

The idea is to obtain kinda this:
a) start with B2:F2 values
{1,2,11,99,2}
b) update according with A3:A7 values
{1,0,0,1,0}
{99,2,11,99,2}
x) get the order of min value
2, at 2nd or 5th position.

Playing maths:
=MATCH(MIN(SUMPRODUCT((B2:F2)<>99*,(TRANSPOSE(A3:A7))<>1);SUMPRODUCT((B2:F2)<>99*,(TRANSPOSE(A3:A7))<>1))

Something alike with these conditions:
- no helper cells allowed
- no array formulas allowed
-... that's to say, just a non-array formula

Attached file with sample values.

Thank you so much for your time and help.
Best regards.

Unable to attach file. Please get it at this link:

Dropbox

www.dropbox.com

 

[Luke M]

I'm having trouble understanding your example. It looks like the 2 is the position you want, and is one of the inputs? Is the desired result 100, 0, or something else? I think I'll either need more examples, or for you to describe in more detail what exactly you want the computer to do.

 

[IDidNotDoIt]

Hi, Luke M!

Thank you very much for time, I highly appreciate it.

I don't know if you've been able to download the sample file as I'm still unable to upload the sample using the built-in feature from my Android phone. However I'll try to write down an example with numbers not so confusing.

Let's take a case of 5 points. In the matrix of distances (5x5) the distance between points (i, j) is set to 1E6 (formerly 99) when i=j, i.e. same point, otherwise it has the actual distance. Also element d(i, j)=d(j, i), because of the law of reversibility of optical paths.

d11, d12, d13, d14, d15
d21, d22, d23, d24, d25
d31, d32, d33, d34, d35
d41, d42, d43, d44, d45
d51, d52, d53, d54, d55

We start at the point 1 (home), which in this case is in 1st order in the list, but it maybe any other else (there's a a parameter which indicates home point). The lesser distance is to point 4 with a value of 10.

1e6, 100, 050, 010, 012
100, 1e6, 080, 020, 080
050, 080, 1e6, 110, 030
010, 020, 110, 1e6, 020
012, 080, 030, 020, 1e6

So for the uploaded sample I placed the 4th row at range B2:F2, with values multiplied by 10 to avoid ambiguities and replaced 99 by 1E6 (i.e. a huge distance greater than all others).

B2:F2
010, 020, 110, 1E6, 020

At a first sight the nearest point is point 1 with a distance of 10, but we've already passed by it. That is indicated with an array of previously used points, and in the example is placed in A3:A7.
{1, 0, 0, 1, 0} transposed.

That is to say, point 1 is used (home, from where we started) and point 4 is used (actual point).

Said so, the goal is to obtain the next point (order in the list) with the minimum distance from actual point. In this last example the lesser value is 20, either for 2nd or 5th points (I'd prefer to obtain the 2nd but it's irrelevant).

Now, how do I get the value of 2, order of the nearest not yet reached point?
a) taking the range B2:F2
010, 020, 110, 1E6, 020
b) replacing original values with 1E6 accordingly to A3:A7 = 1 (0 point not used, 1 used point)
1, 0, 0, 1, 0
c) getting this array:
1e6, 020,110, 1e6, 020
d) obtaining the order of min value.

Hope I made myself clear. Thanks in advance.

Best regards.

 

[Luke M]

Are we wanting the formula to return the number 020 (the lowest value found), or the number 2 (the position of lowest value)?

I know you didn't want to use helper cells, but having the vertical and horizontal ranges is going to make this tough w/o arrays. If we could use 5 horizontal cells, that would make this less of a pain.

In reality, do we just have 5 points, or much larger, or is it dynamic? If just 5, we could build a long formula that checks each item and then feeds into a MIN, like

=MIN(IF(AND(B2<99, A3=0), B2), IF(AND(C2<99, A4=0), C2), IF(AND(D2<99, A4=0), D2), ...)

 

[IDidNotDoIt]

Are we wanting the formula to return the number 020 (the lowest value found), or the number 2 (the position of lowest value)?

I know you didn't want to use helper cells, but having the vertical and horizontal ranges is going to make this tough w/o arrays. If we could use 5 horizontal cells, that would make this less of a pain.

In reality, do we just have 5 points, or much larger, or is it dynamic? If just 5, we could build a long formula that checks each item and then feeds into a MIN, like

=MIN(IF(AND(B2<99, A3=0), B2), IF(AND(C2<99, A4=0), C2), IF(AND(D2<99, A4=0), D2), ...)

The number 2, that's to say the order of the 1st (or last, it's up to you) point with minimum distance.

The constraints of not using helper cells is that users may select any number of points, 100, 1000... At the original workbook for Excel for Windows, dynamic ranges, array formulas and a bit of VBA did the job. At the new workbook for Excel for Android, neither array formulas nor VBA code. Firstly I thought on helper columns, but for a thousand points I need a million rows per a thousand columns, to avoid circular references. That's why I was looking for a formula to handle that tricky array fix.

 

[IDidNotDoIt]

Re reading again, that's for third time your last post, and regarding my million helper cells range, if you can manage to use N cells (either row or column wise), I think I could adapt the model.

The reason of the range of used points vertically (by column) is because of the model that requires for each point a list of yet-passed points, and as the solution places the resulting points at 1st row, the distances to them in the 2nd row, the aggregated distance in the 3rd row, all this above the NxN matrix of distances, and at an auxiliary worksheet vertically the previously used points for each new point of the solution. If this vertical designs bothers, I can easily switch it to a horizontal design. To put it simple: do the array trick however you want within a formula and I'll adapt the model.

 

[Luke M]

Re reading again, that's for third time your last post, and regarding my million helper cells range, if you can manage to use N cells (either row or column wise), I think I could adapt the model.

Let's go for that. Since it has to be a square, we know there's a set limit as to how many number's we're dealing with. To make it clean, you could put this helper in a different sheet; for now I'll just pretend it starts in B1.

formula in B1

=IF(AND(INDEX($A:$A, 1+COLUMN())=0, B2<99, ISNUMBER(B2)), B2, "")

and copy as far right as could ever be needed. Then you have all your checks built into a single formula. The min value is simply
=MIN(1:1)

and position would be
=MATCH(MIN(1:1), 1:1, 0)-1

 

[IDidNotDoIt]

Hi, Luke M!

Please give me until tomorrow to test and try to deploy this variant and I'll get back to you ASAP.

Thanks a lot.
Regards.

 

[IDidNotDoIt]

Hi, Luke M!

I'm afraid that we'll have to go for the variant without helper row, since it's required to have a helper row per each point, because even if the distance matrix remains constant (ergo, row used for each point too), the array of used points changes for each new point.

In the5x5 sample:

1e6, 100, 050, 010, 012
100, 1e6, 080, 020, 080
050, 080, 1e6, 110, 030
010, 020, 110, 1e6, 020
012, 080, 030, 020, 1e6

With point #1 set as home point, these are the rows of distance matrix and the column (or row or array) of yet used points:

1st point (home): point #1
Used points: 0,0,0,0,0
No point search required as it's the starting point.

2nd point:
Distance row from last point (#1): 1e6, 100, 050, 010, 012
Used points: 1,0,0,0,0
Tricky array: 1e6, 100, 050, 010, 012
Min distance: 010
2nd point: #4

3rd point:
Distance row: 010, 020, 110, 1e6, 020
Used points: 1,0,0,1,0
Tricky array: 1e6, 020, 110, 1e6, 020
Min distance: 020
3rd point: #2 (or #5, let's take #2)

4th point:
Distance row: 100, 1e6, 080, 020, 080
Used points: 1,1,0,1,0
Tricky array: 1e6, 1e6, 080, 1e6, 080
Min distance: 080
4th point: #3 (or #5, let's take #3)

5th point:
Distance row: 050, 080, 1e6, 110, 030
Used points: 1,1,1,1,0
Tricky array: 1e6, 1e6, 1e6, 1e6, 030
Min distance: 030
5th point: #5

Final solution (returning home):
Points in order: 1,4,2,3,5,1
Distances: 010, 020, 080, 030, 012
Total distance: 152

As you can see, the helper row changes for each point, which means that for N points it requires NxN helper cells (i.e. N rows). And this is not suitable for use on a cell phone, just for 100 points were talking of 10000 cells, for 1000 the number raises to a million.

That's why I thought about a formula-only solution for handling the damned tricky array.

Please let me know if you happen to create that formula, or if you find a workaround for solving the whole problem as described above step by step. Despite the behaviour of Excel for Android I don't have any constraints to develop a solution, just the NxN matrix of distances and the home point.

Thank you very much for you time, man.

Best regards.