Hello everyone, this is my first post so hopefully I do a good enough job describing my issue. See my attached workbook to better understand my question. In this workbook I have the template and two examples of the results I would be looking to be able to have automatically calculated.
I am wanting to have the "Final" column blue cells set to reference "Investment A" cells for each "Month" up until the month of change (D2) which I want to be able to adjust at any time to get different results. On the month that equals (D2) I want the "Final" column blue cells to start referencing "Investment B".
So far this is simple, I run into problems with the fact that I always want the point where the reference changes to begin the "Investment B" reference from the first month. For example if I select the change to happen at month 5 I want month 5 to reference "Investment B" "Month" 1 and then the remaining final cells to reference the following Investment B rows.
To take it one step further I would like the "Investment B" reference to actually be affected by another conditional reference shown in column "Investment B+A at time of switch". I want this to add "Investment B"s starting value with whatever the value of "Investment A" is one month before the switch. This will then impact the values for the cells I would like to reference after the switch.
So in summary I want to be able to select any month for a switch to occur in referencing two different columns. I want the post switch reference to always begin at the first cell of the new reference rather than the corresponding cell in the column. I would also like the reference, post switch, to include the value of the reference one row before the switch into its calculation. I want to be able to change only the yellow cells to get different results and I need help with formulas/functions for all of the blue cells.
I have really been struggling to describe what I am going for so hopefully there is some excel wizard on here that can translate all this for me. If not please ask questions.
Thanks All!
Bookmarks