If And Drop Down
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
-
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!
-
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?
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Hi in between
-
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
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
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
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
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?
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
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!
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!