Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup help please
I'm not sure how to approach this and would appreciate some direction. Can I
use an IF formula with Vlookup? A 1 Scores 2 150 3 100 4 140 5 120 6 200 First I need to calculate the difference between two numbers in column A. For example the difference between A6 and A5. Once I have determined this value I thought I would use a Vlookup table to assign a grade in column B but I'm not certain if this will work because sometimes the difference between the two numbers may be a negative number. For Example: If the value falls 20-30 points I can assign a Grade of 2 If the value falls 31-40 points I can assign a Grade of 3 If the value is changes < 20 points in either direction I can assign a Grade of 0 If the value between two cells rises 20-30 points I can assign a Grade of 2 If the value between two cells rises 31-40 points I can assign a Grade of 3 If the value between two cells rises 41 points I can assign a Grade of 4 Thank you in advance |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup help please
Try this
=LOOKUP(ABS(your formula here),{0,20,31,41},{0,1,2,3,4}) If this post helps click Yes --------------- Jacob Skaria "ferde" wrote: I'm not sure how to approach this and would appreciate some direction. Can I use an IF formula with Vlookup? A 1 Scores 2 150 3 100 4 140 5 120 6 200 First I need to calculate the difference between two numbers in column A. For example the difference between A6 and A5. Once I have determined this value I thought I would use a Vlookup table to assign a grade in column B but I'm not certain if this will work because sometimes the difference between the two numbers may be a negative number. For Example: If the value falls 20-30 points I can assign a Grade of 2 If the value falls 31-40 points I can assign a Grade of 3 If the value is changes < 20 points in either direction I can assign a Grade of 0 If the value between two cells rises 20-30 points I can assign a Grade of 2 If the value between two cells rises 31-40 points I can assign a Grade of 3 If the value between two cells rises 41 points I can assign a Grade of 4 Thank you in advance |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup help please
I'm not sure what formula to plug in to capture the difference between A4 and
A5 =LOOKUP(ABS(A4-A5),{0,20,31,41},{0,1,2,3,4}) =3 The formula I used is A4-A5 which equals a fall of 50 points and so the Grade assigned should be 4. The next score added to cell A6 may be higher than A5 and so I will need to subtract the higher score (A6)from the previous score (A5) to determine the difference and assign a grade. Thank you for your help. A Scores Grade 4 150 falls20-30 2 5 100 falls31-40 3 6 falls40 4 0-19 0 rises20-29 1 rises30-40 2 rises40 3 "Jacob Skaria" wrote: Try this =LOOKUP(ABS(your formula here),{0,20,31,41},{0,1,2,3,4}) If this post helps click Yes --------------- Jacob Skaria "ferde" wrote: I'm not sure how to approach this and would appreciate some direction. Can I use an IF formula with Vlookup? A 1 Scores 2 150 3 100 4 140 5 120 6 200 First I need to calculate the difference between two numbers in column A. For example the difference between A6 and A5. Once I have determined this value I thought I would use a Vlookup table to assign a grade in column B but I'm not certain if this will work because sometimes the difference between the two numbers may be a negative number. For Example: If the value falls 20-30 points I can assign a Grade of 2 If the value falls 31-40 points I can assign a Grade of 3 If the value is changes < 20 points in either direction I can assign a Grade of 0 If the value between two cells rises 20-30 points I can assign a Grade of 2 If the value between two cells rises 31-40 points I can assign a Grade of 3 If the value between two cells rises 41 points I can assign a Grade of 4 Thank you in advance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLookUp - Does the VLookUp return the exact information? | Excel Worksheet Functions | |||
Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |