Speed up Excel spread sheet by taking calculations off the face of the sheet
- or -
Post a project like this2883
$$
- Posted:
- Proposals: 9
- Remote
- #1161047
- Awarded
Description
Experience Level: Intermediate
General information for the business: Education
Kind of development: Customization of existing program
Description of requirements/functionality: The problem
The Spread sheet is running too slow - I believe this is because the calculation carried out in cols AH and AI are being carried out on the face of the SS - if these were written in VBA I think this may help speed the process up ? But I am not sure
Spread sheet workings explanation
This is a simple Monte Carlo simulation spreadsheet. The data in column AD is Supplied by the user. Then
Set the number of samples to be sampled in B1
Set the number of iterations/runs in B2
Press the Red Run macro button
Column AF will populate with a randoml selection of the number of samples selected in B1 (ie 100 at the moment)
The formulas in Cols AH and AI will automaticallt recalculate.
This loop will be repeated for the number of iteration selected in B2 (ie 150 at the moment)
For each iteration The total of col AH and the highest single negative figure appearing in col AI will be added to the cols AM and AN
So at the end of the run cols AM and AN will include 1 row of data for each of the number of iterations selected in B2 (150 at the moment)
The SS really should be running maybe 10,000 iterations which at the moment would take about 15 minutes to complete I need to improve this to just a few minutes.
Specific technologies required: Excel
OS requirements: Windows
Extra notes:
Kind of development: Customization of existing program
Description of requirements/functionality: The problem
The Spread sheet is running too slow - I believe this is because the calculation carried out in cols AH and AI are being carried out on the face of the SS - if these were written in VBA I think this may help speed the process up ? But I am not sure
Spread sheet workings explanation
This is a simple Monte Carlo simulation spreadsheet. The data in column AD is Supplied by the user. Then
Set the number of samples to be sampled in B1
Set the number of iterations/runs in B2
Press the Red Run macro button
Column AF will populate with a randoml selection of the number of samples selected in B1 (ie 100 at the moment)
The formulas in Cols AH and AI will automaticallt recalculate.
This loop will be repeated for the number of iteration selected in B2 (ie 150 at the moment)
For each iteration The total of col AH and the highest single negative figure appearing in col AI will be added to the cols AM and AN
So at the end of the run cols AM and AN will include 1 row of data for each of the number of iterations selected in B2 (150 at the moment)
The SS really should be running maybe 10,000 iterations which at the moment would take about 15 minutes to complete I need to improve this to just a few minutes.
Specific technologies required: Excel
OS requirements: Windows
Extra notes:
Paul D.
100% (87)Projects Completed
17
Freelancers worked with
16
Projects awarded
48%
Last project
31 Dec 2023
United Kingdom
New Proposal
Login to your account and send a proposal now to get this project.
Log inClarification Board Ask a Question
-
There are no clarification messages.
We collect cookies to enable the proper functioning and security of our website, and to enhance your experience. By clicking on 'Accept All Cookies', you consent to the use of these cookies. You can change your 'Cookies Settings' at any time. For more information, please read ourCookie Policy
Cookie Settings
Accept All Cookies