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 “sublist” 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_IncDec].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_IncDec].Nth(1) = 0,
Sequence(1,Total_Lists).FormulaMap(List_1.Nth(1)),
ListCombine(
Sequence(
List_1.Nth(1),
List_2.Nth(1),
[Values_IncDec].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 Allinone
at the bottom of the doc )
Sequence(1,3).FormulaMap(
(List_2.Nth(CurrentValue)  List_1.Nth(CurrentValue))/(Total_Lists1)
).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 !