Hi, I would like to allocate a value in different rows into multiple rows. For example, the below tables show what I would like to achieve. Source table ItemAmounta 1b 1c 1a+b 2 (to be allocated into a ,b)a+b+c3 (to be allocated into a, b, c) Resultant table ItemAmounta3 (1+1+1)b3 (1+1+1)c2 (1+1) Could anyone please advise which tool(s) & formulas that I can use to make this happen? Kind regards, Jaesun

Calculation of a value into multiple row

Hi,

I would like to allocate a value in different rows into multiple rows. For example, the below tables show what I would like to achieve.

Source table

Item	Amount
a	1
b	1
c	1
a+b	2 (to be allocated into a ,b)
a+b+c	3 (to be allocated into a, b, c)

Resultant table

Item	Amount
a	3 (1+1+1)
b	3 (1+1+1)
c	2 (1+1)

Could anyone please advise which tool(s) & formulas that I can use to make this happen?

Kind regards,

Jaesun

Common Use Cases

Accepted answers

Felipe_Ribeir0

Hi @JaesunK

Although your input (Book1.xlsx) seems similar to the first sample, the solution is very different (for example, the first sample had + separating items, this new input uses /). Anyway, here is the solution adapted to the second input.

Calculation of a value into multiple rowv3.yxmd

Felipe_Ribeir0

@JaesunK you can use formulas like these ones to do this:

Calculation of a value into multiple rowv4.yxmd

All comments

Felipe_Ribeir0

Hi @JaesunK

One way of doing this (updated)*:

Input:

Output:

Calculation of a value into multiple rowv2.yxmd

JaesunK

Felipe_Ribeir0

Thanks for your reply and the proposed solution.

I noticed that this proposed solution might only work for the sample I provided.

I have attached another sample with the actual numbers as attached (it has input, working, and outcome tab separately)

Could you please help me with this sample?

Kind regards,

Jaesun

Book1.xlsx

Felipe_Ribeir0

Hi @JaesunK

Calculation of a value into multiple rowv3.yxmd

JaesunK

This is fantastic!

Felipe_Ribeir0

Kind regards,

Jaesun

JaesunK

Felipe_Ribeir0

Just one quick additional question please.

Now I would like to allocate "Distribution Alpha/Beta/Gamma" into a certain ratio like 50% Alpha, 40% beta and 10% Gamma, would you be able to advise how I can reflect this change please?

Kind regards,

Jaesun

Felipe_Ribeir0

@JaesunK you can use formulas like these ones to do this:

Calculation of a value into multiple rowv4.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703