Forum Discussion
Sensitivity
I have a question regarding the table in cells J4:N8. The table somehow does not change the numbers even when column I % is different and row 3 also. Please can you tell me what I am doing wrong in this context? Thank you. HansVogelaar​
I'm sorry I keep repeating the same thing over and over again. You have fixed the total amount in F9 to be equal to the fixed value of D9, so varying C4 and C5 does not change the total amount in F9.
Put another way: since the amounts in F4:F8 vary exactly the same as the percentages in C4:C8 (since F4=C4*$D$9 etc.), you keep the total fixed.
If you drop the requirement that C4:C8 sum to 100%, you could create a working data table.
In the screenshot below, I replaced the formula in C6 with the value 9%. If you then vary C4 and C5, the sum won't be 100% anymore, and the total amount won't be $0.83 anymore.
11 Replies
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
No sensitive data on here :-). Thanks for looking....
Thank you!
The cause of the problem is that C6 contains a formula that ensures that C9 will always be 100%, regardless of the values of C4 and C5. Hence the sum in F9 is always $0.83, regardless of the values of C4 and C5.
So the data table is in fact correct - the value does not vary!
It is now. Even with this change, the data isnt updating? Also, when I go to Data> What if> etc....I get a message "cannot change part of the formula"
First please check if you are in Automatic calculation mode
