Vlookup and Average

One last formula to go.... I think...

Ok so we have the vlookup command..
it is searching for a specific id number, the id number has three values next to it. I need the formula to find the id then display the average of the 3 values next to it.
I tried this:
=AVERAGE(VLOOKUP(K3,'CSR Data'!$G$3:$J$41,2,FALSE))

the averages aren't matching.
the id is 33126
the totals to average are 88.6%, 79.1% and 85.0%

to my calculations the average is 84.23
but the formula displays 88.64

just the average command on those cells displays 84.25


What am I doing wrong?

bpaw

I figured out the end portion isn't working, because the 2 would be telling it to output the data from the second field.

I am also trying this formula:
VLOOKUP(AVERAGE('CSR Data'!H3:J4)(K4,'CSR Data'!G3:J41,2))
this one just came back an error or added a * before k4


bpaw

ok, I tried this formula... I may be getting closer, but the output is still wrong.

=VLOOKUP(K2,'CSR Data'!$G$3:$J$41,AVERAGE(2,3,4))

this returns the first number of the three that needs to be averaged


bpaw

Hi

VLOOKUP will only return one item.

If you want to bring back more than one response, then you will need to do something else.

Try
=sumproduct(--('CSR Data'!$G$3:$G$41 = k2),($H$3:$H$41)) / countif('CSR Data'!$G$3:$G$41,K2)

rylo

I don't want more than one response. I just need the average of the three numbers that follow the id.

Vlookup is suppose to find the id, then i am working on the portion that will average the 3 numbers after then id and display it in the field.

bpaw

i don't understand the part you said about ctl+shift+enter

If I enter as it is typed, it give an error so I am assuming that I need the ctl+shift+enter..

Can you please explain further?

Thanks
bpaw

Ok, a couple of questions...
I think I understand the concept now, however if I put the formula in the field then push control+shift+enter all at the same time, it then displays the formula, not the result.

so first question is must I type the formula or can I copy and paste it?
second question is when I type the formula must I be exact in pushing all three buttons at the same time?
last, once (if i ever can) get this formula to work, can I copy and paste it to the remaining other 39 cells?

I am reading the links you sent, but apparently I am having some kind of brain overload.

sorry about this... I am usually not such a dork when it comes to instructions.

bpaw

So I need to sit on a curb and kick a rock.
This is just blowing my mind.

I know I am missing something but I can't figure it out.

I made sure the cell was set to general.
I copied the formula and pressed control, then shift, then hit the enter button. I got an formula error box.

erased, the data, copied and pasted it, then pressed control, shift and enter all at the same time.
I got a formula error.

erased the data, hand typed it, then pressed control and shift then hit enter.
I got a formula error.

erased the data, hand typed it then pressed control, shift and enter all at the same time.
I got a formula error.

then I screamed UGH! then sniffled a bit.... then I came back to the message board, lol.

I am seriously missing something. I have tried to check my fields to make sure they are proper but at this point... I am lost.

the number I am looking for is in field k2 on the sheet named test, the data ranges from column g3 to g41 on the csr data sheet. the percentages I need to average are in columns h3, i3 and j3 going down to h41, i41, and j41 on the csr sheet.

so i start with the average formula because that is the result I want. I find the id in field k2 on the test sheet. I tell it to look in the range of g3:j41 of the csr data sheet, then it is suppose to find that id, then the three percentages adjacent to it and give me an average.

How about I get a cup of hot chocolate, and scream again.

You have been awesome, I just feel like an idiot about now.

bpaw

You can find the actual line i am working on on the instruction sheet number 10,

line 59 on the excel sheet.

thanks...
bpaw

See the attached

The instructions are specifically requesting that I use vlookup.
At this point I am wondering if it is possible to use vlookup and the average command in the same formula.


bpaw

It works!!!

I must have tried this formula at least 10 times.
The difference was I was using : instead of , between the vlookup commands.

You guy rock!!!
Thank you.

bpaw