How to Divide Its own Cell by Another Column Delimited Text (Dynamic Array)

[staticfluids]

Hi all, i hope you're doing well. First of all, sorry for bad English.

I'm currently using Excel 2021 and trying to experiment with Dynamic Array formula lately. It's quite helping with my daily Excel use.
I have a one case where i want to divide its own cell using a value from another cell. And the other cell it's not a number but a general text (number delimited with comma).

I have an array like this (Original):

No	Name	Value	Divided	Ratio	Savings 1	Savings 2	Savings 3
1​	Testing 1	Rp 20,000,000.00	2​	0.5,0.5	Rp 10,000,000.00	Rp 10,000,000.00
2​	Testing 2	Rp 4,000,000.00	1​	1​	Rp 4,000,000.00
3​	Testing 5	Rp 3,000,000.00	1​	1​	Rp 3,000,000.00
4​	Testing 6	Rp 9,000,000.00	3​	0.5,0.2,0.3	Rp 3,000,000.00	Rp 3,000,000.00	Rp 3,000,000.00

F1	="Savings " & SEQUENCE(1,(MAX(D:D)))
F2	=C2/D2 + ((SEQUENCE(,D2)<=C2-D2*C2/D2))

I want to modify the formula on cell F2 so the expected array would be like this:

No	Name	Value	Divided	Ratio	Savings 1	Savings 2	Savings 3
1​	Testing 1	Rp 20,000,000.00	2​	0.5,0.5	Rp 10,000,000.00	Rp 10,000,000.00
2​	Testing 2	Rp 4,000,000.00	1​	1​	Rp 4,000,000.00
3​	Testing 5	Rp 3,000,000.00	1​	1​	Rp 3,000,000.00
4​	Testing 6	Rp 9,000,000.00	3​	0.5,0.2,0.3	Rp 4,500,000.00	Rp 1,800,000.00	Rp 2,700,000.00

P2	=$M2*0.5
R5	=$M5*0.3

I'm trying to look a formula that able to divide Savings column by the Ratio column without having to change manually one by one.
Any idea how?

Thank you.

 

[FormR]

Hi, assuming the "value" column is a number with special formatting - then, if I understand correctly, you can try something like this.

Book1
	A	B	C	D	E	F	G	H
1	No	Name	Value	Divided	Ratio	Savings 1	Savings 2	Savings 3
2	1	Testing 1	20,000,000.00	2	0.5,0.5	40,000,000.00	40,000,000.00
3	2	Testing 2	4,000,000.00	1	1	4,000,000.00
4	3	Testing 5	3,000,000.00	1	1	3,000,000.00
5	4	Testing 6	9,000,000.00	3	0.5,0.2,0.3	18,000,000.00	45,000,000.00	30,000,000.00
Sheet1

Cell Formulas
Range	Formula
F2:G2,F5:H5,F3:F4	F2	=C2/TEXTSPLIT(E2,",")
Dynamic array formulas.

 

[Fluff]

Another option

Fluff.xlsm
	A	B	C	D	E	F	G	H
1	No	Name	Value	Divided	Ratio	Savings 1	Savings 2	Savings 3
2	1	Testing 1	20,000,000.00	2	0.5,0.5	40,000,000.00	40,000,000.00
3	2	Testing 2	4,000,000.00	1	1	4,000,000.00
4	3	Testing 5	3,000,000.00	1	1	3,000,000.00
5	4	Testing 6	9,000,000.00	3	0.5,0.2,0.3	18,000,000.00	45,000,000.00	30,000,000.00
Sheet6

Cell Formulas
Range	Formula
F2:G2,F5:H5,F3:F4	F2	=TRANSPOSE(C2/FILTERXML("<k><m>"&SUBSTITUTE(E2,",","</m><m>")&"</m></k>","//m"))
Dynamic array formulas.

 

[staticfluids]

Hi FromR and Fluff,
Thank you for the answer. Both of them working perfectly, and i got the logic. But due to my Excel version, my setup doesn't support TEXTSPLIT function. So, I used the Fluff version.

However, I may have used the wrong wording as it results the wrong question, so you must be misunderstood what I want. Sorry, my fault. (The logic is same though )
What I expect would be, the F cell is the result from Value multiplied by the Ratio. So, the result to each Savings would produce the SUM of the Savings to be the same value as the C cells.

My main objective is to divide each Savings per part.
Example:
0.5 (50%) of 9.000.000 for the Savings 1 = 4.500.000,
0.2 (20%) of 9.000.000 for the Savings 2 = 1.800.000, and
0.3 (30%) of 9.000.000 for the Savings 3 = 2.700.000 and so on

But I think I can just give a little experiment with the formula you provided. Thanks for being helpful.

Have a great day!

 

[FormR]

But I think I can just give a little experiment with the formula you provided.

I think you just need to change the "/" to a "*" - i.e. multiply rather than divide by the ratios.

 

[staticfluids]

I think you just need to change the "/" to a "*" - i.e. multiply rather than divide by the ratios.

Yes indeed

 

[Fluff]

Glad we could help & thanks for the feedback.