HUNCHPICK: one option from your list
=HUNCHPICK(A2, "billing|bug|feature request|other") returns the one option from your list that best describes the text. In Excel it is =HUNCH.PICK. Add "confidence" to get how sure the pick is.
Syntax
=HUNCHPICK(text, options, [question], [as])=HUNCH.PICK(text, options, [question], [as])| Argument | Required | What it is |
|---|---|---|
text | Yes | A cell or one-column range with the text to classify. |
options | Yes | 2 to 255 options separated by |. Describe an option after a colon and a space: "bug: something is broken|billing: invoices, charges, refunds|other". The cell shows only the label. |
question | No | An instruction for the choice. Default: "Which option best describes this text?" Use "" to keep the default and still set as. |
as | No | "option" (default) returns the label. "confidence" returns the probability of the chosen option, 0 to 1. "probabilities" returns every option with its probability, highest first. |
Examples with real outputs
=HUNCHPICK(A2:A7, "billing: invoices, charges, refunds|bug: something is broken|feature request: asks for something new|account: login, users, access|other")In Excel: =HUNCH.PICK(A2:A7, "billing: invoices, charges, refunds|bug: something is broken|feature request: asks for something new|account: login, users, access|other")
=HUNCHPICK(A2:A7, "billing: invoices, charges, refunds|bug: something is broken|feature request: asks for something new|account: login, users, access|other", "", "confidence")In Excel: =HUNCH.PICK(A2:A7, "billing: invoices, charges, refunds|bug: something is broken|feature request: asks for something new|account: login, users, access|other", "", "confidence")
| Row | A | B | C |
|---|---|---|---|
| 1 | Ticket | Category | Confidence |
| 2 | I can't log in since the password reset email never arrives. | account | 0.83 |
| 3 | Would be great if the dashboard could export to PDF. | feature request | 1.00 |
| 4 | Your invoice shows 12 seats but we only have 9 users. | billing | 1.00 |
| 5 | Charts are blank in Safari after the last update. | bug | 1.00 |
| 6 | Can I add my accountant as a read-only user? | account | 0.99 |
| 7 | Do you have an office in Madrid? | other | 1.00 |
Five of the six picks come back at 0.99 or higher. The login problem gets account at 0.83, because a reset email that never arrives could also be a bug, and the lower confidence says so. The office question lands in other, which is what that option is for.
Writing good options
- Always include
other. Without it every text lands in one of your buckets, even when none fits. - Describe any option whose label is ambiguous. "account" alone could mean a customer account or a bank account. "account: login, users, access" cannot.
- Keep options on one level. Mixing "billing" with "angry customer" asks two questions at once. Use HUNCH or HUNCHMULTI for flags that can overlap.
- Long lists work (up to 255 options), and for a deep taxonomy, pick the top level first, then run a second HUNCHPICK per branch.
Put the options in a cell (say $F$1) and write =HUNCHPICK(A2:A500, $F$1). Editing the list in one place re-sorts the whole column.
Using confidence
The confidence is the probability of the chosen option. Near 1, the pick is clear. Lower values mean the text fits more than one option or none well. Filter on it to get a short review queue instead of rereading every row.
=FILTER(A2:B500, HUNCHPICK(A2:A500, $F$1, "", "confidence") < 0.6)In Excel: =FILTER(A2:B500, HUNCH.PICK(A2:A500, $F$1, "", "confidence") < 0.6)
"probabilities" shows the whole distribution, such as bug 0.62, account 0.31, other 0.07, which helps when you are still deciding where the boundaries between options should be.
Cost and limits
- One credit per answered row, whatever the number of options. The confidence and the probabilities come from the same answer: a second formula with the same text, options and question reads the cached answer, so the confidence column is free for six hours.
- Blank cells and repeated texts are free. Answers are cached for six hours.
- The label in the cell is always exactly one of your labels, so COUNTIF, pivot tables and conditional formatting work on it without cleanup.
Questions
Can one text get two categories?
Does the order of the options matter?
Why did a row get other?
Run it on your own column
Copy the template (or use the Excel add-in), paste your key, point the formula at your data. Every formula here has its Excel spelling underneath. 100 rows free to start, then $29 for 5,000 rows. Credits never expire and there is no subscription.
More reference and guides
- HUNCH: the probability that the answer is yes HUNCH
- HUNCHSCORE: a position on your own scale HUNCHSCORE
- HUNCHMULTI: several questions, one read of the text HUNCHMULTI
- Categorize support tickets in a spreadsheet Ticket triage
- Code open-ended survey responses in Google Sheets Survey coding
- Classify text into categories with a Google Sheets formula Classification