Auto adjustment of functions
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! |
Auto adjustment of functions
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 |
Quote:
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. |
All times are GMT +1. The time now is 08:59 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com