Determining Area under the curve

[Gavalar]

Hi All

I'm trying to work out a way to determine the area under the curve. I initially thought there would be a function but have been unable to fine one. So my issue

I have 6 graphs that have undergone linear extrapolation for calulating crossing points as to when the results when out of range. I would now like to have excel calculate the area under the curve for the part of the curve that exceeds 1000 on the Y axis. I also have the values of the x-axis (column H in the work book) as well as the length in days between the 2 x-axis values (Column L) but because most AUC is determined through normal distribution and mean and SD values I can't find a suitable example.

I have attached my graphs (For visual understanding) as well as part of my workbook so hopefully a solution may be found or someone can at least point me in the right direction with this.

Many thanks in advance

Gav

[kvsrinivasamurthy]

Pl see attached file,Formula is in K17.
It gives area under curve for the range A14:B21.

[kvsrinivasamurthy]

In some charts some initial readings are missing.

[Gavalar]

I have looked through these and cannot see the ones you mean. I have at least 2 data points where Excel is incapable of giving me values as the line it is extrapolating is too obtuse.

Thanks for the formula I have a couple of points to question
- Why is there a multiplication factor of 0.5
- I only really want the AUC where values are >1000

I like the formula and have tried amending but my main issue is not understanding the multiplication factor.

Thanks again for your help

[MrShorty]

If I follow the solution correctly, he is simply employing the "trapezoidal rule" for numerical integration -- a fairly common numerical integration algorithm: http://en.wikipedia.org/wiki/Riemann_sum If you are unfamiliar with numerical integration, you might review the different riemann sums and how they work.

[Gavalar]

Thanks for this info making a little more sense but not being a mathematician I am I right in assuming that the AUC as determined by the formula described is the total area under Curve?

If so, am I also right in assuming to get what I require I will need to deduct from this, the area of the curve under 1000 (for the same graph) and therefore give me area of curve over 1000 or can this equation be modified to account for that?

Sorry as I say my maths is not that flash although I'm working my way through links you have provided.

Thanks again

[MrShorty]

In response to PM:

For a quick review of the geometry of trapezoids (the basis of our algorithm), I might suggest a suitable tutorial like: http://www.mathopenref.com/trapezoid.html or http://www.mathopenref.com/trapezoidarea.html

Calculating the area between y=1000 and y=curve should be as simple as subtracting off the area of the rectangle between y=1000 and y=0. If you need help remembering how to calculate the area of a rectangle, I would suggest putting "area of a rectangle" into your favorite search engine.

In the PM, you asked about units. If I understand correctly, you are sort of correct. If the units on the ferritin value are ug/L, then the composite unit for the area would be (ug/l)*day.