If And Drop Down

Options
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 ✭✭✭✭
    Options

    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

  • Aash
    Options

    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

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    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?

  • Aash
    Options
  • Genevieve P.
    Genevieve P. Employee Admin
    edited 11/03/22
    Options

    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

  • Aash
    Options

    i would do a drop down list of each no.

    0.01

    0.02

    0.03

    0.04

    0.05

    0.07

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    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

  • Aash
    Options

    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

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    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?

  • Aash
    Options

    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

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    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!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!