Question on assigning a qualitative value based on quantitative info from members of the same group
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!
Answers
-
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?
-
Hi David, the second thought is on the right track
-
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?
-
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!
-
No problem. Glad it worked out.
-
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.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.9K Get Help
- 429 Global Discussions
- 147 Industry Talk
- 487 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 151 Just for fun
- 74 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!