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

edited 12/09/19 in Formulas and Functions

Hi there, I hope someone with a big brain can help me solve this issue I'm having. laugh

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)


  • 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:


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



Thanks in advance for your help!





  • L_123
    L_123 ✭✭✭✭✭✭



    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!