Is there any way for formulas to self-adjust? I'm doing a worksheet for savings. IE: Item A gets 10%, Item B gets 40%, Item C gets 15%, Item D gets 15% Item E gets 30%. If $100 is saved so far: Item A is up to $10, B is $40, C is $15, D is $15 And E is $30. If item B's goal is $15 with the rest still needin to accrue, is there any way to automatically have the formula stop contributing to Item B and split the rest to the other items? I know this would be fairly complex.

Thanks for any help!

On Wednesday, February 29, 2012 8:30:08 AM UTC-6, gfy532 wrote:
Is there any way for formulas to self-adjust? I'm doing a worksheet for
savings. IE: Item A gets 10%, Item B gets 40%, Item C gets 15%, Item D
gets 15% Item E gets 30%. If $100 is saved so far: Item A is up to $10,
B is $40, C is $15, D is $15 And E is $30. If item B's goal is $15 with
the rest still needin to accrue, is there any way to automatically have
the formula stop contributing to Item B and split the rest to the other
items? I know this would be fairly complex.

Thanks for any help!

At least show us your formula or send a file to dguillett1 @gmail.com





--
gfy532

Hello,

well the first problem is that you distributed percentage all wrong. Your sum gives back 110 and not 100 :))) it took me quite a while to figure it out.

I can give you this formulas but they are not ideal.

in a case where you have in a column A your names B,A,C,D,E (starting with the row number 2) and in column C you percentage. Cell B1 is the amount saved.
so in the cell B2 you would put, +IF($B$1*C2=15;15;$B$1*C2) this reffers to name B. And then in cell B3 you would put this and then drag it down to other cells:
+IF($B$1*$C$2<$B$2;($B$1-$B$2)*(C3+(1-SUM($C$3:$C$6))*(C3/SUM($C$3:$C$6)));$B$1*C3)

i dont know if this helps you, is not ideal formula cuz it assumes that you are always looking to stop contributing to the item B.