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?
 
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.