FAQ 
Calendar 


#1




Algorithm needed: 126=AZ, then AAAZ, BABZ...ZZAZZZ, AAAA, etc.
I'm trying to set up a calc formula that will reference a number field and convert it to letters ("alphabase", or base 26).
I've got a perfectly splendid simple little algorithm that works fine for anything I throw at it, of any size  except that it treats "Z" as zero, creating some counterintuitive ordinal patterns like "AW, AX, AY, BZ, BA". Then I've got three different calc formulas that try to wrestle the results into the desired ordinal pattern (AZ, then AA, AB...AZ, then BA, BB...BZ, CACZ, etc to YAYZ, ZAAA, then AAA, AAB...AAZ...AZZ, BAA...ZZY...ZZZ, AAAA, you get the picture). I will post the one that "runs" correctly the longest, generating its first undesired results at 35828 (which in alphabase = 2 1 0 0 where each column is 26 times the value of the column to its right), at which point it yields BZYZ instead of AYYZ following AYYY . I don't care for the code itself, though  it has more brute force than elegance about it if you know what I mean. I'm getting errors in the 676's column as well as the 17576's column at 2 1 0 0 and have yet to get far enough into checking and debugging to even look at columns yet farther to the left. I'm convinced there must be a simple and reliable formula that for some reason I've simply failed to visualize, so if you know one, post one! Thanks! (I'm doing this in FileMaker but a lot of techniques are portable between environments) Note about the Choose function: "Choose" is a zeroindexed function, e.g., Choose(weekday, "zilch", "sunday", "monday", "tuesday", "wednesday", "thursday", "friday", "saturday") returns "saturday" when weekday=7 and returns "sunday" when weekday=1; if you put 0 in for weekday you get back "zilch". Code:
SN: original (decimal) serial number Units: Mod(SN, 26) A 26s: Int(Mod( SN/26, 26)) A 676s: Int(Mod( SN/676, 26)) A 17576s: Int(Mod( SN/17576, 26)) A 456976s: Int(Mod( SN/456976, 26)) Conversion to Alpha formula (the ugly brute force thing): Case(SN¾475254, "", Choose(A 456976sCase(A 456976s=0, 1) , "Z", "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y")) & Case(SN¾18278,"", A 17576s=0 and A 676s=0, "Y", Choose(A 17576sCase(A 676s=0, 1) , "Z", "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y")) & Case(SN¾702,"", (SN>17602 and SN<18279), "Z", A 676s=0, "Y", Choose(A 676sCase(A 26s=0 or (A 26s=1 and Units=0), 1) , "Z", "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y")) & Case(SN¾26,"", A 26s=0 and Units=0, "Y", Choose(A 26sCase(Units=0, 1) , "Z", "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y")) & Choose(Units , "Z", "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y")
__________________
Disable Similes in this Post  Omar Little suggests lunatics like me include a disclaimer 
#2




In the leftmost column, ' ' is 0, 'A' is 1, 'B' is 2, etc. In any other column, 'A' is 0, 'B' is 1, etc.
So you have to determine the number of columns first. 
#3




#!/usr/bin/perl
I just wrote up this perl script. It's recursive and should convert any number of any length into the AZ format you want. I don't know Filemaker. Notes on syntax: the "." is the string catenation operator. "my" creates a local variable. I think this is what you want right? $ALPHA = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"; print "The alpha value of the number is " . base26($ARGV[0]) . "\n"; sub base26 { my $n = shift; my $d = int($n/26); my $r = $n % 26; if ($d != 0) { $alpha = base26($d) . substr($ALPHA,$r,1); return $alpha; } else { return substr($ALPHA,$r,1); } } 
#4




It's base 26. Use Horner's method with A = 1, ..., Z = 26.
Horner's method is used to quickly evaluate a polynomial a_{n}x^{n} + ... + a_{0} at a particular value of x. Here's pseudocode: p = a_{n} for n  1 > i > 0 p = p * 26 p = p + a_{i} That'll do it. 


#5




Re: Algorithm needed: 126=AZ, then AAAZ, BABZ...ZZAZZZ, AAAA, etc.
Quote:
It uses the fairly common idiom for printing base N values by constructing the value from least to greatest significant digit in an array, and then reversing it for presentation (in this case, returing it as a string). Code:
#include <iostream> #include <string> #include <vector> #include <algorithm> #include <iterator> std::string conv(unsigned long val) { static const char digits[] = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"; std::vector<char> buf; buf.push_back(digits[val % 26]); val /= 26; if (val) { do { val; buf.push_back(digits[val % 26]); val /= 26; } while (val); } // copy constructed value into string. // by iterating through the buffer in reverse order, the // resulting string will be in the correct order. std::string s; std::copy(buf.rbegin(), buf.rend(), std::back_inserter(s)); return s; } 
#6




Quote:
Here's pseudocode. A is an array of sufficient length, d is the input, and % is the modulus operator. while d != 0 A_{i} = d % 26 d = d / 26 Then just print the entries of A backwards. Keep a counter so that you know where to start, and print 0 if d is 0. This is what John T. Conklin did, but the pseudocode may be easier to adapt than C++ code. 
#7




It's so nice to feel needed!

#8




I could do this, but only on the TI82.

#9




Careful  it isn't exactly base 26, or base anything. There is no 0 placeholder, so you have 26 ** 2 two digit "numbers", not 25*26, and so on (ie, AA means something distinct from A, AB != B, and so on  ZZ = 702, not 26**2). I've done this thing a couple of times and always had to play a bit to get it to work right. The "val" in John T. Conklin's routine is crucial. A display routine I'm currently using:
Code:
private static void alphaseq0(int n, String alphabet, StringBuffer buf) { int len = alphabet.length(); if (n >= len) { alphaseq0(n/len  1,alphabet,buf); n = n % len; } buf.append(alphabet.charAt(n)); } 


#10




Got it
bup:
Quote:
The key was learning how to scale this portion of the formula for 26s so as to apply the same logic to the columns farther leftwards: Case(Units=0, 1) This case statement describes how much (if anything) to subtract from the raw base26 value in order to get the correct Choose digit for obtaining the letter. I knew it had to scale somehow but I didn't have a sense or feel for how the pattern would develop. Here's the Case statements that worked: Code:
for 676s: Case(A 26s=0 or A 26s*26+Units<27, 1) for 17576s: Case(A 676s=0 or A 676s*676 +A 26s*26+Units<703, 1) for 456976s: Case(A 17576s=0 or A 17576s*17576+A 676s*676+ A 26s*26+Units<18279, 1) for 11881376s: Case(A 456976s=0 or A 456976s*456976+A 17576s*17576+ A 676s*676+A 26s*26+Units<475255, 1)
__________________
Disable Similes in this Post  Omar Little suggests lunatics like me include a disclaimer 
#11




I want to thank the rest of you folks as well. Some of the procedures described would work in FileMaker as a verb (i.e., as a Script) but I wanted a noun (i.e., a Field Definition).

Reply 
Thread Tools  
Display Modes  

