Macro to calculate multiple scenarios at once and display the results

[Evolution_GT]

Hello all,

I'm new to the forum and I was hoping I could get some assistance writing this particular macro. I have three sheets, first of which let's refer to as "sheet1", second will be called "List", and third will be called "Calculator".

The "calculator" sheet has several fields which can be manipulated to change the end result, using several formulas and references to other data tables that make it necessary to use scenarios.

"sheet1" contains raw data inputs in cells I3:I5 and K3:K5, which, depending on the type of calculation (will explain later), either I3:I5 OR K3:K5 will be used to populate cells G15:G17 in the "Calculator" sheet, for a given scenario. Let's call the first type "PV1" (uses I3:I5 from "sheet1" to populate G15:G17 in "calculator" sheet), and the second type "PV2" (uses K3:K5 from "sheet1" to populate G15:G17 in "calculator" sheet). The scenario type is indicated as a column heading in the "List" sheet, PV1 and PV2 are in cells "I6" and "J6", respectively. All scenarios require that both scenario types be calculated.

The "List" sheet contains the rest of the raw inputs for each scenario. The headings for these raw data inputs are on row 6, and extend from columns C-F, and column H. Based on the result from the "calculator" sheet (cell S22), this result will feed back into the "List" sheet. The result for scenario type "PV1" will go into column "I", and the result for scenario type "PV2" will go into column "J". As for the other raw data inputs for each scenario, for each respective column stated, they will feed into the following cells in the "calculator" sheet (first scenario begins in row 7):

Column C -> C8
Column D -> E8
Column E -> G8
Column F -> G10
Column H -> G12

Again, the first scenario row begins on row 7, and can potentially go on for at least hundreds of rows.

Also, in sheet "List", there is a trigger in column B (let's call the heading "Trigger"): if the cell in column B states "A", then the macro should ignore this scenario. If the cell in column B states "P", then the macro should calculate this scenario.

This macro I intend to attach to a button.

Any help that can be provided for this would be very, very much appreciated.

Thank you in advance for your time.

 

[Myconservator]

I am so dumb that while reading this description I lost control of reality.
Are you able to share the excel file?
I might be of some use if I can understand the problem.

 

[Evolution_GT]

I am so dumb that while reading this description I lost control of reality.
Are you able to share the excel file?
I might be of some use if I can understand the problem.

Please find link below, and thanks again for your help!

https://drive.google.com/file/d/0B2u2BLgJoahtX0V3bnlWLWx0TkE/view?usp=sharing

 

[Myconservator]

Please bear with my while I try to wrap my head around your request.
So, I have downloaded the file and have corrected all the year to 13, 15 etc instead of 103 115 etc.

Now you already have formula in the list sheet.
I don't understand what you want a Macro to do?

Is there any value in the formula that you want to change?

 

[Evolution_GT]

Please bear with my while I try to wrap my head around your request.
So, I have downloaded the file and have corrected all the year to 13, 15 etc instead of 103 115 etc.

Now you already have formula in the list sheet.
I don't understand what you want a Macro to do?

Is there any value in the formula that you want to change?

I should clarify - I've had to strip out a lot of the other sheets and functionality of the workbook as it is an internal tool to my firm. Originally, 103, 115, etc., would render the desired result (i.e. year 1999 would be "99"). That's why the worksheet "calculator" merely multiplies the values to get the result in S22, which really makes no sense in the end. The objective is to get the macro to feed numbers into the calculator, the calculator produce a number in cell S22, and feed that number back into the sheet "List".

As for the formula in the list sheet, this formula applies only to scenarios with the Frequency Type, "A". I would imagine, should the macro work for all scenarios with freq type "P", the answer from S22 in "calculator" sheet would replace the cell where the formula once was in sheet "List". Therefore, you can ignore the formula that is already there.

To be clear, I want the macro to take the data from the aforementioned columns in sheets "sheet1" and "List", and input them into the aforementioned desired cells in sheet "Calculator". I would imagine, should the macro work, once each scenario is run, the "Calculator" sheet would produce a result in cell S22, which would then feed back into columns "I" and "J" in sheet "List". I would also imagine there be some sort of loop functionality as I would like the macro to calculate for all scenarios at once.

I hope this clears things up and thank you very much again for your time.

 

[Myconservator]

Under the year it says "YY" which led me to believe that there was a data entry error.

Question 1:
Now in the list column, will there be more than one entry?
Because it is linked to the Sheet1 and every time data in sheet 1 is changed List column will also change.
Unless data is copy pasted (as values) every time data entry takes place in sheet.

Now if I were to assume that, List sheet will have data and calculate PV is pressed.

You want the macro to go through all the rows that have data.
Copy CEG column (row 8 in given example) information from List to Calculator
Column F gives the Payment interval.

Question 2 & 3:
Where do I get "Until age (200 if until mortality)"?
Where do I get discount rates or are they constants?

 

[Evolution_GT]

Under the year it says "YY" which led me to believe that there was a data entry error.

Question 1:
Now in the list column, will there be more than one entry?
Because it is linked to the Sheet1 and every time data in sheet 1 is changed List column will also change.
Unless data is copy pasted (as values) every time data entry takes place in sheet.

Now if I were to assume that, List sheet will have data and calculate PV is pressed.

You want the macro to go through all the rows that have data.
Copy CEG column (row 8 in given example) information from List to Calculator
Column F gives the Payment interval.

Question 2 & 3:
Where do I get "Until age (200 if until mortality)"?
Where do I get discount rates or are they constants?

Q1: For the sheet "List", yes, please assume multiple entries will exist at any given time (each entry/scenario is a new row). Sheet "Sheet1" should only be linked to sheet "List" by way of the formula that exists in columns "I" and "J", which again, should be disregarded for purposes of this macro. The percentages entered in columns "I" and "K" in "Sheet1" will stay constant at all times.

To clarify - the columns which are copied from "List" to "Calculator" are columns C, D, E, F, and H. The cells indicated in row 5 of sheet "List" tell you where each respective column of data in sheet "List" are to go. As an example, data in column "C" of sheet "List" will feed into cell C8 of sheet "Calculator". Therefore, to answer Q2, "Until age...", this information, for each scenario, comes from column H of sheet "List".

Q3: The discount rates come from sheet "Sheet1". Each scenario has to be calculated twice. For the example in sheet "List", row 10, both columns "I" and "J" need to be populated by the macro. Column "I" will be calculated using the discount rates from I3:I5 in sheet "Sheet1". Column "J" will be calculated using the discount rates from K3:K5 in sheet "Sheet1".


I hope this clears things up further and please let me know if you have any further questions. I greatly appreciate your time. Thank you!

 

[Myconservator]

What is the formula for PV?

 

[Evolution_GT]

What is the formula for PV?

You don't need to worry about the formula - the calculator sheet will calculate that for each scenario.

 

[Myconservator]

In that case where on calculator sheet can I find Normal PV and CareMGMT PV?