Finding the Acending of 4 Variables

[jimkent]

Hi, I have an interesting problem that I can solve myself but it seems to be taking too may variables and manipulations that must be easier.

I have 4 global variables within a record, let's call them V1 V2 V3 and V4. Is there any way to sort these variables Lowest to highest? For instance, I have numbers in each variable such as v1=25 v2 = 65, v3=75 and v4=35. Is there a way to sort these variables into positions lowest to highest? Maybe set 4 new Global variables with the sorted value like S1=25 S2=35 S3=65 and S4=75?

I can get the lowest and the highest with the Min and Max functions but I can't seem to figure out the other two middle ones. Any thoughts? Thanks.

[mr_vodka]

What is the purpose of this? Variables are temporary storage holders. I cant see why you would need to do this. Please elaborate.

[The Shadow]

This seems like fun, I'll have a go.

  s1 set to: Min(v1,v2,v3,v4)

  s4 set to: Max(v1,v2,v3,v4)

Now the harder ones could be done as:

  s2 set to: Case(s1=v1; Min(v2;v3;v4); s1=v2; Min(v1;v3;v4); s1=v3; Min(v1;v2;v4); Min(v1;v2;v3))

  s3 set to: v1+v2+v3+v4 - (s1+s2+s4)

It seems like that s2 could still be made to use less comparisions, via a binary search. But that isn't important if there really will only ever be 4 variables:

  s2 set to: Case(s1=Min(v1;v2); If (s1=v1;Min(v2;v3;v4); Min(v1;v3;v4)); s1=v3; Min(v1;v2;v4); Min(v1;v2;v3))

Edited April 3, 2008 by Guest
Clarify = verus setting variable.

[jimkent]

You know, that just might work! I guess you could also set S3 equal to the same as how you did S2 except change Min to Max, but that is not as creative as your way. I like it!... Thanks.

[Søren Dyhr]

I came to think of what I would do if the number not in any way could be predicted ... recreating somewhat like dynamic value list and ValueListItems( if we just could link on the record ID in a self join. When dealing with variables must it be some way to deal with it in a script regardless of context.

If we define a CF like JMO's:

LeftWords(Case(End + 1 > Start; Start & "¶" & Range(Start + 1; End)); 999999999)

Could it be used in a calc' this way:

Let([

t=Substitute ( input ; "-" ; "¶" );

tt=Substitute ( input ; "-" ; ";" );

ttt=Evaluate("Min(" & tt & ")");

tttt=Evaluate("Max(" & tt & ")")];

LeftWords(Substitute(FilterValues ( Range ( ttt ; tttt ) ; t );"¶";"-");99999999)

)

Other sorting algorithms like Bubble Sort might though be faster - since there always are a price to pay when using Evaluate( speedwise:

http://www.briandunning.com/cf/27

...or the QuickSort:

http://www.briandunning.com/cf/89

--sd