Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Ok I will try to explain my issue effectively. I am making a GPA sheet in which the value out of 100 is computer in to a GPA value and then in to a letter.

In cell N5 i have the value of all their grades (formula: =H3+H4+H5)

Now in cell (j6) I have a formula which is giving them a number depending on the value calculated in N5 (Formula: =IF(AND(N5>=60,N5<=63.999),"2.0",IF(AND(N5>=64,N5<=66.999),"2.25",IF(AND(N5>=67,N5<=69.999),"2.4",IF(AND(N5>=70,N5<=73.999),"2.5",IF(AND(N5>=74,N5<=76.999),"2.75",IF(AND(N5>=77,N5<=79.999),"2.9",IF(AND(N5>=80,N5<=83.999),"3.0",IF(AND(N5>=84,N5<=86.999),"3.25",IF(AND(N5>=87,N5<=89.999),"3.4",IF(AND(N5>=90,N5<=93.999),"3.50",IF(AND(N5>=94,N5<=96.999),"3.75",IF(AND(N5>=97,N5<=100),"4",IF(AND(N5<=59.999),"0")))))))))))))

Still no problem... as the values I was looking for comes out (example 84.2 shows up as 3.25 as I wanted).

However here comes the problem.... I have tried to use the outcome in J6 to do Vlookup or another if formula, however excel does not seem to recognize the value in J6.

For example: =VLOOKUP(j6,B3:C15,2,FALSE)... this returns N/A however if I enter =VLOOKUP(3.25,B3:C15,2,FALSE) it gives me what im looking for. It seems that excel will not register the outcome of my formula as a number.

What can I do please?

share|improve this question
    
Sorry the last paragraph was meant to say For example: =VLOOKUP(j6,B3:C15,2,FALSE)... this returns N/A however if I enter =VLOOKUP(3.25,B3:C15,2,FALSE) it gives me what im looking for. It seems that excel will not register the outcome of my formula as a number. –  Steven Nov 3 '13 at 13:10

2 Answers 2

up vote 1 down vote accepted

By using quotes you are returning text values - remove the quotes, i.e.

=IF(AND(N5>=60,N5<=63.999),2,IF(AND(N5>=64,N5<=66.999),2.25,IF(AND(N5>=67,N5<=69.999),2.4,IF(AND(N5>=70,N5<=73.999),2.5,IF(AND(N5>=74,N5<=76.999),2.75,IF(AND(N5>=77,N5<=79.999),2.9,IF(AND(N5>=80,N5<=83.999),3,IF(AND(N5>=84,N5<=86.999),3.25,IF(AND(N5>=87,N5<=89.999),3.4,IF(AND(N5>=90,N5<=93.999),3.5,IF(AND(N5>=94,N5<=96.999),3.75,IF(AND(N5>=97,N5<=100),4,IF(AND(N5<=59.999),0)))))))))))))

....although you don't need all those ANDs - this should work for you

=IF(N5<60,0,IF(N5<64,2,IF(N5<67,2.25,IF(N5<70,2.4,IF(N5<74,2.5,IF(N5<77,2.75,IF(N5<80,2.9,IF(N5<84,3,IF(N5<87,3.25,IF(N5<90,3.4,IF(N5<94,3.5,IF(N5<97,3.75,IF(N5<=100,4)))))))))))))

or with LOOKUP

=LOOKUP(N5,{0,60,64,67,70,74,77,80,84,87,90,94,97;0,2,2.25,2.4,2.5,2.75,2.9,3,3.25,3.4,3.5,3.75,4})

share|improve this answer
    
you are amazing! thankyou so so much! –  Steven Nov 3 '13 at 14:26

What is J6 formatted? Looks like it's probably text, which won't be useful if your vlookup is looking in numbers.

Try using =value() before your if() to convert text to number -

=Value(IF(AND(N5>=60,N5<=63.999),"2.0",IF(AND(N5>=64,N5<=66.999),"2.25",IF(AND(N5>=67,N5<=69.999),"2.4",IF(AND(N5>=70,N5<=73.999),"2.5",IF(AND(N5>=74,N5<=76.999),"2.75",IF(AND(N5>=77,N5<=79.999),"2.9",IF(AND(N5>=80,N5<=83.999),"3.0",IF(AND(N5>=84,N5<=86.999),"3.25",IF(AND(N5>=87,N5<=89.999),"3.4",IF(AND(N5>=90,N5<=93.999),"3.50",IF(AND(N5>=94,N5<=96.999),"3.75",IF(AND(N5>=97,N5<=100),"4",IF(AND(N5<=59.999),"0"))))))))))))))

Will that work in the vlookup?

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.