I need to make a calculation that involves lists in list of things.
So, I have a list of “efforts”. I need to do a calculation to all of them. So for each item in the list of Efforts I need to calculate the value of “progress”.
For each effort the value of progress = sum(crypto:amount * crypto:value)/effort:goal
so for each effort, each crypto get its amount and value multiplied and all those products are summed up. this is divided by the goal.
So for each effort there is a list of cryptos that need a calculation done with them.
I get lost quickly in the dynamic text I need to use, or even when I use a math.js block. This isn’t just a sumproduct.
I guess the magic you are looking for is format as text. When you format as text, you can do more complex calculations for each crypto item and then merge/split them and continue with effort calculations.
See the demo below:
While calculating the progress column, I do the following:
this totally worked. But I do not understand a single part of the calculation or how you know to use those. Could you describe why you put each thing in there.
well you need to reference the effort you are talking about
obviously you need to reference the list of cryptos from the element
format it as text because it is an arcane black art
format as text simply gets a list and returns a string. While returning the string, you can restructure each element in this list (like the one I did above, some calculation).
For example if your list has these four elements: a b c d, you can format this list as text and separate (delimit) them with asterisk * and you can get a*b*c*d as a single string.
In this example, I didn’t do anything extra to each element than just appending them together. I could have done this. Get each list element, duplicate them (append it with itself), and then delimit it with asterisk *. In this case, it would return: aa*bb*cc*dd.
I don’t understand why the decimal point doesn’t also get converted to text. I also don’t understand why I can do a “formatted as a number” on the resulting value in each crypto when I calculate it.
Hmmm, now I dont mean to question the master… But I am getting a result that indicates something is wrong.
The selected part of the equation is the “format as text”, and the result of that part, is:
0 | 10000 (prices have changed today)
There are 2 cryptos in the list. The first one has zero balance, the second one has 100.00. It seems to be that is is converting to text without the ‘.’
the "each item converted to a number, shows, again, 10000 instead of 100.00
So then the two numbers are summed, the answer to that is 10000, and this is divided by the goal, in this case 5000. The result of this is 200… which is the answer it shows at the end
It seems to be that the decimal point is being ignored when converting to text. No?
Decimal point is not ignored. I have tested it by the way. So, if anything, there is something wrong with the calculation.
Format as text multiples (because we specified it to do so) the value and the amount of the crypto. The value is 100.00 as you said, and the amount for the second one is 100 as you noted in the previous message, so the multiplication result for the second item is: 100.00*100 equals to 10000.
If the goal is 5000, the result should be 2 actually. But then you multipled with 100 makes it 200. Multiplication by 100 was not in my original post, so you added it later for a reason I guess?