Don't know how to write the formula?

[jlawyer55]

Here is what I need to do. in the example below I need Colum L (Velocity Code) to have a formula that if Colum K (Hit Count) has a number >250 it puts an "A" in the Velocity Code Colum. But if it is <250 but >100 it puts a "B", if it is <100 but > 50 it puts a "C", <50 but > 10 it puts a D and <10 it puts an "E".

Any help would be appreciated.

 

[Scott Huish]

Are they whole numbers or are there decimal amounts?

If whole numbers, then:

=LOOKUP(K1,{0,11,51,101,251},{"E","D","C","B","A"})

 

[jlawyer55]

This is what the sheet looks like;


[TABLE="width: 1135"]
<colgroup><col width="90" style="width: 68pt; mso-width-source: userset; mso-width-alt: 3291;"> <col width="94" style="width: 71pt; mso-width-source: userset; mso-width-alt: 3437;"> <col width="74" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2706;"> <col width="64" style="width: 48pt;"> <col width="70" style="width: 53pt; mso-width-source: userset; mso-width-alt: 2560;"> <col width="117" style="width: 88pt; mso-width-source: userset; mso-width-alt: 4278;"> <col width="235" style="width: 176pt; mso-width-source: userset; mso-width-alt: 8594;"> <col width="232" style="width: 174pt; mso-width-source: userset; mso-width-alt: 8484;"> <col width="204" style="width: 153pt; mso-width-source: userset; mso-width-alt: 7460;"> <col width="123" style="width: 92pt; mso-width-source: userset; mso-width-alt: 4498;"> <col width="68" style="width: 51pt; mso-width-source: userset; mso-width-alt: 2486;"> <col width="140" style="width: 105pt; mso-width-source: userset; mso-width-alt: 5120;"> <tbody>[TR]
[TD="width: 90, bgcolor: transparent"]Business Unit[/TD]
[TD="width: 94, bgcolor: transparent"]Short Item No[/TD]
[TD="width: 74, bgcolor: transparent"]Sales Code[/TD]
[TD="width: 64, bgcolor: transparent"]ROP[/TD]
[TD="width: 70, bgcolor: transparent"]Mfg. Code[/TD]
[TD="width: 117, bgcolor: transparent"]2nd Item Number[/TD]
[TD="width: 235, bgcolor: transparent"]Description[/TD]
[TD="width: 232, bgcolor: transparent"]Description Line 2[/TD]
[TD="width: 204, bgcolor: transparent"]Primary Location[/TD]
[TD="width: 123, bgcolor: transparent"]Total Quantity[/TD]
[TD="width: 68, bgcolor: transparent"]Hit Count[/TD]
[TD="class: xl65, width: 140, bgcolor: transparent"]Velocity Code[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1100[/TD]
[TD="bgcolor: transparent, align: right"]2167437[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]939[/TD]
[TD="bgcolor: transparent"]HOS[/TD]
[TD="bgcolor: transparent"]798303CS[/TD]
[TD="bgcolor: transparent"]Sodium Chloride 0.9% 500ml BG[/TD]
[TD="bgcolor: transparent"]INJ 24/cs[/TD]
[TD="bgcolor: transparent"]K-01[/TD]
[TD="bgcolor: transparent, align: right"]3397[/TD]
[TD="bgcolor: transparent, align: right"]1564[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1100[/TD]
[TD="bgcolor: transparent, align: right"]2211531[/TD]
[TD="bgcolor: transparent, align: right"]16[/TD]
[TD="bgcolor: transparent, align: right"]1273[/TD]
[TD="bgcolor: transparent"]3M[/TD]
[TD="bgcolor: transparent"]3M9640[/TD]
[TD="bgcolor: transparent"]Electrode Red Dot Adult LF[/TD]
[TD="bgcolor: transparent"]High Tack Adhesive 50/bx[/TD]
[TD="bgcolor: transparent"]H-14-A[/TD]
[TD="bgcolor: transparent, align: right"]4522[/TD]
[TD="bgcolor: transparent, align: right"]896[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1100[/TD]
[TD="bgcolor: transparent, align: right"]2498316[/TD]
[TD="bgcolor: transparent, align: right"]11[/TD]
[TD="bgcolor: transparent, align: right"]2287[/TD]
[TD="bgcolor: transparent"]MY[/TD]
[TD="bgcolor: transparent"]742130EA[/TD]
[TD="bgcolor: transparent"]Dexamethasone 4mg/ml 30ml MDV[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]D-22[/TD]
[TD="bgcolor: transparent, align: right"]6009[/TD]
[TD="bgcolor: transparent, align: right"]150[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1100[/TD]
[TD="bgcolor: transparent, align: right"]2495431[/TD]
[TD="bgcolor: transparent, align: right"]11[/TD]
[TD="bgcolor: transparent, align: right"]661[/TD]
[TD="bgcolor: transparent"]HOS[/TD]
[TD="bgcolor: transparent"]662902EA[/TD]
[TD="bgcolor: transparent"]Quelicin 20mg/ml 10ml[/TD]
[TD="bgcolor: transparent"]*Refrigerate*[/TD]
[TD="bgcolor: transparent"]RF-22[/TD]
[TD="bgcolor: transparent, align: right"]1688[/TD]
[TD="bgcolor: transparent, align: right"]802[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1100[/TD]
[TD="bgcolor: transparent, align: right"]2495967[/TD]
[TD="bgcolor: transparent, align: right"]11[/TD]
[TD="bgcolor: transparent, align: right"]635[/TD]
[TD="bgcolor: transparent"]WW[/TD]
[TD="bgcolor: transparent"]600610EA[/TD]
[TD="bgcolor: transparent"]Atropine Sulfate 0.4mg/ml 20ml[/TD]
[TD="bgcolor: transparent"]MDV[/TD]
[TD="bgcolor: transparent"]D-22[/TD]
[TD="bgcolor: transparent, align: right"]1960[/TD]
[TD="bgcolor: transparent, align: right"]50[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1100[/TD]
[TD="bgcolor: transparent, align: right"]2167357[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]445[/TD]
[TD="bgcolor: transparent"]HOS[/TD]
[TD="bgcolor: transparent"]795303CS[/TD]
[TD="bgcolor: transparent"]Lactated Ringer 500ml BG INJ[/TD]
[TD="bgcolor: transparent"]24/cs[/TD]
[TD="bgcolor: transparent"]K-03[/TD]
[TD="bgcolor: transparent, align: right"]1463[/TD]
[TD="bgcolor: transparent, align: right"]634[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1100[/TD]
[TD="bgcolor: transparent, align: right"]2498412[/TD]
[TD="bgcolor: transparent, align: right"]11[/TD]
[TD="bgcolor: transparent, align: right"]561[/TD]
[TD="bgcolor: transparent"]APP[/TD]
[TD="bgcolor: transparent"]061603EA[/TD]
[TD="bgcolor: transparent"]Amiodarone 50mg/ml 3ml VL[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]D-22[/TD]
[TD="bgcolor: transparent, align: right"]2136[/TD]
[TD="bgcolor: transparent, align: right"]8[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Scott Huish]

See my formula above your last post.

 

[jlawyer55]

Thank You very much