VBA code to find the area between two curves plotted in excel by exact method

I have two curves as shown in the attached file. The first one is a two-point straight line. The second is a series of straight lines. The missing values in the second one to be interpolated.

I need the total area between the two lines, the areas above and below to be returned separately. The area is also bounded by vertical lines through the end points of the first line.

I had asked this question earlier and the expert provided an approximate numerical method which takes a long time if the precision is increased.


I have provided a file showing the calculation steps which I am looking for. Can someone provide VBA code for the same?
LVL 43
Saqib Husain, SyedEngineerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Basically you want the Green part ? and if yes how do you want that ? not clear.
Oops !! just noticed you had 2 worksheets in this WB was looking at the first one. Just opened Calculation requirements and see all the explanations.

Will study all this to see if I can understand something !!! :)

will revert.
Ejgil HedegaardCommented:
Try this exact calculation.
When all points exist, the areas are rectangles and triangles.
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

Saqib Husain, SyedEngineerAuthor Commented:
Hi, thanks for the effort. But as my given file says,

Inputs are all yellow. White cells are  formulas and need to be calculated in VBA - not displayed in the spreadsheet.

I do not want to modify the source data.
Ejgil HedegaardCommented:
Strange how a sentence can be understood.
I read it as no formulas in cells, only the values from the VBA calculation, not that nothing should be displayed.
But here it is, where all is done in VBA, and the original data is left as is.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Saqib Husain, SyedEngineerAuthor Commented:
Looks goood but is failing for line data

X            Y
1000      225
1344      226
Ejgil HedegaardCommented:
Boundary checks added
Saqib Husain, SyedEngineerAuthor Commented:
Hi, did not get chance to check the last post as I had done my own modification and got it to work. I also had to modify it (not part of the question) to stop at the ends of the curve 2 if curve 1 extended beyond.

Anyways, thanks for the effort. The basic thing is working just as I wished.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.