If value between 2 numbers

[CMoHorn]

I am working on an inventory spreadsheet. The formula I need help wit
is IF("VLOOKUP VALUE") is between 1-19 then "LOW"; IF "VALUE" betwee
20-49 then "MEDIUM"; IF "VALUE" between 50-74 then "HIGH"; IF "VALUE" =
75 then "HEAVY".

I hope that makes sense. Thanks

 

[lhkittle]

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.

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

 

[GS]

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($A1>0,$A1<20),"LOW",IF(AND($A1>19,$A1<50),"MEDIUM",IF(AND($A1>49,$A1<75),"HIGH",IF($A1>74,"HEAVY",""))))

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[GS]

=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

 

[GS]

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

 

[lhkittle]

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

 

[GS]

(e-mail address removed) brought next idea :

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

I agree, and as I said to Howard it's how I would do it, just for the
simplicity. Did you note my 2nd version to address errors if the lookup
value cell is empty OR contains a value <1?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Ron Rosenfeld]

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<20,"LOW",IF(A1<50,"MEDIUM",IF(A1<75,"HIGH","HEAVY"))))