Calculating Area Under a Curve

[davyj93]

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:

[TABLE="class: grid, width: 307"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD="align: center"]Time[/TD]
[TD="align: center"]Engine Power[/TD]
[/TR]
[TR]
[TD="align: center"](min)[/TD]
[TD="align: center"](kW)[/TD]
[/TR]
[TR]
[TD="align: center"]0.000000[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]0.000383[/TD]
[TD="align: center"]4.04[/TD]
[/TR]
[TR]
[TD="align: center"]0.001017[/TD]
[TD="align: center"]4.12[/TD]
[/TR]
[TR]
[TD="align: center"]0.001033[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]0.001700[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]0.001717[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]0.002350[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]0.002383[/TD]
[TD="align: center"]4.09[/TD]
[/TR]
[TR]
[TD="align: center"]0.003067[/TD]
[TD="align: center"]4.09[/TD]
[/TR]
[TR]
[TD="align: center"]0.003083[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]0.003917[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]0.003933[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]0.004433[/TD]
[TD="align: center"]4.13[/TD]
[/TR]
[TR]
[TD="align: center"]0.004967[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]0.004983[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]0.005517[/TD]
[TD="align: center"]4.06[/TD]
[/TR]
[TR]
[TD="align: center"]0.005533[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]0.005933[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]0.005950[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]0.006483[/TD]
[TD="align: center"]4.02[/TD]
[/TR]
[TR]
[TD="align: center"]0.006967[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]0.006983[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]0.007433[/TD]
[TD="align: center"]4.09[/TD]
[/TR]
[TR]
[TD="align: center"]0.007450[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]0.007967[/TD]
[TD="align: center"]4.05[/TD]
[/TR]
[TR]
[TD="align: center"]0.008500[/TD]
[TD="align: center"]4.09[/TD]
[/TR]
[TR]
[TD="align: center"]0.008933[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]0.008950[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]0.009417[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]0.009433[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]0.009917[/TD]
[TD="align: center"]4.05[/TD]
[/TR]
[TR]
[TD="align: center"]0.009933[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]0.010550[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]0.010567[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]0.011250[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]0.011267[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]0.01[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]0.01[/TD]
[TD="align: center"]4.09[/TD]
[/TR]
[TR]
[TD="align: center"]0.01[/TD]
[TD="align: center"]4.01[/TD]
[/TR]
</tbody>[/TABLE]


Thanks,
David

 

[Jeffrey Mahoney]

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]

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]

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]

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]

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.