Average for specified intervals

pssureshbabu

New Member
Joined
Oct 5, 2006
Messages
16
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
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,826
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Oct 5, 2006
Messages
16
Auswertung_.xls
ABCDEF
1X1Y1X2Y2X3Y3
20,017145455,32790,000341546,90060,0069010351,9034
30,017045455,5380,000516547,07580,0072260352,1131
40,017257955,73070,00080447,26850,0071885352,2878
50,017370455,94090,00112947,51370,0073135352,4625
60,017332956,20360,001391547,72390,0075885352,6722
70,017657956,43130,00157947,91660,0074635352,8993
80,017882956,64140,001891548,10920,0075135353,109
90,017782956,85160,002041548,30190,0077135353,3536
100,018082957,11430,00220448,47710,0077635353,5633
110,018470457,3070,002291548,66970,0078510353,7555
120,018545457,53470,002416548,9150,0080760353,9302
130,018782957,81490,002391549,09010,0081385354,1224
140,019257958,04260,00242949,33530,0081260354,3321
150,019357958,27030,00220449,51050,0080510354,5767
160,019445458,5330,002166549,77320,0079885354,8213
170,019732958,76070,002041549,93090,0078635355,0484
180,019770458,97080,00187950,17610,0078260355,2581
190,019857959,1810,001841550,36880,0080135355,4503
200,020082959,39120,002116550,59650,0080135355,6425
210,020120459,60140,00200450,73660,0081260355,8522
220,020145459,7590,002166550,91180,0082760356,0443
230,020245459,96920,002466551,13950,0083010356,3239
240,020182960,21440,00245451,27960,0083635356,5336
250,020195460,38950,002491551,47230,0086385356,7607
260,020282960,63470,00297951,66490,0085635357,0053
270,020157960,89740,002991551,82260,0087885357,215
280,020232961,07260,003266551,98020,0091135357,4072
290,020282961,28270,00380452,20790,0091385357,6169
300,020157961,52790,00400452,40060,0092885357,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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,377
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:

Code:
=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

New Member
Joined
Oct 5, 2006
Messages
16
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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,377
...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...

Code:
=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!
 

Watch MrExcel Video

Forum statistics

Threads
1,113,918
Messages
5,545,025
Members
410,647
Latest member
bernardazar
Top