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 |
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 |
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 |
All times are GMT +1. The time now is 06:13 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com