If value between 2 numbers
I am working on an inventory spreadsheet. The formula I need help with is IF("VLOOKUP VALUE") is between 1-19 then "LOW"; IF "VALUE" between 20-49 then "MEDIUM"; IF "VALUE" between 50-74 then "HIGH"; IF "VALUE" = 75 then "HEAVY".
I hope that makes sense. Thanks. |
Quote:
If always whole numbers then a simple VLOOKUP table would work. If decimals then a long winded IF AND nested formula would be needed. |
Quote:
|
Quote:
You nest your current VLOOKUP inside another one. So where a VLOOKUP starts =vlookup(a1,range... Etc, you replace the A1 with your current VLOOKUP. Does that make sense? You can either use ,false) at the end meaning you'd need a lookup table with each possible value or a ,true) at the end meaning you'd only have to have one entry for each low, med high etc. If you can post an example workbook here or email it to me at pubnut @ gmail . com I will show you what I mean as soon as I get home to my pc. |
Quote:
|
Quote:
|
If value between 2 numbers
On Monday, December 10, 2012 8:03:37 AM UTC-8, CMoHorn wrote:
I am working on an inventory spreadsheet. The formula I need help with is IF("VLOOKUP VALUE") is between 1-19 then "LOW"; IF "VALUE" between 20-49 then "MEDIUM"; IF "VALUE" between 50-74 then "HIGH"; IF "VALUE" = 75 then "HEAVY". I hope that makes sense. Thanks. -- CMoHorn Hi CMoHorn, See if this works for you. Where your lookup value is in C1. =LOOKUP(C1,{1,20,50,75},{"Low","Med","High","Heavy "}) HTH Regards, Howard |
If value between 2 numbers
CMoHorn has brought this to us :
I am working on an inventory spreadsheet. The formula I need help with is IF("VLOOKUP VALUE") is between 1-19 then "LOW"; IF "VALUE" between 20-49 then "MEDIUM"; IF "VALUE" between 50-74 then "HIGH"; IF "VALUE" = 75 then "HEAVY". I hope that makes sense. Thanks. Assuming "VLOOKUP VALUE" is the result of a formula in A1, try... =IF(AND($A10,$A1<20),"LOW",IF(AND($A119,$A1<50), "MEDIUM",IF(AND($A149,$A1<75),"HIGH",IF($A174,"H EAVY","")))) -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
If value between 2 numbers
on 10/12/2012, supposed :
=LOOKUP(C1,{1,20,50,75},{"Low","Med","High","Heavy "}) Howard, Exactly what I would do in a real project because it's more efficient (and self-explanatory to me). I chose to follow the OP's logic combining IF/AND functions for learning benefit (hopefully).<g -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
If value between 2 numbers
Actually, my above statement is not entirely true. I like a clean
worksheet and so try to avoid looking at errors returned by formulas. My first offering accomplishes this if the lookup value is outside the criteria range[s]. The way I'd use LOOKUP normally would to wrap it in an IF function so it returns an empty string on error... =IF(ISERROR(LOOKUP($A1,{1,20,50,75},{"low","med"," high","heavy"})),"",LOOKUP($A1,{1,20,50,75},{"low" ,"med","high","heavy"})) ...and so we lose formula brevity. I this OP's scenario, outside the range is any value <1. IMO, it's better to *not* have errors propagate to other cells containing formulas that ref cells with formulas that return errors! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
If value between 2 numbers
On Monday, December 10, 2012 12:06:45 PM UTC-8, GS wrote:
on 10/12/2012, supposed : =LOOKUP(C1,{1,20,50,75},{"Low","Med","High","Heavy "}) Howard, Exactly what I would do in a real project because it's more efficient (and self-explanatory to me). I chose to follow the OP's logic combining IF/AND functions for learning benefit (hopefully).<g -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion Hi Gary, "...combining IF/AND functions for learning benefit (hopefully).<g " When it comes to that many IF/AND's (and let's not forget OR) I struggle. I should take your gentle advise and become more adept with them, but it is way to easy go the LOOKUP route. Howard |
If value between 2 numbers
On Mon, 10 Dec 2012 16:03:37 +0000, CMoHorn wrote:
I am working on an inventory spreadsheet. The formula I need help with is IF("VLOOKUP VALUE") is between 1-19 then "LOW"; IF "VALUE" between 20-49 then "MEDIUM"; IF "VALUE" between 50-74 then "HIGH"; IF "VALUE" = 75 then "HEAVY". I hope that makes sense. Thanks. And here is another that assumes VALUE will always be an integer, and that you have not defined what you want returned if VALUE is not a number =1: =IF(OR(A1<1,NOT(ISNUMBER(A1))),"undefined",IF(A1<2 0,"LOW",IF(A1<50,"MEDIUM",IF(A1<75,"HIGH","HEAVY") ))) |
All times are GMT +1. The time now is 03:27 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com