I have a table. The table consists of an ID column ranging from 1 to 400. Column 2 ranges from 1 to 10, and column 3 ranges from 1 to 40. Here's my problem...
I need to mathematically resolve the correct column 2 and 3 values from column 1.
Example 1:
Col 1 is 40 Col 2 should be 1 Col 3 should be 40
Example 2:
Col 1 is 288 Col 2 should be 8 Col 3 should be 8
Example 3: Col 1 is 52 Col 2 should be 2 Col 3 should be 12
I thought I could do this with simple division and modulus values, but it breaks at the first\last value in a range (e.g. col 2 is 0 and col 3 is 11 at 400 (should be 10/400), or col 2 is 0 and col 3 at 40 (should be 1/1)). This should be super easy but I am brain farting all over the place. Too much methane.
Help is appreciated.
As mentioned in my comment above, had the columns gone from 0-399, 0-9 and 0-39 instead, then simple division and remainder would be exactly what you need. So, you can do all your calculations in that realm, then convert back afterwards, by first subtracting $1$ from every value that goes into a calculation, and then adding back $1$ at the end. (If you're afraid that your audience won't understand this, then you don't have to mention it; give them the formulas, and demonstrate that they work.)
If the value in column $1$ is $x_1$, then in column $2$ you want $$ x_2=\left\lfloor\frac{x_1-1}{40}\right\rfloor +1=\left\lceil\frac{x_1}{40}\right\rceil $$ and in column $3$ you would want $$ x_3=(x_1-1)-40(x_2-1) +1\\=x_1-40x_2+40 $$