Formula to convert numerical values in Column A with LOW/MED/HIGH

Manny

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/1PoJ5J1lzDQ4gE0CXlW-eL4zcSCjh0FnUOfl1re3LnJc/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 hand-type 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

L_123

=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