|
Multiple If and Else if
Hi All,
I need some help with mulitple condtion using if and else if(If possible) otherwise I am open for better ideas. I have 2 columns A & B (These number are just estimated points it will be changed) Column A Column B < 70 5 Points 70-80 15 Points 80-85 14 Points 85-90 13 Points 90-95 12 Points 95-100 12 Points 100 10 Points 100-110 8 Points 110-120 7 Points 120 5 Points Based on Column A, I want the formula in Column B to generate the points. I hope I am not asking that cannot be done :-) Please if someone can help. Thanks in Advance AM |
You can do it with If functions, though I recommend a VLookup...
Enter the following table... Col G , Col H 0, 5 80, 15 85, 14 90, 13 95, 12 100, 10 101, 8 110, 7 121, 5 then in cell B1, enter the following formula ... =VLOOKUP(A1,G1:H9,2) Now in cell A1, type in a value & see the score in B1 update. Hope this helps - Steve. "Andy" wrote in message ... Hi All, I need some help with mulitple condtion using if and else if(If possible) otherwise I am open for better ideas. I have 2 columns A & B (These number are just estimated points it will be changed) Column A Column B < 70 5 Points 70-80 15 Points 80-85 14 Points 85-90 13 Points 90-95 12 Points 95-100 12 Points 100 10 Points 100-110 8 Points 110-120 7 Points 120 5 Points Based on Column A, I want the formula in Column B to generate the points. I hope I am not asking that cannot be done :-) Please if someone can help. Thanks in Advance AM |
=LOOKUP(A1,{0,5;70,15;80,14;85,14;90,13;95,12;100, 10;101,8;110,7;120,5})
-- HTH RP (remove nothere from the email address if mailing direct) "Andy" wrote in message ... Hi All, I need some help with mulitple condtion using if and else if(If possible) otherwise I am open for better ideas. I have 2 columns A & B (These number are just estimated points it will be changed) Column A Column B < 70 5 Points 70-80 15 Points 80-85 14 Points 85-90 13 Points 90-95 12 Points 95-100 12 Points 100 10 Points 100-110 8 Points 110-120 7 Points 120 5 Points Based on Column A, I want the formula in Column B to generate the points. I hope I am not asking that cannot be done :-) Please if someone can help. Thanks in Advance AM |
That's a good tip Bob, I didn't even realise you could list in-place (so to speak).
I'll have to remember that one! NOTE to Andy, If you change your points scoring system, then you have to change every instance of this, If you use a table (as in the answer I submitted) then you only change the points in the table once. "Bob Phillips" wrote in message ... =LOOKUP(A1,{0,5;70,15;80,14;85,14;90,13;95,12;100, 10;101,8;110,7;120,5}) -- HTH RP (remove nothere from the email address if mailing direct) "Andy" wrote in message ... Hi All, I need some help with mulitple condtion using if and else if(If possible) otherwise I am open for better ideas. I have 2 columns A & B (These number are just estimated points it will be changed) Column A Column B < 70 5 Points 70-80 15 Points 80-85 14 Points 85-90 13 Points 90-95 12 Points 95-100 12 Points 100 10 Points 100-110 8 Points 110-120 7 Points 120 5 Points Based on Column A, I want the formula in Column B to generate the points. I hope I am not asking that cannot be done :-) Please if someone can help. Thanks in Advance AM |
Stevie, You are great.
It worked like a piece if cake. Thank you Sir AM "Stevie_mac" wrote: You can do it with If functions, though I recommend a VLookup... Enter the following table... Col G , Col H 0, 5 80, 15 85, 14 90, 13 95, 12 100, 10 101, 8 110, 7 121, 5 then in cell B1, enter the following formula ... =VLOOKUP(A1,G1:H9,2) Now in cell A1, type in a value & see the score in B1 update. Hope this helps - Steve. "Andy" wrote in message ... Hi All, I need some help with mulitple condtion using if and else if(If possible) otherwise I am open for better ideas. I have 2 columns A & B (These number are just estimated points it will be changed) Column A Column B < 70 5 Points 70-80 15 Points 80-85 14 Points 85-90 13 Points 90-95 12 Points 95-100 12 Points 100 10 Points 100-110 8 Points 110-120 7 Points 120 5 Points Based on Column A, I want the formula in Column B to generate the points. I hope I am not asking that cannot be done :-) Please if someone can help. Thanks in Advance AM |
Thanks Bob
AM "Bob Phillips" wrote: =LOOKUP(A1,{0,5;70,15;80,14;85,14;90,13;95,12;100, 10;101,8;110,7;120,5}) -- HTH RP (remove nothere from the email address if mailing direct) "Andy" wrote in message ... Hi All, I need some help with mulitple condtion using if and else if(If possible) otherwise I am open for better ideas. I have 2 columns A & B (These number are just estimated points it will be changed) Column A Column B < 70 5 Points 70-80 15 Points 80-85 14 Points 85-90 13 Points 90-95 12 Points 95-100 12 Points 100 10 Points 100-110 8 Points 110-120 7 Points 120 5 Points Based on Column A, I want the formula in Column B to generate the points. I hope I am not asking that cannot be done :-) Please if someone can help. Thanks in Advance AM |
An in-line array, gets useful for multi-condition tests.
You would only change one, then copy down for the rest. Not arguing against a table, just pointing out that it is not that onerous. More importantly, if there are many instances of that type formula (the non-table type), it is less efficient and consumes more workbook. -- HTH RP (remove nothere from the email address if mailing direct) "Stevie_mac" wrote in message ... That's a good tip Bob, I didn't even realise you could list in-place (so to speak). I'll have to remember that one! NOTE to Andy, If you change your points scoring system, then you have to change every instance of this, If you use a table (as in the answer I submitted) then you only change the points in the table once. "Bob Phillips" wrote in message ... =LOOKUP(A1,{0,5;70,15;80,14;85,14;90,13;95,12;100, 10;101,8;110,7;120,5}) -- HTH RP (remove nothere from the email address if mailing direct) "Andy" wrote in message ... Hi All, I need some help with mulitple condtion using if and else if(If possible) otherwise I am open for better ideas. I have 2 columns A & B (These number are just estimated points it will be changed) Column A Column B < 70 5 Points 70-80 15 Points 80-85 14 Points 85-90 13 Points 90-95 12 Points 95-100 12 Points 100 10 Points 100-110 8 Points 110-120 7 Points 120 5 Points Based on Column A, I want the formula in Column B to generate the points. I hope I am not asking that cannot be done :-) Please if someone can help. Thanks in Advance AM |
| All times are GMT +1. The time now is 03:22 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com