Distribute value

[Thomas Kuriakose]

Respected Sirs,

We have a file with values to be distributed proportionately.

For each name in A4:A8, the value D2 should be apportioned in D4:D6 based on percentage of C4:C6.
For each name in A4:A8, the value E2 should be apportioned in D7:D8 based on percentage of C7:C8.

The expected results are in G4:G8.

Thank you very much,

with regards,
thomas

 

[bosco_yip]

1] "Distribution" D4,

=IF(A4="TK",D$2,E$2)/SUMIF(A$4:A$8,A4,C$4:C$8)*IF(A4="TK",D$2,E$2)

or,

=IF(A4="TK",D$2,E$2)^2/SUMIF(A$4:A$8,A4,C$4:C$8)

2] "Value2" G4,

=F4*IF(A4="TK",D$2,E$2)

Regards
Bosco

 

[Thomas Kuriakose]

Respected Sir,

Thank you very much for the solution provided. Much appreciated.

The actual file has a lot of names.

Kindly guide on how to reference the names when there are multiple entries without entering each name.

Thank you very much,

with regards,
thomas

 

[p45cal]

A formula such as:
=INDEX($D$2:$E$2,MATCH($A4,{"TK","BD"},0))*C4/SUMIF($A$4:$A$8,A4,$C$4:$C$8)

See attached.

 

[bosco_yip]

Respected Sir,

Thank you very much for the solution provided. Much appreciated.

The actual file has a lot of names.

Kindly guide on how to reference the names when there are multiple entries without entering each name.

Thank you very much,

with regards,
thomas

If the name list more than 2, then used INDEX+MATCH

e.g.

=INDEX(D$2:F$2,MATCH(A4,{"TK","BD","CT"},0))

which is better to adopt CHOOSE+MATCH

Regards
Bosco

 

[Thomas Kuriakose]

Respected Sirs,

Apologies for not giving all the requirements.

Thank you so much for the support and guidance on this.

I have now clipped the almost original file(a copy), where data is in two tabs. The Actual tab has the reference number, name and value. The lookup tab has name and the value to be distributed in Actual tab D2 to D31 based on values in C2 to C31 for the corresponding names.

Thank you very much once again.

with regards,
thomas

 

[p45cal]

see attached.
=INDEX(Lookup!$B$2:$B$11,MATCH($B2,Lookup!$A$2:$A$11,0))*C2/SUMIF($B$2:$B$31,B2,$C$2:$C$31)

or:
=VLOOKUP(B2,Lookup!$A$1:$B$11,2,0)*C2/SUMIF($B$2:$B$31,B2,$C$2:$C$31)

 

[bosco_yip]

Since your Lookup Table has been sorted, you can use LOOKUP instead of INDEX+MATCH or VLOOKUP which is shorter and faster.

=LOOKUP(B2,Lookup!A$2:B$11)*C2/SUMIF(B:B,B2,C:C)

Regards
Bosco

 

[Thomas Kuriakose]

Respected Sirs,

Thank you very much for the solutions provided.

Much much appreciated,

with regards,
thomas