Forum Discussion
Sensitivity
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.
ok....the formula in C6 is because I need to ensure C9 is always 100%. So basically C4 and C5 are the only "levers", while C6:C8 are constant, however, I still need to be sure C9 is 100%. What to do?
Since C9 is always 100%, F9 is always $0.83, so nothing varies.
What do you want to measure? Changing C4 and C5 does not affect F9, so using +F9 as formula cell for the data table makes no sense.
You are asking all the right questions :-). No, F9 doesn't have to always be $.83 but C9 has to always be 100%. C column is just the % split of C9. F column is the $ equivalents.
F9 contains the formula =SUM(F4:F8).
This is equivalent to =SUM(C4:C8*$D$9).
This in turn is equivalent to =SUM(C4:C8)*$D$9.
Since SUM(C4:C8) is always 100%, this is the same as =$D$9.
So F9 will always return the value of D9, a cell with a fixed value.
As always, very good points. So if my only levers are C4 and C5, maintaining 100% in C9 and F9 just the result in USD of it all, how do you propose the setup should be? For example, if I change C4 to 42% and C5 to 16%, I would like F9 to show the true new $?