This is my formula but what I should be getting for: B's, C's & D's is not
what I am getting:

=LOOKUP(D18,{"a","a-","b+","b","b-","c+","c","c-","d+","d","d-","f";4,3.7,3.3,3,2.7,2.3,2,1.7,1.3,1,0.7,0})* E18

As you can see from my spread sheet I am getting the wrongs numbers:

A 1 4
A- 1 3.7
B+ 1 3.3
B 1 3.7 (should be 3)
B- 1 3.7
C+ 1 2.3
C 1 2.7 (should be 2)
C- 1 2.7
D+ 1 1.3
D 1 1.7 (should be 1)
D- 1 1.7
F 1 0

Can someone please tell me where my formula is going wrong. Because for the
rest of the sheet there should be nothing showing if there is no grade to
enter.

Use:

=VLOOKUP(E18,$F$1:$G$12,2,0)*F18

Data in F!:G12 as shown below

a 4
a- 3.7
b+ 3.3
b 3
b- 2.7
c+ 2.3
c+ 2
c- 1.7
d+ 1.3
d+ 1
d- 0.7
f 0



"JohnG" wrote:

This is my formula but what I should be getting for: B's, C's & D's is not
what I am getting:

=LOOKUP(D18,{"a","a-","b+","b","b-","c+","c","c-","d+","d","d-","f";4,3.7,3.3,3,2.7,2.3,2,1.7,1.3,1,0.7,0})* E18

As you can see from my spread sheet I am getting the wrongs numbers:

A 1 4
A- 1 3.7
B+ 1 3.3
B 1 3.7 (should be 3)
B- 1 3.7
C+ 1 2.3
C 1 2.7 (should be 2)
C- 1 2.7
D+ 1 1.3
D 1 1.7 (should be 1)
D- 1 1.7
F 1 0

Can someone please tell me where my formula is going wrong. Because for the
rest of the sheet there should be nothing showing if there is no grade to
enter.

sorry ... should be ....


=VLOOKUP(D18,$F$1:$G$12,2,0)*E18

"Toppers" wrote:

Use:

=VLOOKUP(E18,$F$1:$G$12,2,0)*F18

Data in F!:G12 as shown below

a 4
a- 3.7
b+ 3.3
b 3
b- 2.7
c+ 2.3
c+ 2
c- 1.7
d+ 1.3
d+ 1
d- 0.7
f 0



"JohnG" wrote:

This is my formula but what I should be getting for: B's, C's & D's is not
what I am getting:

=LOOKUP(D18,{"a","a-","b+","b","b-","c+","c","c-","d+","d","d-","f";4,3.7,3.3,3,2.7,2.3,2,1.7,1.3,1,0.7,0})* E18

As you can see from my spread sheet I am getting the wrongs numbers:

A 1 4
A- 1 3.7
B+ 1 3.3
B 1 3.7 (should be 3)
B- 1 3.7
C+ 1 2.3
C 1 2.7 (should be 2)
C- 1 2.7
D+ 1 1.3
D 1 1.7 (should be 1)
D- 1 1.7
F 1 0

Can someone please tell me where my formula is going wrong. Because for the
rest of the sheet there should be nothing showing if there is no grade to
enter.

OR change order of data in LOOKUP to be:

a,a-,b,b-,b+ etc

"JohnG" wrote:

This is my formula but what I should be getting for: B's, C's & D's is not
what I am getting:

=LOOKUP(D18,{"a","a-","b+","b","b-","c+","c","c-","d+","d","d-","f";4,3.7,3.3,3,2.7,2.3,2,1.7,1.3,1,0.7,0})* E18

As you can see from my spread sheet I am getting the wrongs numbers:

A 1 4
A- 1 3.7
B+ 1 3.3
B 1 3.7 (should be 3)
B- 1 3.7
C+ 1 2.3
C 1 2.7 (should be 2)
C- 1 2.7
D+ 1 1.3
D 1 1.7 (should be 1)
D- 1 1.7
F 1 0

Can someone please tell me where my formula is going wrong. Because for the
rest of the sheet there should be nothing showing if there is no grade to
enter.

I tried changing the order as you suggested, but now I receive an error
message:

"The formula you typed contains an error." I still have the same
information just changed the order:
=LOOKUP(D18,{"a","a-","b","b-","b+","c","c-","c+","d","d-","d+","f";4,3.7,3,2.7,3.3,2,1.7,2.3,1,1,0.7,1.3,0 })*E18

Can I send you a copy of the spreadsheet with coloums heading and all the
information that I have so that you can see what can be done.


"Toppers" wrote:

OR change order of data in LOOKUP to be:

a,a-,b,b-,b+ etc

"JohnG" wrote:

This is my formula but what I should be getting for: B's, C's & D's is not
what I am getting:

=LOOKUP(D18,{"a","a-","b+","b","b-","c+","c","c-","d+","d","d-","f";4,3.7,3.3,3,2.7,2.3,2,1.7,1.3,1,0.7,0})* E18

As you can see from my spread sheet I am getting the wrongs numbers:

A 1 4
A- 1 3.7
B+ 1 3.3
B 1 3.7 (should be 3)
B- 1 3.7
C+ 1 2.3
C 1 2.7 (should be 2)
C- 1 2.7
D+ 1 1.3
D 1 1.7 (should be 1)
D- 1 1.7
F 1 0

Can someone please tell me where my formula is going wrong. Because for the
rest of the sheet there should be nothing showing if there is no grade to
enter.

Try:

=LOOKUP(D18,{"a","a-","b","b-","b+","c","c-","c+","d","d-","d+","f"},{4,3.7,3,2.7,3.3,2,1.7,2.3,1,1,0.7,1.3 ,0})*E18

"JohnG" wrote:

I tried changing the order as you suggested, but now I receive an error
message:

"The formula you typed contains an error." I still have the same
information just changed the order:
=LOOKUP(D18,{"a","a-","b","b-","b+","c","c-","c+","d","d-","d+","f";4,3.7,3,2.7,3.3,2,1.7,2.3,1,1,0.7,1.3,0 })*E18

Can I send you a copy of the spreadsheet with coloums heading and all the
information that I have so that you can see what can be done.


"Toppers" wrote:

OR change order of data in LOOKUP to be:

a,a-,b,b-,b+ etc

"JohnG" wrote:

This is my formula but what I should be getting for: B's, C's & D's is not
what I am getting:

=LOOKUP(D18,{"a","a-","b+","b","b-","c+","c","c-","d+","d","d-","f";4,3.7,3.3,3,2.7,2.3,2,1.7,1.3,1,0.7,0})* E18

As you can see from my spread sheet I am getting the wrongs numbers:

A 1 4
A- 1 3.7
B+ 1 3.3
B 1 3.7 (should be 3)
B- 1 3.7
C+ 1 2.3
C 1 2.7 (should be 2)
C- 1 2.7
D+ 1 1.3
D 1 1.7 (should be 1)
D- 1 1.7
F 1 0

Can someone please tell me where my formula is going wrong. Because for the
rest of the sheet there should be nothing showing if there is no grade to
enter.

I tried this formula and received a "N/A"

"Toppers" wrote:

sorry ... should be ....


=VLOOKUP(D18,$F$1:$G$12,2,0)*E18

"Toppers" wrote:

Use:

=VLOOKUP(E18,$F$1:$G$12,2,0)*F18

Data in F!:G12 as shown below

a 4
a- 3.7
b+ 3.3
b 3
b- 2.7
c+ 2.3
c+ 2
c- 1.7
d+ 1.3
d+ 1
d- 0.7
f 0



"JohnG" wrote:

This is my formula but what I should be getting for: B's, C's & D's is not
what I am getting:

=LOOKUP(D18,{"a","a-","b+","b","b-","c+","c","c-","d+","d","d-","f";4,3.7,3.3,3,2.7,2.3,2,1.7,1.3,1,0.7,0})* E18

As you can see from my spread sheet I am getting the wrongs numbers:

A 1 4
A- 1 3.7
B+ 1 3.3
B 1 3.7 (should be 3)
B- 1 3.7
C+ 1 2.3
C 1 2.7 (should be 2)
C- 1 2.7
D+ 1 1.3
D 1 1.7 (should be 1)
D- 1 1.7
F 1 0

Can someone please tell me where my formula is going wrong. Because for the
rest of the sheet there should be nothing showing if there is no grade to
enter.

Not sure which formula caused the error but #N/A error is due to data. For
example, you have " b " i.e. a blank before and/or after the letter.

You could try (TRIM(D18) .... in the relevant (or all ) formula.


"JohnG" wrote in message
...
I tried this formula and received a "N/A"

"Toppers" wrote:

sorry ... should be ....


=VLOOKUP(D18,$F$1:$G$12,2,0)*E18

"Toppers" wrote:

Use:

=VLOOKUP(E18,$F$1:$G$12,2,0)*F18

Data in F!:G12 as shown below

a 4
a- 3.7
b+ 3.3
b 3
b- 2.7
c+ 2.3
c+ 2
c- 1.7
d+ 1.3
d+ 1
d- 0.7
f 0



"JohnG" wrote:

This is my formula but what I should be getting for: B's, C's & D's
is not
what I am getting:

=LOOKUP(D18,{"a","a-","b+","b","b-","c+","c","c-","d+","d","d-","f";4,3.7,3.3,3,2.7,2.3,2,1.7,1.3,1,0.7,0})* E18

As you can see from my spread sheet I am getting the wrongs numbers:

A 1 4
A- 1 3.7
B+ 1 3.3
B 1 3.7 (should be 3)
B- 1 3.7
C+ 1 2.3
C 1 2.7 (should be 2)
C- 1 2.7
D+ 1 1.3
D 1 1.7 (should be 1)
D- 1 1.7
F 1 0

Can someone please tell me where my formula is going wrong. Because
for the
rest of the sheet there should be nothing showing if there is no
grade to
enter.