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.

ID	BS1	BS2	BS3	BS4	BS5	BS6	BS7	BS8	BS9	BS10	BS11	BS12	BS13	BS14	BS15	BS16	BS17	BS18	BS19	BS20
62160	-30.5	-34.5	-37.5	-33	-29.5	-27	-25.5	-25.5	-25.5	-26.5	-28	-30.5	-34.5	-42	-43.5	-39	-36	-34	-33	-32.5
62161	-29.5	-30	-31	-31	-30	-30	-30.5	-31.5	-34	-36	-33	-29	-26.5	-25	-24.5	-25.5	-27.5	-32	-39	-34.5
62162	-23.5	-23.5	-24	-25	-27	-29.5	-32	-34	-37.5	-39.5	-35.5	-30.5	-27	-24.5	-23.5	-23.5	-23.5	-24.5	-26.5	-30
62163	-32.5	-31	-30	-30	-30.5	-31.5	-33.5	-37	-45	-44.5	-38.5	-37	-37.5	-39	-40.5	-44	-48.5	-51.5	-48.5	-45
62164	-33.5	-30	-28	-27	-27.5	-28	-29.5	-30.5	-31.5	-31.5	-31	-30.5	-30	-28.5	-27.5	-27	-27.5	-28.5	-29.5	-30

<colgroup><col width="64" span="21" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[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