Formula to convert numerical values in Column A with LOW/MED/HIGH
Hi there, I hope someone with a big brain can help me solve this issue I'm having.
Desired Result: I'm trying to fix a formula that will automatically input the Values "LOW", "MED", or "HIGH". I would like to be able to copy the three (corrected) formulas I have into each spreadsheet I need to work with, without having to create a lookup table)
Here's my sheet on google docs (I couldn't get it to upload in smartsheet for some reason)
https://docs.google.com/spreadsheets/d/1PoJ5J1lzDQ4gE0CXlWeL4zcSCjh0FnUOfl1re3LnJc/edit?usp=sharing
Layout
 In column A, I will have fields of numbers which I want to assign the values.
 Column B is where I have a calculation that's supposed to interpret column A's values into either "LOW", "MED", or "HIGH" relative to the calculated values in C3 and C4.
Problem one: All of my values in Column B are coming in as LOW or HIGH. No mid. I can't figure out how to make mid "Higher than C, but lower than D)
Here's the calculation I'm using:
=IF(A3<=$D$3,"LOW",IF(A3<=$D$3,"MID","HIGH"))
Problem two: The number of rows will depend on the sheet I'm working on.
I would like the calculations In C and D to automatically take into account all of the values in Column A, without me having to scroll to the bottom, figure out the number of rows, and handtype the number of rows into the calculation. Is there a way to do that?
Here are the calculations for C and D
=PERCENTILE($A$3:$A$200,1/3)
=PERCENTILE($A$3:$A$200,2/3)
Thanks in advance for your help!
Manny
Comments

=IF(A3<=$D$3,"LOW",IF(A3<=$D$3,"MID","HIGH"))
This is a redundant statement. If A <= D then Low Else
At the else we have said that A <= D is false. Which means that A > D. This means that there is no opportunity for MID to be returned. This statement can be simplified to
=if(A3 <= D3,"Low","High")
I don't really know what you want to have for the criteria for the returns so I can't be sure about the solution for you, but below is a possibility.
if(a3 < D3, "Low",if(a3 = D3, "Mid","High"))
If A3 is less than D3 then "Low", if A3 = D3 "Mid" if A3 > D3 "High"
for your second question a column reference in smartsheet is [Column Name]:[Column Name]. Omit the row numbers in a range reference and it will refer to the entire column
Help Article Resources
Categories
Check out the Formula Handbook template!