Calculating Area Under a Curve

davyj93

New Member
Joined
Apr 14, 2017
Messages
14
Hi guys,

I am trying to calculate the area under the curve on my Power-Time graph so that I can determine the energy.

Would anyone have an idea of how I can calculate the area under the curve? The solution would also have to ignore cells with #N/A. Would I also be able to choose a specific time range for this area?

Shown below is a very small sample of my data:

TimeEngine Power
(min)(kW)
0.000000#N/A
0.0003834.04
0.0010174.12
0.001033#N/A
0.001700#N/A
0.001717#N/A
0.002350#N/A
0.0023834.09
0.0030674.09
0.003083#N/A
0.003917#N/A
0.003933#N/A
0.0044334.13
0.004967#N/A
0.004983#N/A
0.0055174.06
0.005533#N/A
0.005933#N/A
0.005950#N/A
0.0064834.02
0.006967#N/A
0.006983#N/A
0.0074334.09
0.007450#N/A
0.0079674.05
0.0085004.09
0.008933#N/A
0.008950#N/A
0.009417#N/A
0.009433#N/A
0.0099174.05
0.009933#N/A
0.010550#N/A
0.010567#N/A
0.011250#N/A
0.011267#N/A
0.01#N/A
0.014.09
0.014.01

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>


Thanks,
David
 

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,604
Area under the curve is simply the sum of all the points. You just need to screen out the #N/A values. Right?

=SUMIF(E15:E20,"<>#N/A")
 

davyj93

New Member
Joined
Apr 14, 2017
Messages
14
The sum of which points? The values in the engine power column? That wouldn't take into consideration the time.

In your equation, which column does E refer to? Is this the engine power column?
 

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,604
I'm my many years of calculating Area Under the Curve (AUC), the time portion is irrelevant to the sum. ## AS LONG AS THE LENGTH OF TIME IS CONSISTENT ##

When comparing two AUC values, the Engine Power, in your case, is the column to sum. Now, I see a problem. The data set needs to be clean. In my opinion, the time values cannot be repeated, and, to compare apples and apples, you need to have the same number of data points to compare. So, a single point where the Engine Power is #N/A needs to be ignored on ALL sets.

I'm used to working with Fish Habitat values over time. It is the same principle though.

I believe you are missing a step. An Exceedence curve needs to be created. An Exceedence curve shows the percent of time the Engine Power Equals or is Exceeded over time. Create a table with 1% to 100%. Use the PERCENTILE formula on your data set to calculate the Engine Power at each percentile. The X Axis on the chart is the Reverse of 1% to 100%. Area Under the curve is calculated as the sum of 1% to 100% of each percentile value. Some people believe that there are different metrics that can be calculated from the curve. A median of the the whole curve is essentially the same as the sum. Summing the left or right side of the curve is a biased decision making process that either focuses on the most Engine Power or Least Engine Power.

Jeff
 

davyj93

New Member
Joined
Apr 14, 2017
Messages
14
My length of time in my data is not consistent though. The time intervals vary.

I tried using the percentile function but it didn't work. I think it was due to the presence of #N/A.

I know that you're telling me to sum the power columns and then compare them. This would be a comparison of total power across different time ranges. But I want to be able to see the values of the area under the curve. These values would be the energy.
 

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,604
My length of time in my data is not consistent though. The time intervals vary.
In my opinion, an identical number of points in your data set is crucial

I tried using the percentile function but it didn't work. I think it was due to the presence of #N/A.
You will have to remove those for the percentile function to work

I know that you're telling me to sum the power columns and then compare them. This would be a comparison of total power across different time ranges. But I want to be able to see the values of the area under the curve. These values would be the energy.
Area Under the Curve is a sum of the all the power values at even percentiles. What you're talking about is a histogram. Simply showing a chart of power across time. An XY chart will give you what you need. You'll still need to remove all the #N/A results.
 

Forum statistics

Threads
1,082,250
Messages
5,364,036
Members
400,774
Latest member
Goldi paul

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top