Average for specified intervals

[pssureshbabu]

Hello!

I have 3 sets of X and Y data. Total rows for each set exceeds 5000 values. For each set, the following valids:
X starts from 0.001
X ends at 14
Now I want to reduce the data to 100 values i.e. average of the 3 sets for each specified interval of X (independent variable).

For eg. I need to set the delta X (eg. 0.005) and for that interval the function has to check the 3 set Y values and to give me only one X and Y value.

I hope i explained it clearly!!!!! Please tell me is there a way to do this automatically?

thanks,

Babu

 

[Peter_SSs]

pssureshbabu

No answer after this long usually means nobody really knows what you want.

Can you explain a bit more of what you have and what you are trying to do?

Perhaps post a small sample of your worksheet? Here's how
http://www.mrexcel.com/board2/viewtopic.php?t=92622
Then give an explanation using your sample as an example.

 

[pssureshbabu]

Auswertung_.xls
	A	B	C	D	E	F
1	X1	Y1	X2	Y2	X3	Y3
2	0,0171454	55,3279	0,0003415	46,9006	0,00690103	51,9034
3	0,0170454	55,538	0,0005165	47,0758	0,00722603	52,1131
4	0,0172579	55,7307	0,000804	47,2685	0,00718853	52,2878
5	0,0173704	55,9409	0,001129	47,5137	0,00731353	52,4625
6	0,0173329	56,2036	0,0013915	47,7239	0,00758853	52,6722
7	0,0176579	56,4313	0,001579	47,9166	0,00746353	52,8993
8	0,0178829	56,6414	0,0018915	48,1092	0,00751353	53,109
9	0,0177829	56,8516	0,0020415	48,3019	0,00771353	53,3536
10	0,0180829	57,1143	0,002204	48,4771	0,00776353	53,5633
11	0,0184704	57,307	0,0022915	48,6697	0,00785103	53,7555
12	0,0185454	57,5347	0,0024165	48,915	0,00807603	53,9302
13	0,0187829	57,8149	0,0023915	49,0901	0,00813853	54,1224
14	0,0192579	58,0426	0,002429	49,3353	0,00812603	54,3321
15	0,0193579	58,2703	0,002204	49,5105	0,00805103	54,5767
16	0,0194454	58,533	0,0021665	49,7732	0,00798853	54,8213
17	0,0197329	58,7607	0,0020415	49,9309	0,00786353	55,0484
18	0,0197704	58,9708	0,001879	50,1761	0,00782603	55,2581
19	0,0198579	59,181	0,0018415	50,3688	0,00801353	55,4503
20	0,0200829	59,3912	0,0021165	50,5965	0,00801353	55,6425
21	0,0201204	59,6014	0,002004	50,7366	0,00812603	55,8522
22	0,0201454	59,759	0,0021665	50,9118	0,00827603	56,0443
23	0,0202454	59,9692	0,0024665	51,1395	0,00830103	56,3239
24	0,0201829	60,2144	0,002454	51,2796	0,00836353	56,5336
25	0,0201954	60,3895	0,0024915	51,4723	0,00863853	56,7607
26	0,0202829	60,6347	0,002979	51,6649	0,00856353	57,0053
27	0,0201579	60,8974	0,0029915	51,8226	0,00878853	57,215
28	0,0202329	61,0726	0,0032665	51,9802	0,00911353	57,4072
29	0,0202829	61,2827	0,003804	52,2079	0,00913853	57,6169
30	0,0201579	61,5279	0,004004	52,4006	0,00928853	57,844
Daten

So as in the above table, for 3 set of X and Y values, I want the following.

for X = 0.01 to 0.05, what is the average value of Y from all the y1, y2, y3 values corresponding to that X range?

for X = 0.05 - 0.1, what is the average value of Y from all the y1, y2, y3 values corresponding to that X range?

for X = 0.1 - 0.15, ................

and so on until the end of final values.

The main reason to do this is, reduce the 5000 values to 150 values. I hope atleast this time I made it clear.

Thanks,

Babu

 

[Domenic]

Let H2:H4 contain 0.01, 0.05, and 0.1

Let I2:I4 contain 0.05, 0.1, and 0.15

J2, copied down:

=AVERAGE(IF(MOD(COLUMN($A$2:$E$30)-COLUMN($A$2),2)=0,IF($A$2:$E$30>=H2,IF($A$2:$E$30<I2,$B$2:$F$30))))

...confirmed with CONTROL+SHIFT+ENTER. Note that if the columns containing the Y values can contain blanks, the formula would need to be amended.

Hope this helps!

 

[pssureshbabu]

Thank you very much. It works well.

As you suspect, I do have empty cells at the final stage.

That is, first set has 6300 values, second set has 6320 values and third has 6350 values!

I gave F cell refernce as F6350, do you think I need to change the formula?

Regards,

Babu

 

[Domenic]

...do you think I need to change the formula?

If a cell in the Y column is blank and the corresponding cell in the X column meets the criteria, then yes. The formula would be changed as follows...

=AVERAGE(IF(MOD(COLUMN($A$2:$E$30)-COLUMN($A$2),2)=0,IF($A$2:$E$30>=H2,IF($A$2:$E$30<I2,IF($B$2:$F$30<>"",$B$2:$F$30)))))

Hope this helps!