Hi there Fellow Makers 
!
As the title of this topic says, I’m looking for ideas/insights 
to potentially “simplify” a somewhat long formula.
The formula does exactly what I need but I have the feeling it contains some redundancy which could be removed … The only problem is that I just don’t see how, for some reasons 
.
As I tend to naturally overcomplicate things, maybe there are “better” ways to get to my desired results too 
…
Any ideas or help will be deeply appreciated 
!
PS: Sorry if everything is not really clear 
… This was pretty hard to explain .
Ok, so here’s my “problem” .
Let’s say I have 2 lists containing a set of 3 numbers (each number is entered through sliders) :
List_1(value 1, value 2, value 3)List_2(value 1, value 2, value 3)
In between those 2 lists, I want to be able to create a precise amount of lists so the total matches a user defined quantity of lists (i.e.: List_1 + Lists_in_between + List_2 = user defined quantity x).
The Lists_in_between should contain sets of incremental or decremental values, going from each values in List_1 to each values in List_2 (not sure this is really clear ).
Trying to say this differently :
If List_1(value 1, value 2, value 3) is my Start list and List_2(value 1, value 2, value 3) is the End I want to create :
-
List_1(value 1, value 2, value 3) -
Lists_in_between(
List_1(value 1)→List_2(value 1),
List_1(value 2)→List_2(value 2),
List_1(value 3)→List_2(value 3)
) -
List_2(value 1, value 2, value 3)
And the Total of lists needs to be precisely equal to the user defined input .
To do this, as you’ll see in the sample doc below, I first calculate the Increment or Decrement for each “set” of values so I could use it with Sequence().FormulaMap(...) to create “sub-list” for each values 1, values 2 and values 3.
I do this with this formula :
Sequence(1,3).FormulaMap(
(List_2.Nth(CurrentValue) - List_1.Nth(CurrentValue))/(Total_Lists - 1)
)
Then as I was hoping to use this simple formula to create my desired lists :
Sequence(1,3).FormulaMap(
Sequence(
List_1.Nth(CurrentValue),
List_2.Nth(CurrentValue),
[Values_Inc-Dec].Nth(CurrentValue)
)
)
I discovered it was returning an imprecise number of values, often less than what’s needed (and I really need that number to be the exact same number of values for each values 1, values 2, values 3 so I can create the exact total number of lists determined by the user)…
So, trying to solve that imprecision trouble, I count (for each values 1, values 2, values 3) the values returned by the formula just above and use these counts to compare them to the desired total.
For each values 1, values 2, values 3, if the count is less than the desired total, I ListCombine() the appropriate values + the missing one (as the missing value is just the last value… so the value in List_2).
I do this for each “set” of values 1, values 2 and values 3 with a formula looking like this :
If(
[Values_Inc-Dec].Nth(1) = 0,
Sequence(1,Total_Lists).FormulaMap(List_1.Nth(1)),
ListCombine(
Sequence(
List_1.Nth(1),
List_2.Nth(1),
[Values_Inc-Dec].Nth(1)
).FormulaMap(
Round(CurrentValue,1)
),
If(
Values_Count.Nth(1) < Total_Lists,
List_2.Nth(1),
"")
).Filter(CurrentValue.IsNotBlank()))
The last step just consist to put everything together with this formula (see the Result part in my sample) :
Sequence(1,Total_Lists).FormulaMap(
List(
Values_1.Nth(CurrentValue),
Values_2.Nth(CurrentValue),
Values_3.Nth(CurrentValue)
)
).BulletedList()
Now, for the sake of playing around with WithName() and because I’m still unsure about the direction I’m going to take with this doc (), I wanted to put everything into one formula :
(See Result All-in-one at the bottom of the doc )
Sequence(1,3).FormulaMap(
(List_2.Nth(CurrentValue) - List_1.Nth(CurrentValue))/(Total_Lists-1)
).WithName(Values_Inc_Dec,
Sequence(1,3).FormulaMap(
Sequence(
List_1.Nth(CurrentValue),
List_2.Nth(CurrentValue),
Values_Inc_Dec.Nth(CurrentValue)).Count()
).WithName(Values_Count,
If(
Values_Inc_Dec.Nth(1) = 0,
Sequence(1,Total_Lists).FormulaMap(
List_1.Nth(1)
),
ListCombine(
Sequence(
List_1.Nth(1),
List_2.Nth(1),
Values_Inc_Dec.Nth(1)
).FormulaMap(
Round(CurrentValue,1)
),
If(
Values_Count.Nth(1) < Total_Lists,
List_2.Nth(1),
""
)
).Filter(CurrentValue.IsNotBlank())
).WithName(Values_1,
If(
Values_Inc_Dec.Nth(2) = 0,
Sequence(1,Total_Lists).FormulaMap(
List_1.Nth(2)
),
ListCombine(
Sequence(
List_1.Nth(2),
List_2.Nth(2),
Values_Inc_Dec.Nth(2)
).FormulaMap(
Round(CurrentValue,1)
),
If(
Values_Count.Nth(2) < Total_Lists,
List_2.Nth(2),
""
)
).Filter(CurrentValue.IsNotBlank())
).WithName(Values_2,
If(
Values_Inc_Dec.Nth(3) = 0,
Sequence(1,Total_Lists).FormulaMap(
List_1.Nth(3)
),
ListCombine(
Sequence(
List_1.Nth(3),
List_2.Nth(3),
Values_Inc_Dec.Nth(3)
).FormulaMap(
Round(CurrentValue,1)
),
If(
Values_Count.Nth(3) < Total_Lists,
List_2.Nth(3),
""
)
).Filter(CurrentValue.IsNotBlank())
).WithName(Values_3,
Sequence(1,Total_Lists).FormulaMap(
List(
Values_1.Nth(CurrentValue),
Values_2.Nth(CurrentValue),
Values_3.Nth(CurrentValue)
)
)
)
)
)
)
).BulletedList()
But the parts where I define my Values_1, Values_2 and Values_3 is bothering me a little. This is the part I see as redundant and I would like to optimize but just seem enable to do .
Here’s the sample
If anyone has any (better) ideas to get to my desired results or remove the redundant part, I’m all eyes and ears !
Thank you very much in advance 
!
… and you’re pretty close 
!
.
)
… But it was just impossible to have a simple 
!
… I just can’t find what 
)
!!!
… But I’m more than grateful that you took that time to help me