I have a cell in google sheets with the entry 20/40/40. I would like to create a formula that will split the string into the substrings 20, 40, 40 e.g. s1, s2, s3 = 20, 40, 40. I would then like to perform a different numerical calculation on each of the substrings to get 3 variables e.g. v1, v2, v3 = cell_number*(s1/(100*4)), cell_number*(s2/(100*4)), cell_number*(s3/(100*9)), whereby cell_number refers to the cell reference of a cell containing a numerical value.
I would then like to concatenate these values into a new cell in the same format as the original cell e.g. cell A5 would look like v1/v2/v3.
I would like to do this because the values of s1, s2, s3 will be changing and I must perform this calculation multiple times. If I extend my table it will be unreadable and the only other solution is to make another table with the variations of s1, s2 and s3 I need use and some way concatenate results of the formulas used on these substring variables to give me v1/v2/v3.
I am completely new to google sheets and javascript and at a complete loss at to how to go about it. I did begin to try to create my own function but I am not sure how to approach it in javascript.
A1:
20/40/40
B1:(cell_number)
5
C1:
=ARRAYFORMULA(JOIN("/",B1*(SPLIT(A1,"/")/{100*4,100*4,100*9})))
SPLIT A1 by / to get numbers. 20|40|40 / Divide it by a array of 100. 20/400|40/400|40/900 * Multiply cell_number B1. 5*20/400|5*40/400|5*40/900 JOIN the result back with /