If And Drop Down

Aash

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"))))

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"))

Aash

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.

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

Hi in between

Genevieve P.

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

i would do a drop down list of each no.

0.01

0.02

0.03

0.04

0.05

0.07

Genevieve P.

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

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.

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

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.

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!