Calculating % Cap

[Gian624]

I'm embarrassed to ask this question as it should be something I could figure out on my own but here goes:

I need to figure out how to calculate a % cap without creating a circular reference. Here is a simple illustration I need to find a formula for to automate my work:
1.) I know that 1 + 1 + 1 + 1 = 4
2.) I know that 2 + 1 + 1 + 2 = 6
3.) However, if the last 2 can only be 25% of the total, that would mean the 2 would need to be ~1.3 and the total would be ~5.3 instead of 6.

I can accomplish this with a Goal Seek but like I said, I'm embarrassed I can't figure out a way to "automate" a formula to % cap it.

Thanks in advance for your help.

 

[Joe4]

If I understand you correctly, the only I can think to "automate" it without a circular reference or using Goal Seek is to use VBA.
Is that a viable solution?

If so, please provide specific details on the structure of your data (maybe post an image so we can see how you have it all laid out)?