# Question on assigning a qualitative value based on quantitative info from members of the same group

Options
✭✭✭✭

Hi, I am looking for help on crafting a formula that would allow me to discriminate between [Type] -> "Dna/Seq". Right now, I know that they are either DNA plate barcodes, or Sequencing plate barcodes based on the "pcr" prefix. However, I would like to be exact with these and say it is either DNA, or Seq. It is fairly straightforward, as the smaller barcode number (ie 9615) will always be the DNA plate, while the larger of the two (ie 9617) will always be the Sequencing plate. This comparison must be done between identical values in the [source_order_reference] column. Thank you!

Tags:

• Overachievers Alumni
Options

I'm just trying to clarify the problem a bit.

Would it be acceptable logic to say "If the barcode starts with pcr and the next row's bardcode also starts with pcr, then this is a DNA plate. Otherwise it is a Sequence plate"?

Or would it have to be "If the barcode is pcr and the source # matches the source # on the next row, then it is a DNA plate, otherwise it is a sequence plate"?

Do either of those thoughts track?

• ✭✭✭✭
Options

Hi David, the second thought is on the right track

• Overachievers Alumni
Options

This formula might take a bit of explaining.

=IF(LEFT(barcode@row, 3) = "pcr", IF(AND(Source@row = Source2, LEFT(barcode@row, 3) = LEFT(barcode2, 3)), "DNA", "Seq"), "N/A")

So first it checks to see if the barcode is pcr. If so, then it checks to see if the source number matches the next source number (the formula starts in row 1 for this to work so the next source is Source2) AND that the current barcode is pcr and the next barcode is pcr (you could be more explicit in this and say that both LEFT have to match "pcr", but I just did a compare to make sure they both match each other). If all of that is true, then DNA, else if the barcode was PCR, then it is a Seq plate. I just put N/A in the false, but that's where you would put checks for other types of plates.

Would that work?

• ✭✭✭✭
Options

Ah that is straightforward, I was expecting something a lot harder as I didn't notice that the way the data is displayed is that the DNA plate is always listed before the Seq plate. Thanks!

• Overachievers Alumni
Options

No problem. Glad it worked out.

• ✭✭✭✭
edited 09/18/20
Options

Ah, but the data isn't always in that sort of order where the 2 are matched together, so this doesn't actually work. I changed my SQL query to make it so though, so should be good to go.

• Overachievers Alumni
Options

Nice! I'm sure we could come up with something to handle unsorted date, it just might be an uglier version of the formula.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!