rarpro
11-10-2010, 05:38 AM
Dear all,
I need help with finding a way to estimate the area under a curve (from data (X,Y). The idea is very simple and I was able to do it on the worksheet (no VBA), but I need it in VBA because the number of observations are not always the same...ok :
I have data X and Y (columns) that are stratified by regions and by observation number like this:
REG OBSN X Y
1 1 0.1 -4
1 2 0.3 -2
1 3 0.5 0
1 4 0.65 2
1 5 0.8 4
: : : :
2 1 0.3 -4
2 2 0.5 -2
2 3 0.8 0
2 4 0.9 2
2 5 0.99 4
So:
- there could be more than 2 regions
-The number of OBS would be the same for each region
- The variable Y will have the same values for each region block (from negative to positive)
Then, think of X and Y as a chart: X=X-axe and Y=Y-axe Then I want to estimate the area under the curve (i.e. the integral).
But I want to do it in two parts: the area under the curve when Y>0, and then when Y<=0 so then I can compare those two values [This is done for each region].
Given this, I found that I can estimate the area of a curve (the integral) by doing this in an excel cell:
=SUMPRODUCT((OFFSET(Xpts, 1, 0, pnls) - OFFSET(Xpts, 0, 0, pnls)), (OFFSET(Ypts, 1, 0, pnls) + OFFSET(Ypts, 0, 0, pnls))) * 0.5
Where Xpts is the name for the range of X values, Ypts the range for Y values, pnls is the number of (regions -1).
This works good for all the data or doing manually, but I need something that can accommodate different number of regions (and obs) automatically.
I can't get a right way of setting range names according criteria (e.g. for Xpts cells when Y>0 or Y<=0). if I could do this, then I can have a place on the workbook to put the value from the equation above for all the cases (regions and when Y positive and Y negative) and then read it back to the VBA program....Hopefully this is not too confusing, and somebody could put me on the right track.
Thanks!!
I need help with finding a way to estimate the area under a curve (from data (X,Y). The idea is very simple and I was able to do it on the worksheet (no VBA), but I need it in VBA because the number of observations are not always the same...ok :
I have data X and Y (columns) that are stratified by regions and by observation number like this:
REG OBSN X Y
1 1 0.1 -4
1 2 0.3 -2
1 3 0.5 0
1 4 0.65 2
1 5 0.8 4
: : : :
2 1 0.3 -4
2 2 0.5 -2
2 3 0.8 0
2 4 0.9 2
2 5 0.99 4
So:
- there could be more than 2 regions
-The number of OBS would be the same for each region
- The variable Y will have the same values for each region block (from negative to positive)
Then, think of X and Y as a chart: X=X-axe and Y=Y-axe Then I want to estimate the area under the curve (i.e. the integral).
But I want to do it in two parts: the area under the curve when Y>0, and then when Y<=0 so then I can compare those two values [This is done for each region].
Given this, I found that I can estimate the area of a curve (the integral) by doing this in an excel cell:
=SUMPRODUCT((OFFSET(Xpts, 1, 0, pnls) - OFFSET(Xpts, 0, 0, pnls)), (OFFSET(Ypts, 1, 0, pnls) + OFFSET(Ypts, 0, 0, pnls))) * 0.5
Where Xpts is the name for the range of X values, Ypts the range for Y values, pnls is the number of (regions -1).
This works good for all the data or doing manually, but I need something that can accommodate different number of regions (and obs) automatically.
I can't get a right way of setting range names according criteria (e.g. for Xpts cells when Y>0 or Y<=0). if I could do this, then I can have a place on the workbook to put the value from the equation above for all the cases (regions and when Y positive and Y negative) and then read it back to the VBA program....Hopefully this is not too confusing, and somebody could put me on the right track.
Thanks!!