Excel formula to proportionally allocate values

Dear @TJV

Thank you for reaching out to us on the Microsoft 365 Q&A forum. I understand you want to allocate three values proportionally using only two formulas instead of three. Here’s a concise solution: 

In your Desired setup (rows 12–14), you can use these two formulas to proportionally allocate the values, including the unallocated 200: 

• For Cell D13 (X): 
  =B13*$D$15/ ($B$15 - $B$12) 
  This formula allocates the 300 (B13) plus a proportional share of the 200 (B12). 
  
• For Cell D14 (Y): 
  =B14*$D$15/ ($B$15 - $B$12) 
  This formula allocates the 400 (B14) plus a proportional share of the 200 (B12). 
  

Explanation: 

By subtracting $B$12 (200) from the total $B$15 (900), the denominator adjusts to 700. This ensures the formulas allocate the remaining 4500 proportionally between the 300 and 400 values, including their shares of the 200. 

Example: 

• Total to allocate: 4500 (D15) 
• Adjusted denominator: 900 - 200 = 700 
• X (D13): =300 * 4500 / 700 ≈ 1928.57 
• Y (D14): =400 * 4500 / 700 ≈ 2571.43 

This method efficiently distributes the entire amount with just two formulas. 

For further reference, you may find Microsoft’s guide on proportional distribution helpful: Calculate percentages - Microsoft Support 

If the solutions I have provided do not meet your expectations, in order for us to further assist you, could you please clarify:

1. What are the current formulas you are using in your Excel sheet?
2. What criteria are you using for the allocations?
3. Are there specific totals or values that need to be retained or adjusted in the new formulas?
4. Can you describe the logic behind the current three formulas, so we can effectively combine them?

If you have any more questions, feel free to ask! 

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".   

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.