Need a Formula to Calculate the Area -Simpson Rule

[vmohan1978]

I have a list of number for example 10Numbers
P0 60
P1 65
P2 70
P3 75
P4 80
P5 50
P6 40
P7 41
P8 43
P9 50
P10 85


i NEED A FORMULA
=P0+P10+2(P2+P4+P6+P8)+4(P1+P3+P5+P7+P9)

INSTED OF PICKING EVERYTIME.
SOMETIMES UTSTED OF 10 NMBERS IT WILL BE 20+ OR 15 OR +50 ALSO

 

[Wigi]

Hello

Assuming you have a range named data consisting of your data, AND that your
data start in row 1, this works:

=SUM(OFFSET(data,,,1,),OFFSET(data,COUNT(data)-1,,1,))+(2*SUMPRODUCT(data*(MOD(ROW(data),2)=1)*(ROW(data)<>1)*(ROW(data)<>COUNT(data))))+(4*SUMPRODUCT(data*(MOD(ROW(data),2)=0)))

Change specifics if the conditions don't match.

 

[Jarek Kujawa]

surely there is a simpler formula, sb. will possibly post it, BUT
presuming yr data is in H1:H100 try:

=IF(MOD(MAX(IF(H1:H100<>"",WIERSZ(H1:H100),"")),
2)=0,2*SUM(IF(MOD(ROW(H1:H100),2)<>0,H1:H100,))-
H1+4*SUM(IF(MOD(ROW(H1:H100);
2)=0,H1:H100,))-3*INDIRECT("H"&MAX(IF(H1:H100<>"",ROW(H1:H100),""))),
2*SUM(IF(MOD(ROW(H1:H100),2)<>0,H1:H100,))-H1-
INDIRECT("H"&MAX(IF(H1:H100<>"",ROW(H1:H100),"")))
+4*SUM(IF(MOD(ROW(H1:H100),2)=0,H1:H100,)))

this is an array formula that must be insrted with CTRL+SHIFT+ENTER

hope it will work on yr PC, it works on my xl2007

 

[Wigi]

Jarek

WIERSZ is not an English Excel function. What does it mean?

 

[Lars-Åke Aspelin]

Jarek

WIERSZ is not an English Excel function. What does it mean?

I don't know, but I guess it is the same as ROW()

Lars-Åke

 

[Teethless mama]

=B1+LOOKUP(10^10,data)+2*(SUMPRODUCT((MOD(ROW(data),2)=1)*data)-B1-LOOKUP(10^10,data))+4*(SUMPRODUCT((MOD(ROW(data),2)=0)*data))

 

[vmohan1978]

Hi,
Just i want to know in data should i consider range b2:b11 or b2:b10, please
let me know

 

[vmohan1978]

Hi Wigi,
Thanks for your help . I want to Know what should i put in data b2:b11 or
b2:b10.
Please let me know

 

[Spiky]

Hi,
Just i want to know in data should i consider range b2:b11 or b2:b10, please
let me know

It should be whatever range you need for that set of numbers. With the
above 11 numbers (P0-P10) it should be B2:B12, I guess. You said there
were 10 numbers, but you printed 11 in the first post.

If you want to make the data range dynamic, as well, you can probably
use INDIRECT and OFFSET functions to do so. But that's a different
question to ask.

 

[Jarek Kujawa]

it's ROW
sorry, missed that, I am using a Polish Excel version

 

[vmohan1978]

Hi,
I am getting different answerActual it should be 1735 but i am getting 1615,
Please Help me?

 

[vmohan1978]

Hi,
It worked wonderfully i have to consider data as B1:B11.
Thank you very much.

 

[vmohan1978]

Hi when i am using following example i am not getting required answer please
let me know what to do .

P0 60
P1 65
P2 70
P3 75
P4 80
P5 50
P6 40
P7 41
P8 43
P9 50
P10 85
P11 80
P12 75
P13 78

required answer is 2368 but i am getting 2524 .
please let me know where i am going wrong

 

[Lars-Åke Aspelin]

The formula you got from Teetless mama works if there are an odd
number of points (corresponding to an even number of subintervals).
Does not Simpson rule require just that? See e.g.
http://math.fullerton.edu/mathews/n2003/SimpsonsRuleMod.html

But in the example below you have an even number of points (P0-P13)
corresponding to an odd number of subintervals.

Lars-Åke

 

[Jarek Kujawa]

why don't you use my formula?
may be a bit illegible BUT it shows 2368

 

[Harlan Grove]

Hi when i am using following example i am not getting required answer please
let me know what to do .

P0      60
P1      65
P2      70
P3      75
P4      80
P5      50
P6      40
P7      41
P8      43
P9      50
P10     85
P11     80
P12     75
P13     78

required answer is 2368 but i am getting 2524 .

Someone else has already pointed out that this has an odd number of
intervals, so Simpson's rule isn't applicable. Looks like the result
you want is the Simpson's rule result for the first 13 points (12
intervals) plus the sum of the last 2 points. In other words, in brute
force terms assuming your data was in B1:B14, you want

=SUMPRODUCT(B1:B14,{1;4;2;4;2;4;2;4;2;4;2;4;2;1})

which is equal to

=SUMPRODUCT(B1:B13,{1;4;2;4;2;4;2;4;2;4;2;4;1})+B13+B14

If you always want to handle even numbers of points/odd numbers of
intervals this way, try

=SUMPRODUCT(B1:B14,LOOKUP(MOD(ROW(B1:B14)-MIN(ROW(B1:B14)),2),{0,1},
{2,4}))
-B1-B14*IF(MOD(ROWS(B1:B14),2)=1,1,3)

And if you want to generalize this to refer to the defined name data,

=SUMPRODUCT(data,LOOKUP(MOD(ROW(data)-MIN(ROW(data)),2),{0,1},{2,4}))
-N(data)-LOOKUP(1E+300,data)*IF(MOD(ROWS(data),2)=1,1,3)

 

[vmohan1978]

Hi,
If you start naming P0 it shouls end with Even Numbers, if you start as P1
it will end up with Odd numbers. I wrongly posted thanks for your feed back