Excel Curves- Area under the curve

Is it neccessary to calculate area under the functions?

Is enough to compare maximum of the function and Sigma?
They must be somehow corelatred to determine area under curve and if you already have those numbers you can compare them.

Dear Zbor.

Thanks for your reply.

Two compare two curves I need same area. This can tell me quatitative difference in maxima peak.

If ur a chemist, then there is another explanation: To compare two chromotagraph, it is necessary to have same chemical concentration, bbut practically it is not possible, so area normalization is required. Which is normally supplied with chromography softwrare, but my software is bit old. Hence I need to do this manually.

I know x value where y reaches maximum. What is mean by sigma? I have X and Y coordinate value. Do u mean summation? How to caculate? Plz help.

Hi Shg,

I have attached my excel sheet in it. There are two curves, Blue and Red. Blue is of lesser area. I need relative ratio of areas. (Area of Blue / Area of Red).

Please help me out.

Saravanan

How about this?

OverLay(1).xlsx

I put all values as positive, but since they are small, it doesn't affect result (I guess)

Hello zbor,

Thats Fantastic!!! Thank you very much.

I am just trying to understand the mechanism of this max function.


For Example:

Column A
2
3
4
7
9
78

If we use max function for above data. we will get 78. How you have used this to calculate area?

This is situation: http://people.sc.fsu.edu/~burkardt/l...rectangles.png

When you aproximating surface under (any) curve you have (y1+y2)/2 (that's average on y) * (x2-x1) (that's difference on x axis).

Now you summ all of this parts and you get total area.

For this issue, since you have x axis from 800 to 1600... and you have 2400 results, that means x step is 2400/(1600-800)=3 (that's 3 in formula)

You can multiply all this parts: 3*(ABS(C4)+ABS(C3))/2

but I add to this summ of previous, so my formula calculate all sum, and result is last number (wich is biggest so it's MAX).
But you could for this formula make SUM (without D2 in formula =D2+3*(ABS(C4)+ABS(C3))/2)

Edit_ this won't work for formulas that intercepting X axis.

Dear Zbor,

Thats Amazing.

Your a mathematical magician.

Thanks a lot, a lot, a lot......................................


-Saravanan

You get 71,6%, I get 65,6%.

The difference can be, as I mention by side, that I change all numbers into positive. As I see, you take positive and negative.
What happens is that curve flow around X axis and + and - in your case compend, and in my case add to each curve. But since it's happening at the first part of the curve where values are negligible compared to the peak it doesn't affect result too much.

I don't know wich of those approaches is better (or what approach has beter physical anchorage).. Difference is only 5%. He can take either any of it, either to test few times result and see wich give better approximation (or put known values and compare results).

Just to clearify problem once more time. If you have y1=A*sin(x) function compared to y2=1 function: your area would be 0 (because negative and positive part of sin function are the same) and compared to 1 is 0. Mine area would be A/SQROOT(2), compared to 1 gives A/1,41 (because I consider both part of sin function as positive). But since Gaussian curve is not symmetrical arround X axe we get positive values for both approachs and now he need to see what's give him more precise result.

can any one able to explain this function
SUM(INDEX(rgn, 0, COLUMN() ) )

What it does actually?

Thanks for your reply. This function used to sum up from required part of the curve. Am I right?

As alternative
I am intrested from X=900 to X = 1600. After calculating the area (y2+y1)/2*(X2-x1))for all data points. Why cannot we sum up all small little areas using sum() function?(only from 900-1600). I have converted all values to positive.