Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Worksheet Functions > vlookup then average 3 above and below
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 74
Default vlookup then average 3 above and below
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.
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 15,768
Default vlookup then average 3 above and below
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.


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 74
Default vlookup then average 3 above and below
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.



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 15,768
Default vlookup then average 3 above and below
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.





Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 74
Default vlookup then average 3 above and below
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.






Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 74
Default vlookup then average 3 above and below
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:D205,1)-4,,7))

"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.






Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 74
Default vlookup then average 3 above and below


"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.






Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 15,768
Default vlookup then average 3 above and below
=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.








Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 74
Default 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.









Reply With QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 15,768
Default vlookup then average 3 above and below
You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"tpeter" wrote in message
...
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.











Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Combining Average, Offset & Vlookup L. Howard Kittle Excel Worksheet Functions 4 November 14th 07 05:45 PM
Find monthly average but have average automatically configured kimbafred Excel Discussion (Misc queries) 2 August 8th 07 12:28 AM
vlookup average() new_121 Excel Worksheet Functions 2 May 29th 07 05:06 PM
Using VLookup, Displacement, SumIF to total and average Paul987 Excel Discussion (Misc queries) 3 November 28th 05 08:55 PM
Error Handling #N/A with AVERAGE Function - Average of values in Row Sam via OfficeKB.com Excel Worksheet Functions 13 July 31st 05 03:59 PM


All times are GMT +1. The time now is 04:55 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement