vlookup then average 3 above and below
Awsome, that worked perfectly, thank you so much for your help.
"T. Valko" wrote:
=AVERAGE(OFFSET(C6,MATCH(200,C6:C205,1)-4,,7))
This works but I need it to find the value of 200 in the c colume and then
take the 7 points in the d colume and average those.
Ok, just change the OFFSET reference C6 to D6:
=AVERAGE(OFFSET(D6,MATCH(200,C6:C205,1)-4,,7))
--
Biff
Microsoft Excel MVP
"tpeter" wrote in message
...
"tpeter" wrote:
I think I am just spinning we wheels now. Here is actual data that I am
using. This data is located in colume c,d,e,f and all I get is an N/A.
series 1 1.347988 #N/A
-16.847461 1.855801 -16.847461 1.855801
-16.847461 1.855801 -16.847461 1.855801
-16.847461 1.855801 -16.847461 1.855801
-16.847461 1.855801 -16.847461 1.855801
-16.847461 1.855801 -16.847461 1.855801
-16.847461 1.855801 -16.847461 1.855801
-16.847461 1.855801 -16.847461 1.855801
-16.847461 1.855801 -16.847461 1.855801
-16.847461 1.855801 -16.847461 1.855801
-16.847461 1.855801 -16.847461 1.855801
-16.847461 1.855801 -16.847461 1.855801
-16.847461 1.855801 -16.847461 1.855801
-16.847461 1.855801 -16.847461 1.855801
-16.847461 1.855801 -16.847461 1.855801
-16.847461 1.824063 -16.847461 1.824063
-16.847461 1.855801 -16.847461 1.855801
-16.847461 1.855801 -16.847461 1.855801
-16.847461 1.824063 -16.847461 1.824063
-16.847461 1.855801 -16.847461 1.855801
-16.847461 1.855801 -16.847461 1.855801
-16.847461 1.824063 -16.847461 1.824063
-16.847461 1.855801 -16.847461 1.855801
-16.847461 1.855801 -16.847461 1.855801
-16.847461 1.855801 -16.847461 1.855801
-16.847461 1.824063 -16.847461 1.824063
-16.847461 1.824063 -16.847461 1.824063
-16.847461 1.855801 -16.847461 1.855801
-16.847461 1.824063 -16.847461 1.824063
-16.847461 1.824063 -16.847461 1.824063
-16.847461 1.824063 -16.847461 1.824063
-16.847461 1.824063 -16.847461 1.824063
-16.847461 1.824063 -16.847461 1.824063
-16.847461 1.824063 -16.847461 1.824063
-16.847461 1.792324 -16.847461 1.792324
-16.847461 1.760586 -16.847461 1.760586
18.251416 1.728848 18.251416 1.728848
18.251416 1.665371 18.251416 1.665371
18.251416 1.633633 18.251416 1.633633
18.251416 1.601895 18.251416 1.601895
18.251416 1.538418 18.251416 1.538418
18.251416 1.538418 18.251416 1.538418
18.251416 1.50668 18.251416 1.50668
18.251416 1.474941 18.251416 1.474941
18.251416 1.474941 18.251416 1.474941
18.251416 1.443203 18.251416 1.443203
18.251416 1.443203 18.251416 1.443203
53.350293 1.443203 53.350293 1.443203
53.350293 1.411465 53.350293 1.411465
53.350293 1.411465 53.350293 1.411465
18.251416 1.379727 18.251416 1.379727
53.350293 1.379727 53.350293 1.379727
53.350293 1.379727 53.350293 1.379727
53.350293 1.347988 53.350293 1.347988
53.350293 1.347988 53.350293 1.347988
53.350293 1.347988 53.350293 1.347988
53.350293 1.347988 53.350293 1.347988
88.44917 1.379727 88.44917 1.379727
88.44917 1.379727 88.44917 1.379727
88.44917 1.379727 88.44917 1.379727
88.44917 1.379727 88.44917 1.379727
88.44917 1.379727 88.44917 1.379727
88.44917 1.379727 88.44917 1.379727
123.548047 1.379727 123.548047 1.379727
123.548047 1.379727 123.548047 1.379727
123.548047 1.379727 123.548047 1.379727
123.548047 1.347988 123.548047 1.347988
123.548047 1.347988 123.548047 1.347988
158.646924 1.379727 158.646924 1.379727
158.646924 1.347988 158.646924 1.347988
193.745801 1.347988 193.745801 1.347988
193.745801 1.379727 193.745801 1.379727
193.745801 1.347988 193.745801 1.347988
193.745801 1.347988 193.745801 1.347988
228.844678 1.347988 228.844678 1.347988
228.844678 1.347988 228.844678 1.347988
263.943555 1.347988 263.943555 1.347988
263.943555 1.347988 263.943555 1.347988
263.943555 1.347988 263.943555 1.347988
299.042432 1.347988 299.042432 1.347988
299.042432 1.347988 299.042432 1.347988
299.042432 1.347988 299.042432 1.347988
334.141309 1.347988 334.141309 1.347988
334.141309 1.347988 334.141309 1.347988
369.240186 1.347988 369.240186 1.347988
What I am looking for is the point in the first colum that is close to
200
and go above 3 and below 3 and give me the average. I am pretty sure it
is in
the Match reference but can't figure it out. here is the formula, c6 is
the
first piece of data in the first row:
=AVERAGE(OFFSET(C6,MATCH(200,C6:C205,1)-4,,7))
This works but I need it to find the value of 200 in the c colume and then
take the 7 points in the d colume and average those.
"tpeter" wrote:
Thank you for your help, this will get me close with some tweaks. I
appreciate you pointing me in the right direction.
"T. Valko" wrote:
I am guessing that is what the -4 is?
Yes. You want to calculate 3 cells on either side of the lookup_value
for a
total of 7 cells. The -4 finds the cell that is 3 on the top side of
the
lookup_value. So, the average starts from that cell and averages the
next 6
cells for a total of 7.
Where this would be a problem is, based on your posted sample, if the
lookup_value was say 1.1. There aren't 3 cells on either side of 1.1.
There
are no cells on the top side. That's why I noted:
Assuming that there will *always* be 3 cells on either
side of the lookup value:
--
Biff
Microsoft Excel MVP
"tpeter" wrote in message
...
I am not very familar with the offset and match functions, but this
lookup
is
for a series of data that is plotted so there is no blank cells on
either
side of the lookup. I am guessing that is what the -4 is? Sorry for
being
a
pain.
"T. Valko" wrote:
Assuming that there will *always* be 3 cells on either side of the
lookup
value:
=AVERAGE(OFFSET(A1,MATCH(1.4,A1:A7,0)-4,,7))
--
Biff
Microsoft Excel MVP
"tpeter" wrote in message
...
I have 2 colums of data and my vlookup works fine, but I now want
it to
find
the value, take the previous 3 data points and the next 3 data
points
and
give me the average of these 7 data points. I think the vlookup
is the
correct function but I am not sure. her is an example
1 1.1
2 1.2
3 1.3
4 1.4
5 1.5
6 1.7
7 1.8
so if my vlookup found the value of 1.4, I want it to average
1.1,1.2,1.3,1.4,1.5,1.6,1.7, and 1.8 and return that value.
Thank you for any help.
|