Formula to allocate figures from one table to another based on percentages

[jkerrigan93]

Hi all,

I would like to allocate figures from one table (data table) to another table (results table) based on another table with percentages. I've attached the workbook and will attempt to explain what I'm trying to do.

Table1 is the data table and has figures against Row A, B and C.

Table2 determines how these percentages are distributed. For example, the Row A figures of 12 (Column A) and 10 (Column B) are allocated 100% to Text A. The Row B figures are allocated 100% to Text B. The Row C figures are allocated 50% to Text A and 50% to Text B.

Table3 is the results table (formula will be in columns J and K) which will have the figures allocated to it from Table1 based on the percentages in Table2. The value for Column A will equal 27 for Text A as it is allocated figures 12 from Row A and 15 (30 * 50%) from Row C.

Thanks in advance for anyone that can help.

[XLent]

one possibility

Please Login or Register  to view this content.

[jkerrigan93]

one possibility

Please Login or Register  to view this content.

Thanks, really really close. The only addition I want to make is for the headers of Column A and Column B to be part of the formula. So instead of B2:B4 it's B2:C4 and the column is decided based on the header (e.g. B2:B4 when Column A and C2:C4 when Column B).

Edit:
I think I've got it now, just updated the first bit of the formula and seems to be working perfectly! I prefer using field names in formulas so updated as such.

Please Login or Register  to view this content.

Cheers for the help! Tested in my main workbook too and all working perfect. I'll mark as solved.