I am beating my head against a wall on how I can rewrite this calc. I decided to brute force it with a case() and a lot of parameters (96). Anyone have a better way on handling this? I know its there, but it is just not coming to me atm.

The context of this is a science application plate mapping conversion. Samples are added to an instrument which in turns transfers them into a 96 well plate. the solution prepares the instrument files from a batch type relationship. I need both the input value for the initial setup (workflow) & the output value for the reference for future testing

defined:

rack & rack position are the initial position that the sample is loaded into the machine. in total there are 4 racks with 24 positions per rack.

the output is the well position number of a 96-well plate.

Let([

r=rack;

p=rackPos

];

Case(

r=1 and p=1; 1;

r=1 and p=2; 13;

r=1 and p=3; 25;

r=1 and p=4; 37;

r=1 and p=5; 49;

r=1 and p=6; 61;

r=1 and p=7; 73;

r=1 and p=8; 85;

r=1 and p=9; 2;

r=1 and p=10; 14;

r=1 and p=11; 26;

r=1 and p=12; 38;

r=1 and p=13; 50;

r=1 and p=14; 62;

r=1 and p=15; 74;

r=1 and p=16; 86;

r=1 and p=17; 3;

r=1 and p=18; 15;

r=1 and p=19; 27;

r=1 and p=20; 39;

r=1 and p=21; 51;

r=1 and p=22; 63;

r=1 and p=23; 75;

r=1 and p=24; 87;

r=2 and p=1; 4;

r=2 and p=2; 16;

r=2 and p=3; 28;

r=2 and p=4; 40;

r=2 and p=5; 52;

r=2 and p=6; 64;

r=2 and p=7; 76;

r=2 and p=8; 88;

r=2 and p=9; 5;

r=2 and p=10; 17;

r=2 and p=11; 29;

r=2 and p=12; 41;

r=2 and p=13; 53;

r=2 and p=14; 65;

r=2 and p=15; 77;

r=2 and p=16; 89;

r=2 and p=17; 6;

r=2 and p=18; 18;

r=2 and p=19; 30;

r=2 and p=20; 42;

r=2 and p=21; 45;

r=2 and p=22; 66;

r=2 and p=23; 78;

r=2 and p=24; 90;

etc....

)

)

example of relative sample position

e.x. rack 1 position 1 = A1

rack 2 position 1 = A4

rack 3 position 1 = A7

this is an example of the output value needed

e.x. Sample 1 in Rack 1 Position 1 is placed into well A1 or "1"

Sample 2 in rack 1 position 2 is placed in well B1 or "13"

So if Rows are numbered from 0 to n then the calculation is (Row x 12) + Plate Column number