# Google Sheets BINOM.INV Function: Syntax, Examples & Tips - URL: https://betteranalyst.com/google-sheets/binominv-function - Author: Better Analyst Formula Bot is now Better Analyst—same account, more powerful analysis.Formula Bot is now Better Analyst—same account and workflows, with more powerful analysis, dashboards, and automation. [Learn what changed](https://betteranalyst.com/formula-bot-is-now-better-analyst) Returns the smallest number of successes whose cumulative binomial probability is at least a target value. ``` =BINOM.INV(num_trials, prob_success, target_prob) ``` Generate FormulaExplain Formula One free generation or explanation per browser, shared across Excel and Google Sheets. Account usage limits apply afterward. Describe the BINOM.INV you need in plain EnglishGenerate Formula ## Parameters | Parameter | Required | Description | | - | - | - | | **num\_trials** | Yes | The number of independent trials. | | **prob\_success** | Yes | The probability of success on each trial, between 0 and 1. | | **target\_prob** | Yes | The cumulative probability to reach, between 0 and 1. | ## What Is BINOM.INV? BINOM.INV works backwards from a probability to a count. Given a number of trials and the success rate on each, it returns the **smallest number of successes *k*** for which the chance of getting *k* or fewer successes is at least your target probability. It is the inverse of the cumulative binomial distribution calculated by [BINOMDIST](https://betteranalyst.com/google-sheets/binomdist-function) (or BINOM.DIST) with `cumulative` set to TRUE. A practical way to read the answer: "95% of the time, we will see this many successes or fewer." That makes BINOM.INV a handy tool for capacity planning, quality limits, and setting thresholds. The older function CRITBINOM performs the same calculation with the same arguments. ## How BINOM.INV Works Google Sheets steps through possible counts 0, 1, 2, and so on, adding up binomial probabilities, and returns the first count at which the running total reaches **target\_prob**. Because the binomial distribution is discrete, the cumulative probability at the returned value will usually be somewhat higher than the target, not exactly equal. ## Basic Examples ### Median number of heads ``` =BINOM.INV(10, 0.5, 0.5) ``` Returns 5. The probability of 5 or fewer heads in 10 flips is about 62%, the first count to pass 50%. ### A 95% upper limit ``` =BINOM.INV(100, 0.05, 0.95) ``` Returns 9. With a 5% defect rate, a batch of 100 will contain 9 or fewer defects at least 95% of the time. ### Capacity planning ``` =BINOM.INV(B1, B2, 0.99) ``` With 400 bookings in B1 and a 90% show-up rate in B2, this returns the number of seats needed to seat everyone who shows up on 99% of days. ### Verify the result ``` =BINOMDIST(BINOM.INV(100, 0.05, 0.95), 100, 0.05, TRUE) ``` Shows the actual cumulative probability at the returned count (here about 0.97). ## Reading the Result Correctly | You want | Formula | | - | - | | A count not exceeded with probability *p* | `BINOM.INV(n, prob, p)` | | A lower limit exceeded with probability *p* | `BINOM.INV(n, prob, 1-p)` (approximately; check with BINOMDIST) | | A middle range covering 90% | `BINOM.INV(n, prob, 0.05)` to `BINOM.INV(n, prob, 0.95)` | ## Common Use Cases - **Quality control**: set an acceptance limit for defects in a sample - **Staffing and seating**: how many slots to provide for uncertain attendance - **Marketing**: a realistic upper range for responses from a campaign - **Inventory**: how many units of a slow-moving item might be demanded - **Fraud and anomaly checks**: flag a day when a count is above the 99th percentile ## Common Errors - **#NUM!**: **prob\_success** or **target\_prob** is outside 0 to 1, or **num\_trials** is negative. - **#VALUE!**: An argument is text that cannot be read as a number. - **Expecting an exact match**: The result is the first count that meets or passes the target, so its cumulative probability is usually higher than **target\_prob**. - **Confusing the order of arguments**: The number of trials comes first, then the success rate, then the target. ## Tips and Best Practices - Use BINOM.INV to turn a percentage rule into a whole-number limit that operations teams can act on. - Pair it with [IF](https://betteranalyst.com/google-sheets/if-function) to flag outliers: `=IF(C2 > BINOM.INV(B2, 0.05, 0.99), "Investigate", "")`. - For a continuous proportion rather than a count, see [BETAINV](https://betteranalyst.com/google-sheets/betainv-function). ## Related Functions - [BINOMDIST](https://betteranalyst.com/google-sheets/binomdist-function): probability of a given number of successes - [BETAINV](https://betteranalyst.com/google-sheets/betainv-function): percentiles of a proportion - [AVERAGE](https://betteranalyst.com/google-sheets/average-function): the mean of a data set - [ROUND](https://betteranalyst.com/google-sheets/round-function): tidy up probability results - [IF](https://betteranalyst.com/google-sheets/if-function): turn a probability into a decision ## Example use case A restaurant takes 120 reservations for Saturday and historically 85% of parties show up. =BINOM.INV(120, 0.85, 0.95) tells the manager how many tables to staff so that on 95% of Saturdays every arriving party is seated. ## Need more help with Google Sheets formulas? Sign up for Better Analyst and get unlimited AI-powered formula generation, explanations, and advanced data analysis. [Create Free Account](https://betteranalyst.com/signup)[Try Formula Generator](https://betteranalyst.com/excel-ai) ## Google Sheets how-to guides - [How To Insert Checkbox In Google Sheets](https://betteranalyst.com/google-sheets/how-to-insert-checkbox-in-google-sheets) - [How To Insert Multiple Rows In Google Sheets](https://betteranalyst.com/google-sheets/how-to-insert-multiple-rows-in-google-sheets) - [How To Lock A Row In Google Sheets](https://betteranalyst.com/google-sheets/how-to-lock-a-row-in-google-sheets) - [How To Lock Cells In Google Sheets](https://betteranalyst.com/google-sheets/how-to-lock-cells-in-google-sheets) - [How To Make A Copy Of An Google Sheets Sheet](https://betteranalyst.com/google-sheets/how-to-make-a-copy-of-an-google-sheets-sheet) - [How To Make A Graph In Google Sheets](https://betteranalyst.com/google-sheets/how-to-make-a-graph-in-google-sheets) - [How To Make A Histogram In Google Sheets](https://betteranalyst.com/google-sheets/how-to-make-a-histogram-in-google-sheets) - [How To Make A Line Graph In Google Sheets](https://betteranalyst.com/google-sheets/how-to-make-a-line-graph-in-google-sheets) ## Explore all Google Sheets formulas Browse our complete library of Google Sheets formula guides Compare your options ## Decide with evidence, not a sales pitch. Open a neutral research prompt in the AI tool you already use. Compare capabilities, limitations, and fit before you choose. 1. [01ChatGPTOpen a balanced comparison prompt in ChatGPT.](https://chatgpt.com/?prompt=Compare%20Better%20Analyst%20with%20using%20a%20general-purpose%20AI%20assistant%20for%20data%20analysis.%20Evaluate%20spreadsheet%20and%20document%20analysis%2C%20dashboards%2C%20repeatable%20reports%2C%20code%20transparency%2C%20data%20connections%2C%20pricing%2C%20and%20limitations.%20Use%20current%20public%20sources%2C%20distinguish%20verified%20facts%20from%20assumptions%2C%20and%20explain%20which%20types%20of%20users%20each%20option%20fits%20best.) 2. [02ClaudeOpen the same research prompt in Claude.](https://claude.ai/new?q=Compare%20Better%20Analyst%20with%20using%20a%20general-purpose%20AI%20assistant%20for%20data%20analysis.%20Evaluate%20spreadsheet%20and%20document%20analysis%2C%20dashboards%2C%20repeatable%20reports%2C%20code%20transparency%2C%20data%20connections%2C%20pricing%2C%20and%20limitations.%20Use%20current%20public%20sources%2C%20distinguish%20verified%20facts%20from%20assumptions%2C%20and%20explain%20which%20types%20of%20users%20each%20option%20fits%20best.) 3. [03PerplexityResearch the comparison with linked web sources.](https://www.perplexity.ai/search?q=Compare%20Better%20Analyst%20with%20using%20a%20general-purpose%20AI%20assistant%20for%20data%20analysis.%20Evaluate%20spreadsheet%20and%20document%20analysis%2C%20dashboards%2C%20repeatable%20reports%2C%20code%20transparency%2C%20data%20connections%2C%20pricing%2C%20and%20limitations.%20Use%20current%20public%20sources%2C%20distinguish%20verified%20facts%20from%20assumptions%2C%20and%20explain%20which%20types%20of%20users%20each%20option%20fits%20best.) AI answers can be incomplete. Check source links, current pricing, and product documentation before deciding.