VLOOKUP troubles!!!!!!

[bass00]

I have four columns of data that are grades.There is one grade in each column.
(i.e the grades can consist of the following
F - 1.5
PC - 3
P - 4
C - 5
D - 6
HD - 7)
I want a total column, that averages the 4 grades. (i.e if in one row I have P,C,D,C the answer I should get is 5. For some reason only half my lookup table works, I cannot get all the grades to convert to numerical format.
Thanks for help.

 

[Legacy 98055]

Hi Bass
I would like to help, but I do not understand how your "lookup" table is setup exactly...
Please post a few more details for us slow learners, or if you'd like, E-Mail a sample with an example of expected results to TsTom@Hotmail.com

Have you checked into HLookUp?

Thanks,
Tom

 

[bass00]

Tom, thanks for helping out, I've email you the sheet, hop its not too confusing.

Thanks

 

[Aladin Akyurek]

On 2002-04-22 22:45, bass00 wrote:
I have four columns of data that are grades.There is one grade in each column.
(i.e the grades can consist of the following
F - 1.5
PC - 3
P - 4
C - 5
D - 6
HD - 7)
I want a total column, that averages the 4 grades. (i.e if in one row I have P,C,D,C the answer I should get is 5. For some reason only half my lookup table works, I cannot get all the grades to convert to numerical format.
Thanks for help.

I take it that what follows is your 2-column table of conversion:

{"F",1.5;
"PC",3;
"P",4;
"C",5;
"D",6;
"HD",7}

Select all the cells containing the grade symbols, go to the Name Box on the Formula Bar, type GSYMS, and hit enter.

Select all of the cells containing symbols and numbers, go to the Name Box, type GRADES, and hit enter.

Lets say that A2:D2 houses the following grades:

{"P","C","D","C"}

In F2 enter:

=SUMPRODUCT(N(OFFSET(GRADES,MATCH(A2:D2,GSYMS,0)-1,1,1,1)))/COUNTA(A2:D2)

which will compute the desired average.

Note. I'd suggest using numerical values instead of grades if possible, because a computation like the one above is rather costly.

Addendum: If you want to avoid getting #DIV/0! when a range has no grades at all, use:

=SUMPRODUCT(N(OFFSET(GRADES,MATCH(A2:D2,GSYMS,0)-1,1,1,1)))/MAX(1,COUNTA(A2:D2))

Aladin
This message was edited by Aladin Akyurek on 2002-04-22 23:56

 

[Legacy 98055]

My system date is all screwed up...
I do not know if you recieved my replies or not...
This formula worked on the sheet you sent me.
Place it in Cell g4 on your Summary sheet and copy down:

=AVERAGE(VLOOKUP(C4,J$4:K$9,2,FALSE),VLOOKUP(D4,J$4:K$9,2,FALSE),VLOOKUP(E4,
J$4:K$9,2,FALSE),VLOOKUP(F4,J$4:K$9,2,FALSE))


Thanks Aladin...
He had the right formula for the job, but the values in his table were not in ascending order. I know next to nothing about Excel functions but was able to figure out the need for the range_lookup = FALSE.
That was the problem
Tom
This message was edited by TsTom on 2002-04-23 00:22

 

[Aladin Akyurek]

On 2002-04-23 00:17, TsTom wrote:
My system date is all screwed up...
I do not know if you recieved my replies or not...
This formula worked on the sheet you sent me.
Place it in Cell g4 on your Summary sheet and copy down:

=AVERAGE(VLOOKUP(C4,J$4:K$9,2,FALSE),VLOOKUP(D4,J$4:K$9,2,FALSE),VLOOKUP(E4,
J$4:K$9,2,FALSE),VLOOKUP(F4,J$4:K$9,2,FALSE))


Thanks Aladin...
He had the right formula for the job, but the values in his table were not in ascending order. I know next to nothing about Excel functions but was able to figure out the need for the range_lookup = FALSE.
That was the problem
Tom
This message was edited by TsTom on 2002-04-23 00:22

That formula becomes a hell of a job if you have a lot of grades in a row and, I think, even costly if it must be copied down to many rows.

Aladin

 

[Legacy 98055]

You would know better than I...
I was just taking what was already there and making it work...
Tom