From: Laurent <lutgenl@icloud.com>
Subject: Some advice required [OT]
Date: Mon, 27 Dec 2021 01:21:50 -0800 (PST) [thread overview]
Message-ID: <7bede061-4b0f-4029-beb1-1056637e57d6n@googlegroups.com> (raw)
Hi all
My problem is not directly related to Ada but on how to solve it in general.
Also writing via the web interface of google groups :(
I have to do statistics on the results of antimicrobial susceptibility testings.
I have to keep only one strain/patient and the most resistant one.
Until now I have been doing it manually by staring for hours at Excel sheets.
I am trying to get it automated but I don't know how to solve my problem.
I have tried calculating a checksum from the results but I have cases
which are unclear/collide.
The result for a strain is one row, the results are in columns.
I treat the results in blocks of 3.
S has a value of 1, I =2 and R=3, empty cells = 0
without weight SRS (1+3+1) and RSS (3+1+1) or SSR (1+1+3) give both 5
I would have to keep the 3 because they are different.
I thought that weighting the position would solve the collisions
but nope.
The first cell has a value of 1, 2nd of 2 and 3rd of 3.
S has a value of 1, I =2 and R=3, empty cells = 0
with weight RRS (1*3+2*3+3*1) and SSR (1*1 + 2*1+3*3) give both 12
Is there a better way doing this?
What I have so far as VBA code:
Public Function Test_Checksum(rng_Range As Range) As String
Dim rng_Cell As Range
Dim int_Counter As Integer
Dim str_Result As String
Dim i As Long
int_Counter = 1
For Each rng_Cell In rng_Range
If rng_Cell.Value = "S" Then
i = i + 1 * int_Counter
ElseIf rng_Cell.Value = "I" Then
i = i + 2 * int_Counter
ElseIf rng_Cell.Value = "R" Then
i = i + 3 * int_Counter
Else
--empty cell
i = i + 0
End If
If int_Counter = 3 Then
int_Counter = 0
If i < 9 Then
str_Result = str_Result & "0" & CStr(i)
Else
str_Result = str_Result & CStr(i)
End If
i = 0
End If
int_Counter = int_Counter + 1
Next rng_Cell
--if the loop terminates but i <> 3
If i < 9 Then
str_Result = str_Result & "0" & CStr(i)
Else
str_Result = str_Result & CStr(i)
End If
Test_Checksum = str_Result
End Function
Thank you very much
Kind regards
Laurent
next reply other threads:[~2021-12-27 9:21 UTC|newest]
Thread overview: 25+ messages / expand[flat|nested] mbox.gz Atom feed top
2021-12-27 9:21 Laurent [this message]
2021-12-27 11:16 ` Some advice required [OT] Niklas Holsti
2021-12-27 12:29 ` Laurent
2021-12-27 13:14 ` Ben Bacarisse
2021-12-27 18:24 ` Laurent
2021-12-27 19:51 ` Dennis Lee Bieber
2021-12-27 20:49 ` Ben Bacarisse
2021-12-27 22:09 ` Laurent
2021-12-28 0:29 ` Ben Bacarisse
2021-12-28 7:48 ` Laurent
2021-12-28 9:05 ` Laurent
2021-12-28 12:54 ` Laurent
2021-12-28 13:57 ` Ben Bacarisse
2021-12-28 18:19 ` Laurent
2021-12-28 13:43 ` Ben Bacarisse
2021-12-28 16:49 ` Dennis Lee Bieber
2021-12-29 4:20 ` Randy Brukardt
2021-12-27 17:41 ` Dennis Lee Bieber
2021-12-27 18:56 ` Niklas Holsti
2021-12-27 19:44 ` Laurent
2021-12-28 2:10 ` Randy Brukardt
2021-12-28 6:02 ` Laurent
2021-12-29 3:58 ` Randy Brukardt
2021-12-27 17:18 ` Simon Wright
2021-12-27 18:30 ` Laurent
replies disabled
This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox