If And Drop Down

Aash
Aash
edited 09/20/22 in Formulas and Functions

Hi i have 3 column's,

col 1 = Small Diamond Size, "0.01-0.07"

col 2 = Smalls Diamond Grade, "IJ"

IF ABOVE TWO THEN Col 3 = 400

col 1 = Small Diamond Size, "0.01-0.07"

col 2 = Smalls Diamond Grade, "GH"

IF ABOVE TWO THEN Col 3 = 500

I have tried this - it does not work please help

=IF(AND([Small Diamond Size]@row = "0.01-0.07"; [Smalls Grade]@row = "IJ"; "400"; IF(AND([Small Diamond Size]@row = "0.01-0.07"; [Smalls Grade]@row = "GH"; "500"))))

Answers

  • SmartLew
    SmartLew ✭✭✭✭

    This should work for you

    =IF(AND([small diamond size]@row = "0.01-0.07", [smalls grade]@row = "IJ"), "400", IF(AND([small diamond size]@row = "0.01-0.07", [smalls grade]@row = "GH"), "500"))

    I'm passionate about helping you leverage the truly awesome power of smartsheet!

    https://www.fiverr.com/smartlew

  • thank you very much this worked.

    However i am trying to multiply the result in col 3 by col 4 (small diamond qty)

    =[$Price/ct]@row * [Qty Small Diamond]@row

    #invalid operation

    please assist

  • Hi @Aash

    The values you have in the formula above are being output as text values (e.g. "0.01-0.07") versus numerical. This means they cannot be multiplied by anything.

    We can change this to be numerical values, but then you can only select one value per output. So for "0.01-0.07", do you want that to be 0.01 or 0.07? Or something inbetween?

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

  • Genevieve P.
    Genevieve P. Employee
    edited 11/03/22

    Hi @Aash

    Can you specify which number in between? You can only choose one value to multiply by:

    0.01, 0.02, 0.03, 0.04, 0.05, 0.06, or 0.07

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

  • i would do a drop down list of each no.

    0.01

    0.02

    0.03

    0.04

    0.05

    0.07

  • Hi @Aash

    Can I clarify what it is you're wanting to multiply it by? Reviewing the formula again, it seems like your column 3 will only ever have "400" or "500" in it, is that correct?

    If so, you'll want to adjust your formula to remove quotes from the numerical output. Try this:

    =IF(AND([small diamond size]@row = "0.01-0.07", [smalls grade]@row = "IJ"), 400, IF(AND([small diamond size]@row = "0.01-0.07", [smalls grade]@row = "GH"), 500))


    However based on your newest description, it sounds like your column "Small diamond size" is actually a dropdown list, and not a text column with the word "0.01-0.07" in it.

    In this case, you'll want the IF formula to look to see if your value is less than 0.07 instead of equal to "0.01-0.07":

    =IF(AND([small diamond size]@row <= 0.07, [smalls grade]@row = "IJ"), 400, IF(AND([small diamond size]@row <= 0.07, [smalls grade]@row = "GH"), 500))


    If this hasn't helped, we'll need to know more information. It would be best if we could see a screen capture of your sheet, but please block out sensitive data.

    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

  • HI

    above diamond price table

    sales staff enters small diamond size in col 1 and enters grade in col 2. Smartsheet to calculate price in col3.

    Sales staff then enters small qty

    Smartsheet to calculate small diam cost

  • Hi @Aash

    Thank you for this example!

    I can see that your Column 1 has a lot more values than in your original statement. In order to get the end result in the final column, we first need to have the correct formula in your Column 3.

    Can you confirm if the image above shows all dropdown values from Column 1, and the correct "IF G/H" associated price for each value?

    For example, does your Column 1 have this:

    "0.01 - 0.07", "0.08 - 0.12"

    or this:

    0.01, 0.02, 0.03, 0.04... etc, until 0.29?

    Does it stop at 0.29?

    Can you list out all of the values you want to appear in Column 3, along with the criteria for that value?

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

  • Hi please see table below hope this explains and really appreciate your help


    col 1

    0.01, 0.02, 0.03, 0.04... etc, until 0.29

    col 3

    if col 2 = gh then price is ...

    we will require formula for col 5 and 6 as well

  • Hi @Aash

    Thank you! This definitely helps clarify what it is you're looking to do. It seems like you need a formula that's much more specific, which is no problem at all.


    Column 3 Formula

    For Column 3, you're looking to see if the value is between a certain range. Since we're looking for numbers, don't put these in quotes. Quotes turn numbers into text!

    For example, if we're looking for the range between 0.01 - 0.07, then this is how we'd structure that IF statement:

    =IF(AND([Column1]@row > 0, [Column1]@row <= 0.07),

    You're also looking for the text value in Column 2 though, so we would add this:

    =IF(AND([Column1]@row > 0, [Column1]@row <= 0.07, [Column2]@row = "IJ"), 480

    Now this is only the first instruction. You'll need to wrote out as many IF statements as you have outputs. What I mean is, for every different value you want to appear (480, 680, 760...) we need to write the instructions.

    Potential full formula:

    =IF(AND([Column1]@row > 0, [Column1]@row <= 0.07, [Column2]@row = "IJ"), 480, IF(AND([Column1]@row > 0.07, [Column1]@row <= 0.12, [Column2]@row = "IJ"), 680, IF(AND([Column1]@row > 0.12, [Column1]@row <= 0.17, [Column2]@row = "IJ"), 760, IF(AND([Column1]@row > 0.17, [Column1]@row <= 0.22, [Column2]@row = "IJ"), 850, IF(AND([Column1]@row > 0.22, [Column1]@row <= 0.29, [Column2]@row = "IJ"), 1100))))) 


    ^This assumes that your highest dropdown value in Column 1 would be 0.29. If Column 2 has something other than "IJ", then the formula will return a blank cell instead of a number. 


    Column 4 Fomula

    For Column 4, we can use the exact same formula but replace what value we're looking for in Column 2, and what the output would be:

    =IF(AND([Column1]@row > 0, [Column1]@row <= 0.07, [Column2]@row = "GH"), 570, IF(AND([Column1]@row > 0.07, [Column1]@row <= 0.12, [Column2]@row = "GH"), 790, IF(AND([Column1]@row > 0.12, [Column1]@row <= 0.17, [Column2]@row = "GH"), 930, IF(AND([Column1]@row > 0.17, [Column1]@row <= 0.22, [Column2]@row = "GH"), 1050, IF(AND([Column1]@row > 0.22, [Column1]@row <= 0.29, [Column2]@row = "GH"), 1250))))) 

    Since these formulas are now outputting numbers, you should be able to quickly do the other multiplications!

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!