Summing a dynamic range to match a precise given total.

[jahnosha]

A3 is where I input a decimal number which changes quarterly
A2 is a factor which I also input quarterly
B3 is (A2*A3)/12

This goes on for A3>A6 and B3 through B6
I do this because I do not know B3 through b6 as individual values but I am given their sum in B7 which I can enter as well but right now I use goal seek to make it match what I have been given.

Typically I use goal seek to make sum(B3:B6) match B7.

Is there any formula to forgo using goal seek knowing that I have to do this alot? as in, modify B3,B4,B5,B6 so that whenever I input a new A2 through A6 it will automatically adjust to fit B7?

 

[energman58]

Sorry, you will need to be clearer regarding what values are fixed and what do you change to get the total to the known value for B7? Can you put out an example of what you are trying to do.

From what I understand the A2 factor is a red herring - if you divide B7 by A2 you will get the total of A3-A6 and there are an infinite number of combinations of 4 decimal numbers that allow you to match that so it sounds like you are just using Goal Seek to allocate a total of (B7/A2) between the four cells given a fixed value in one of them - there isnt a single correct answer - there are infinite numbers of combinations and permutations that will do it and Goal Seek is just spitting out the first one it finds.

To illustrate if b7 divided by A2 is, say, 7 and A3 is 2 that leaves a remainder of 5 to be allocated between A4, A5 and A6 which could be done 5,0,0 or 4,1,0 or 4,0,1 or 1,0,4 or 0,1,4 or 1,1,3 or 1,2,2 or 3,2,0 etc; even if only integers are allowed there are 21 possible ways of doing this.
Or am I missing something?

 

[jahnosha]

A B
1 9084
2 1.2 908
3 2.3 1741
4 2649

A1:A3 is input by me, B4 is B2+B3 but I know B2+B3 to be a certain number, I don't know what they are individually until I do the math:

B2:B3 right now is (A2*A1)/12

Sometimes I have to use goal seek B4 to match 2649 by changing A2 or A3
Is there a formula that can automatically find B2:B3 knowing it has to add up to B4

 

[energman58]

Err sorry not clear what you are doing here:

In your example A2*A1/12 = 908.4 not 908

So is what you are doing dealing with the fact that this rounding is throwing an error and therefore you need to tweak the values in A2 and A3 to get the sum to match the number you first thought of (B4)?

Is there a real world application of this that would make it clearer?

 

[jahnosha]

Can I email the sheet to you? I don't know how to post a table in this forum.