calculate area from a non-normal distribution

[Torito]

Hi,

I'd like to calculate the area of a non-normal distribution set of numbers.

The set of numbers are in rows in series of 20 records.

Each serie can have a different distribution, so some might be close to Normal while others won't be

Ideally i need a function that can approx for any series so i can drag down and add the Area result in a new field

The original data has thousands of series.
[TABLE="width: 1344"]
<colgroup><col width="64" span="21" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]ID[/TD]
[TD="width: 64"]BS1[/TD]
[TD="width: 64"]BS2[/TD]
[TD="width: 64"]BS3[/TD]
[TD="width: 64"]BS4[/TD]
[TD="width: 64"]BS5[/TD]
[TD="width: 64"]BS6[/TD]
[TD="width: 64"]BS7[/TD]
[TD="width: 64"]BS8[/TD]
[TD="width: 64"]BS9[/TD]
[TD="width: 64"]BS10[/TD]
[TD="width: 64"]BS11[/TD]
[TD="width: 64"]BS12[/TD]
[TD="width: 64"]BS13[/TD]
[TD="width: 64"]BS14[/TD]
[TD="width: 64"]BS15[/TD]
[TD="width: 64"]BS16[/TD]
[TD="width: 64"]BS17[/TD]
[TD="width: 64"]BS18[/TD]
[TD="width: 64"]BS19[/TD]
[TD="width: 64"]BS20[/TD]
[/TR]
[TR]
[TD="align: right"]62160[/TD]
[TD="align: right"]-30.5[/TD]
[TD="align: right"]-34.5[/TD]
[TD="align: right"]-37.5[/TD]
[TD="align: right"]-33[/TD]
[TD="align: right"]-29.5[/TD]
[TD="align: right"]-27[/TD]
[TD="align: right"]-25.5[/TD]
[TD="align: right"]-25.5[/TD]
[TD="align: right"]-25.5[/TD]
[TD="align: right"]-26.5[/TD]
[TD="align: right"]-28[/TD]
[TD="align: right"]-30.5[/TD]
[TD="align: right"]-34.5[/TD]
[TD="align: right"]-42[/TD]
[TD="align: right"]-43.5[/TD]
[TD="align: right"]-39[/TD]
[TD="align: right"]-36[/TD]
[TD="align: right"]-34[/TD]
[TD="align: right"]-33[/TD]
[TD="align: right"]-32.5[/TD]
[/TR]
[TR]
[TD="align: right"]62161[/TD]
[TD="align: right"]-29.5[/TD]
[TD="align: right"]-30[/TD]
[TD="align: right"]-31[/TD]
[TD="align: right"]-31[/TD]
[TD="align: right"]-30[/TD]
[TD="align: right"]-30[/TD]
[TD="align: right"]-30.5[/TD]
[TD="align: right"]-31.5[/TD]
[TD="align: right"]-34[/TD]
[TD="align: right"]-36[/TD]
[TD="align: right"]-33[/TD]
[TD="align: right"]-29[/TD]
[TD="align: right"]-26.5[/TD]
[TD="align: right"]-25[/TD]
[TD="align: right"]-24.5[/TD]
[TD="align: right"]-25.5[/TD]
[TD="align: right"]-27.5[/TD]
[TD="align: right"]-32[/TD]
[TD="align: right"]-39[/TD]
[TD="align: right"]-34.5[/TD]
[/TR]
[TR]
[TD="align: right"]62162[/TD]
[TD="align: right"]-23.5[/TD]
[TD="align: right"]-23.5[/TD]
[TD="align: right"]-24[/TD]
[TD="align: right"]-25[/TD]
[TD="align: right"]-27[/TD]
[TD="align: right"]-29.5[/TD]
[TD="align: right"]-32[/TD]
[TD="align: right"]-34[/TD]
[TD="align: right"]-37.5[/TD]
[TD="align: right"]-39.5[/TD]
[TD="align: right"]-35.5[/TD]
[TD="align: right"]-30.5[/TD]
[TD="align: right"]-27[/TD]
[TD="align: right"]-24.5[/TD]
[TD="align: right"]-23.5[/TD]
[TD="align: right"]-23.5[/TD]
[TD="align: right"]-23.5[/TD]
[TD="align: right"]-24.5[/TD]
[TD="align: right"]-26.5[/TD]
[TD="align: right"]-30[/TD]
[/TR]
[TR]
[TD="align: right"]62163[/TD]
[TD="align: right"]-32.5[/TD]
[TD="align: right"]-31[/TD]
[TD="align: right"]-30[/TD]
[TD="align: right"]-30[/TD]
[TD="align: right"]-30.5[/TD]
[TD="align: right"]-31.5[/TD]
[TD="align: right"]-33.5[/TD]
[TD="align: right"]-37[/TD]
[TD="align: right"]-45[/TD]
[TD="align: right"]-44.5[/TD]
[TD="align: right"]-38.5[/TD]
[TD="align: right"]-37[/TD]
[TD="align: right"]-37.5[/TD]
[TD="align: right"]-39[/TD]
[TD="align: right"]-40.5[/TD]
[TD="align: right"]-44[/TD]
[TD="align: right"]-48.5[/TD]
[TD="align: right"]-51.5[/TD]
[TD="align: right"]-48.5[/TD]
[TD="align: right"]-45[/TD]
[/TR]
[TR]
[TD="align: right"]62164[/TD]
[TD="align: right"]-33.5[/TD]
[TD="align: right"]-30[/TD]
[TD="align: right"]-28[/TD]
[TD="align: right"]-27[/TD]
[TD="align: right"]-27.5[/TD]
[TD="align: right"]-28[/TD]
[TD="align: right"]-29.5[/TD]
[TD="align: right"]-30.5[/TD]
[TD="align: right"]-31.5[/TD]
[TD="align: right"]-31.5[/TD]
[TD="align: right"]-31[/TD]
[TD="align: right"]-30.5[/TD]
[TD="align: right"]-30[/TD]
[TD="align: right"]-28.5[/TD]
[TD="align: right"]-27.5[/TD]
[TD="align: right"]-27[/TD]
[TD="align: right"]-27.5[/TD]
[TD="align: right"]-28.5[/TD]
[TD="align: right"]-29.5[/TD]
[TD="align: right"]-30[/TD]
[/TR]
</tbody>[/TABLE]

 

[Asala42]

Well if you look at each point as a "1 unit" wide, and since you did use the word "approximate", you could start by adding the absolute values of each records data points.

For example
{=SUM(ABS(B2:U2))}

Basically like summing the area of a bar chart. If you want to mimic the area under a surface area chart, you could adjust further with:
{=SUM(ABS(B2:U2))+0.5*SUM(ABS(C2:U2-B2:T2))}

Where you are adding in some area representing the various individual increases/decreases from point to point.

 

[Torito]

Well if you look at each point as a "1 unit" wide, and since you did use the word "approximate", you could start by adding the absolute values of each records data points.

For example
{=SUM(ABS(B2:U2))}

Basically like summing the area of a bar chart. If you want to mimic the area under a surface area chart, you could adjust further with:
{=SUM(ABS(B2:U2))+0.5*SUM(ABS(C2:U2-B2:T2))}

Where you are adding in some area representing the various individual increases/decreases from point to point.

I understand the rationale and should work fine for what i am trying to acheive. However, the formulaes are giving me an error. I fixed the first one by using
{=ABS(SUM(B2:U2))}
But i can't fix the second one. ( I know my level is very basic!).

 

[Asala42]

Worked for me. Are you hitting Ctrl+Alt+Enter to get the brackets? These were meant be written as array formulas

Also {=ABS(SUM(B2:U2))} and {=SUM(ABS(B2:U2))} will become very different formulas if you can have both negative and positive values in your range.

 

[Torito]

Ctrl+Shift+Enter

Thanks