The following outlines some tips to keep in mind when programming an algorithm:
 Increase decimals in Excel file cells until you only see 0's at the end. When pasting from Excel, the code editor will only get the value that is shown, not the background value. So if you don't increase decimals, Excel will know the value is .43197809873254 when we're only using .432. This is a big difference. We're responsible for carrying forward the precision of the "typing tool" (another term often used to mean algorithm).
 Always make sure the client provides an unlocked or otherwise clearly transparent Excel sheet that shows the variables' formulas and relationships. Without this, our hands are tied. The client should understand that we can't be 100% sure to recreate the algorithm correctly.
 Try rightclicking sheetnames to unhide any hidden sheets.
 Another technique is to click Ctrl+Page Up or Down to go to other sheets (useful when the sheet tabs are hidden)
 Use a barebones outline at first and then copy it for all your seg variables. If you follow a samewidth format for all lines, it's easy to paste in the decimals and constant directly from the Excel via column mode. This means fewer copy/paste steps and less room for error. E.g.
SEG1. Thrifty Thadeus type: hidden datatype: number cvalue: <<END ($QC1AR57 * PASTE COEFFICIENT FROM EXCEL HERE ) + ($QC1AR36 * PASTE COEFFICIENT FROM EXCEL HERE ) + ($QC1AR56 * PASTE COEFFICIENT FROM EXCEL HERE ) + ($QC1AR34 * PASTE COEFFICIENT FROM EXCEL HERE ) + ($QC1AR35 * PASTE COEFFICIENT FROM EXCEL HERE ) + ($QC1AR60 * PASTE COEFFICIENT FROM EXCEL HERE ) + ($QC1AR14 * PASTE COEFFICIENT FROM EXCEL HERE ) + ($QC1AR27 * PASTE COEFFICIENT FROM EXCEL HERE ) + ($QC1AR49 * PASTE COEFFICIENT FROM EXCEL HERE ) + ($QC1AR2 * PASTE COEFFICIENT FROM EXCEL HERE ) + ($QC1AR11 * PASTE COEFFICIENT FROM EXCEL HERE ) + PASTE CONSTANT FROM EXCEL HERE END
NOTE: Note the placeholder for a constant at the end.
 Follow the breadcrumbs of all the formulas in the Excel file to make sure your design accounts for the various pieces you'll need. Sometimes standard deviation, mean, recentering (i.e. converting values to some other range), etc. is needed but not mentioned in the accompanying Word document.
 Client might send test data. You can create a selftesting survey with the algorithm as follows:
 Create the survey putting all variables on one page with
autoprocess:y
.  Upload all the IDs with their question values.
 Include for each ID a QNEXT variable which contains the ID of the next record in line (in the Excel file)
 The next page will contain a way to redirect to the same survey but with $QNEXT as the trans_id in the URL. The current ID's segment has been calculated and the next ID takes over, repeating until done. Set up so that no redirect happens if $QNEXT eq "".
 When done, download the segments. Compare them to the client's dummy data segments. Do all match?
 If they have a spreadsheet to check answers in bulk, you can make your own dummy data file. Use =randbetween() in Excel to create dummy answers. Then copy/paste special (As Values) to remove the formula so the randomizer doesn't keep changing things.
 Compare decimals in intermediate variables' b/n Excel and survey. (i.e., Take the survey the same way in both and make sure the variables doing the math are getting the same answers)
 Create the survey putting all variables on one page with
 Standard deviation,
=STDEV()
in Excel, is calculated in a slightly special way. Read about standard dev. on Wikipedia and you'll see many formulas. The right way is usually as follows: Find the mean of all n values
 Subtract mean from all values one by one
 Square all these. In perl, x squared is x**2.
 Sum all these
 Divide this sum by n MINUS 1. (This is how std. dev. is sometimes different)
 Get the square root of all this. This is done in perl by setting it to the power of 1.2. I.e., x**0.5
 Voila, STDEV.
=MMULT
is another Excel formula. This one is for calculating matrices. Matrices could be considered a fancy and difficult mathematical concept to process or convert to perl code, but sometimes it's actually not so bad. Namely, it's about multiplying and summing ranges of numbers. If these ranges are a single row and a single column, chances are good that this formula is succinctly doing the same thing we always do: multiply answers by coefficients (decimals) and add them up. MMULT can do this in one quick step. However, when an algorithm uses MMULT and there are larger arrays/ranges of cells in the equation, you might be in very deep water. Walk carefully! ADVANCED CASES: Sometimes (rarely) we can't reproduce the algorithm due to time, budget, or technical constraints. In cases like these, the client might expect this and have a URL set up to perform serverside calculation for you! It's a webpage we give to our perl engine to talk to. The other server then responds to ours with the segment assignment. I have only seen this once, on this survey. Here is the tricky part of the code, for future reference:

After this step, the output is split on semicolons and we carry on. The point of this technique's being server side (i.e., not JavaScript) is that the respondent can't see what's going on. This is important.
code:<<END sub saveData { my $self = shift; my $resp = $self>respondent; # only set this if we don't have a value, so we don't change tracks if respondent goes back and forward again if (not $resp>get('QSEG')) { use LWP::UserAgent; use Crypt::SSLeay; my $url = sprintf("https://apps1.metricstudios.com/intel1/v1.aspx?vendor=M95HTH32&Q1=%s&RFRQ14R1=%s&FQ18=%s&FQ20R2=%s&FQ23R4=%s&BQ24R1C1=%s&FQ52R1=%s&respid=%s", $resp>get('QSEG1'),$resp>get('QSEG2'),$resp>get('QSEG3'),$resp>get('QSEG4R1'),$resp>get('QSEG4R2'),$resp>get('QSEG5'),$resp>get('QSEG6'),$resp>get('_trans_id')); my $ua = LWP::UserAgent>new; my $req = HTTP::Request>new( 'GET', $url ); my $res = $ua>request($req); $resp>set(QSEG => $res>content); } $self>SUPER::saveData(@_); }

Thanks for reading. This page is not meant to be a complete howto, but hopefully it has provided some tips that help novice or seasoned users when navigating algorithmic waters.
Comments
0 comments
Please sign in to leave a comment.