Reply to Thread

Message:: [QUOTE="dursin, post: 561809, member: 17425"]Went back and fixed a small error in my one line Excel calculation and after staring at it for a while, came up with an alternate way to calculate the plate position which is a little shorter. 1) Subtract 1 from serial number2) Divide result from step 1 by number of sheets per run and round down.3) Divide result from step 2 by number of notes printed per sheet and take the remainder. This should give you the offset within the plate of the note from 0 to 31.  Using the prior example. 1) 87654321 - 1 = 876543202) 87654320 / 200000 = 438.2716 rounded down to 4383) 438.2716 / 32 = 13, remainder 22 is offset to note within plate. To convert the 22 to the actual letter/number pair divide the result from step 8.   Take the remainder and add to letter "A" to get the letter and then take the quotient + 1 to get the number. Example: 22 / 8 = 6, remainder 2.  So, "A"+6 = "G", 2+1 = 3, Plate position is G3. Haven't tested it with all numbers yet, but seems to produce accurate results.  In a single cell Excel formula, would look like this: =CHAR(CODE("A")+MOD(MOD(INT((B2-1)/200000),32),8))&INT(MOD(INT((B2-1)/200000),32)/8+1) Not too pretty and does some of the work twice.  If you break it down the steps though it becomes clearer.  In Excel, you could put in the following values: B2 = The serial number of noteB3 = B2 - 1B4 = INT( B2 / 200000 )B5 = MOD( B4 , 32 )B6 = CHAR( CODE( "A" ) + MOD( B5 , 8) ) & INT( B5 / 8 ) + 1 Note: The MOD() function gives remainder.  Also, if your version of Excel or Google documents supports the QUOTIENT() function, the cells B4 and B6 could be re-written as: B4 = QUOTIENT( B2 , 200000 )B6 = CHAR( CODE( "A" ) + MOD( B5 , 8) ) & QUOTIENT( B5 , 8 ) + 1[/QUOTE]

Create my FREE Account!

Log in or Sign up

Reply to Thread

Useful Searches