Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .