Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combining Average, Offset & Vlookup
Excel 2002
Hi Excel users and Experts, I'm trying to lookup a value in a list, and average the four values 1 column to the right and -4 rows high. This formula does that. =AVERAGE(OFFSET(F9,0,1,-4,1)) The F9 is a cell in the list column and I want to replace F9 with a vlookup or a lookup or a match where the lookup or match reference is supplied by a dropdown in B2. These do not work and error out. Tried index & match but also failed miserably . A Google search danced all around what I need. AVERAGE(OFFSET(lookup(B2,F2:G16,1,0),0,1,-4,1)) AVERAGE(OFFSET(vlookup(B2,F2:G16,1,0),0,1,-4,1)) AVERAGE(OFFSET(indirect(lookup(B2,F2:G16,1,0),0,1,-4,1))) Thanks, Regards, Howard |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combining Average, Offset & Vlookup
Try it like this:
=AVERAGE(OFFSET(INDEX(F2:F16,MATCH(B2,F2:F16,0)),, 1,-4)) -- Biff Microsoft Excel MVP "L. Howard Kittle" wrote in message . .. Excel 2002 Hi Excel users and Experts, I'm trying to lookup a value in a list, and average the four values 1 column to the right and -4 rows high. This formula does that. =AVERAGE(OFFSET(F9,0,1,-4,1)) The F9 is a cell in the list column and I want to replace F9 with a vlookup or a lookup or a match where the lookup or match reference is supplied by a dropdown in B2. These do not work and error out. Tried index & match but also failed miserably . A Google search danced all around what I need. AVERAGE(OFFSET(lookup(B2,F2:G16,1,0),0,1,-4,1)) AVERAGE(OFFSET(vlookup(B2,F2:G16,1,0),0,1,-4,1)) AVERAGE(OFFSET(indirect(lookup(B2,F2:G16,1,0),0,1,-4,1))) Thanks, Regards, Howard |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combining Average, Offset & Vlookup
Hi Biff,
Right on the money! Thanks you very much for the help. I can see I would have never really been able to put it together. Thanks again, Regards, Howard "L. Howard Kittle" wrote in message . .. Excel 2002 Hi Excel users and Experts, I'm trying to lookup a value in a list, and average the four values 1 column to the right and -4 rows high. This formula does that. =AVERAGE(OFFSET(F9,0,1,-4,1)) The F9 is a cell in the list column and I want to replace F9 with a vlookup or a lookup or a match where the lookup or match reference is supplied by a dropdown in B2. These do not work and error out. Tried index & match but also failed miserably . A Google search danced all around what I need. AVERAGE(OFFSET(lookup(B2,F2:G16,1,0),0,1,-4,1)) AVERAGE(OFFSET(vlookup(B2,F2:G16,1,0),0,1,-4,1)) AVERAGE(OFFSET(indirect(lookup(B2,F2:G16,1,0),0,1,-4,1))) Thanks, Regards, Howard |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combining Average, Offset & Vlookup
Hi again Biff,
I added an error checker to your suggested formula, as there may be a selected value from the dropdown that won't have three values to average to the selected value. =IF(ISERROR(AVERAGE(OFFSET(INDEX(F2:F16,MATCH(B2,F 2:F16,0)),,1,-4))),"There are not 4 months to average",AVERAGE(OFFSET(INDEX(F2:F16,MATCH(B2,F2:F 16,0)),,1,-4))) I ground a few theeth getting all the )))'s & the ((('s in the correct place but knowing I had a solid formula I got'er done. Thanks again, Howard "L. Howard Kittle" wrote in message . .. Excel 2002 Hi Excel users and Experts, I'm trying to lookup a value in a list, and average the four values 1 column to the right and -4 rows high. This formula does that. =AVERAGE(OFFSET(F9,0,1,-4,1)) The F9 is a cell in the list column and I want to replace F9 with a vlookup or a lookup or a match where the lookup or match reference is supplied by a dropdown in B2. These do not work and error out. Tried index & match but also failed miserably . A Google search danced all around what I need. AVERAGE(OFFSET(lookup(B2,F2:G16,1,0),0,1,-4,1)) AVERAGE(OFFSET(vlookup(B2,F2:G16,1,0),0,1,-4,1)) AVERAGE(OFFSET(indirect(lookup(B2,F2:G16,1,0),0,1,-4,1))) Thanks, Regards, Howard |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combining Average, Offset & Vlookup
You're welcome. Thanks for the feedback!
Another way to write the same formula: =AVERAGE(OFFSET(F2,MATCH(B2,F2:F16,0)-1,1,-4)) The difference between the 2 formulas is like 6 of one, half dozen of the other! -- Biff Microsoft Excel MVP "L. Howard Kittle" wrote in message . .. Hi Biff, Right on the money! Thanks you very much for the help. I can see I would have never really been able to put it together. Thanks again, Regards, Howard "L. Howard Kittle" wrote in message . .. Excel 2002 Hi Excel users and Experts, I'm trying to lookup a value in a list, and average the four values 1 column to the right and -4 rows high. This formula does that. =AVERAGE(OFFSET(F9,0,1,-4,1)) The F9 is a cell in the list column and I want to replace F9 with a vlookup or a lookup or a match where the lookup or match reference is supplied by a dropdown in B2. These do not work and error out. Tried index & match but also failed miserably . A Google search danced all around what I need. AVERAGE(OFFSET(lookup(B2,F2:G16,1,0),0,1,-4,1)) AVERAGE(OFFSET(vlookup(B2,F2:G16,1,0),0,1,-4,1)) AVERAGE(OFFSET(indirect(lookup(B2,F2:G16,1,0),0,1,-4,1))) Thanks, Regards, Howard |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
offset off a vlookup? | Excel Discussion (Misc queries) | |||
combining weekday formula, average and going back 7 cells | Excel Discussion (Misc queries) | |||
VLOOKUP with OFFSET | Excel Worksheet Functions | |||
Problems with offset/average formula | Excel Worksheet Functions | |||
AVERAGE / OFFSET FUNCTIONS, TODAY.. | Excel Discussion (Misc queries) |