Using VBA arrays to calculate cumulative average

qefsadkfpok

New Member
Joined
Feb 9, 2021
Messages
1
Office Version
  1. 2016
Platform
  1. Windows
*VBA newbie alert!*

Hi,

Have tried searching but I can't find an answer so thought I would give posting a go.

I have a model in a workbook which carries out a series of calculations on a population across a number of years. The calculations are carried out by formulas and are the same every year.

At the moment I use a simple VBA macro to push the data through from one year to the next (only rows which are eligible are pushed from one year to the next). At the end of the simulation, it works out averages across the period. It runs in a loop so I can do a Monte Carlo type simulation, but at the moment it just spits the results into a summary sheet, copies the summary sheet and then starts the loop again.

The code is something like:

Run X times:

Calculate formulas in year 1; push eligible rows to year 2;
Calculate formulas in year 2; push eligible rows to year 3;
etc.
After year 10, aggregate the results into a summary sheet;
Copy the summary sheet into a new workbook;
Start loop again

What I would like to do is use another macro to store the details of the summary sheet so that a "summary of summaries" can be built across several runs of the model (rather than at the moment where I get X number of summaries and have to aggregate them manually), but I can't figure out where to start. Any help or suggestions would be greatly appreciated!

Thanks
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Watch MrExcel Video

Forum statistics

Threads
1,129,491
Messages
5,636,633
Members
416,932
Latest member
mm07

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top