Quote:
Originally Posted by gfy532
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!
|
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.
Similar Threads
Hybrid Mode
