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

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.
Area-between-two-curves.xlsx
LVL 43
Saqib Husain, SyedEngineerAsked:
Who is Participating?

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

x
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.

Ejgil HedegaardCommented:
Try this, using numerical integration.
The step width is set to 0.001, which is a compromise between accuracy and calculation time.
The yellow areas are named ranges, used in the program.
Area-between-two-curves.xlsm
0
Saqib Husain, SyedEngineerAuthor Commented:
Hi, thanks for the effort. But I see that you did not consider the last sentence of the question.

The attached file shows how I would have calculated it manually.
Area-between-two-curves.xlsm
0
Ejgil HedegaardCommented:
I did consider, but read it the other way, expanding the line to the entire range.
Easy to see that is wrong, when you point me in the right direction :)
A small modification restricts the calculation to the line range.
To have the non filled cells in the range, I revised the file I send.
Area-between-two-curves-A.xlsm
0

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:
Hi again, and thanks.

It does give the correct answer (havent tested it much). But it is very slow. I do not want to reduce the precision. I shall ask another question where the macro would be based on the calculations I have shown in the follow-up file. You might like to give it a go.
0
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.