The ranges parameter
What it does
Comma-separated rules for recoding values, or the shortcut "reverse". Every rule works on its own; the first rule that matches a value wins.
Formats:
- Range: new:min:max (recodes any x with min <= x < max to NEW). An empty bound is open.
Example: "1:0:10" (0-10 -> 1), "3:20:" (x >= 20 -> 3), "9::0" (everything below 0 -> 9).
- Range to text: label:min:max gives the values of the range the text label ("kids:0:18,adult:18:"); label:code:min:max gives the code with a label ("kids:1:0:18").
- Pair: which side is the old value depends on the type of the column — the side of the column's type.
Two numbers are new:old: "1:0,0:1" (flip 0<->1), "1:7,2:6,3:5".
In a text column "Never:1" and "1:Never" both recode Never -> 1 (Never becomes the label of 1); "control:group_a" recodes text to text.
In a numeric column "low:1" recodes 1 -> low (the result mixes text and numbers).
- Reverse: "reverse" mirrors the scale, min + max − value (1,2,3,5 -> 5,4,3,1).
"reverse:1:7" declares the scale (useful when 1 or 7 is not in the data). Numbers only; one scale for all selected columns.
A list that starts with the bare word reverse is a reverse, and the other rules are dropped: "reverse,1:2" gives the same as "reverse". To recode and reverse, use two formulas.
A value that no rule matches stays as it is. An empty item (a trailing comma) is skipped. Letter case matters: "Never:1" does not touch "never".
A wrong rule is an error of the whole formula that names the rule — nothing is recoded: no rules, one part ("5"), an empty side (":1"), a bound that is not a number ("1:a:3"), an empty range ("9:3:1"), more than four parts, two texts in a numeric column ("a:b"), the bare word reverse after other rules, "reverse:1" in a numeric column.
A pair with text over numeric and text columns at once is an error ("recode them separately"): recode them in separate formulas. Rules of numbers only work on such a selection and leave the text columns as they are.
Values
Required.
In plain words
A teacher had ages 9, 14 and 20 and wanted three groups. She wrote the rules 1:0:12, 2:12:18, 3:18: and in the new column the ages became 1, 2 and 3. In each rule the first number is the new code, then the start, which counts, then the end, which does not. So 12 goes to group 2, not group 1, and an empty end means no end. An age that no rule fits stays as it was. She can also write a word instead of a code, such as kids:0:12, or the word reverse to turn answers from 1 to 5 around.
Where you use it
STATS_RECODE— Recode values by rule bins (ranges) or exact matches, or reverse a scale. New values can be numbers or text.
Examples
"1:0:10,2:10:20,3:20:"— ranges to codes: 0–9 is 1, 10–19 is 2, 20 and above is 3"male:1,female:2"— text answers to codes"control:group_a"— text to text"low:1:3,high:3:"— ranges to text: 1–2 is low, 3 and above is high"1:7,2:6,3:5"— numbers to numbers, new:old: 7 becomes 1, 6 becomes 2, 5 becomes 3"reverse"— reverse a scale by the smallest and the largest answer"reverse:1:5"— reverse a 1–5 scale
Get it in your sheet
ALS Stats is a free add-on for Google Sheets™.
Install ALS Stats from Google Workspace Marketplace (opens in a new tab)