18F: Digital service delivery | Three ways to write fearless spreadsheet formulas

<!DOCTYPE html> <html lang="en-US"> <head>  <meta charset="utf-8" /> <meta http-equiv="X-UA-Compatible" content="IE=edge" />  <meta name="HandheldFriendly" content="True" /> <meta name="MobileOptimized" content="320" /> <meta name="viewport" content="width=device-width, initial-scale=1.0" />  <title>18F: Digital service delivery | Three ways to write fearless spreadsheet formulas</title> <meta property="og:title" content="18F: Digital service delivery | Three ways to write fearless spreadsheet formulas" /> <meta name="description" content="" /> <meta property="og:description" content="" /> <meta name="twitter:card" content="summary" /> <meta name="twitter:site" content="@18F" /> <meta name="twitter:title" content="Three ways to write fearless spreadsheet formulas" /> <meta name="twitter:description" content="" /> <meta property="og:type" content="article" /> <link rel="canonical" href="/2024/11/05/three-ways-to-write-fearless-spreadsheet-formulas/" /> <meta property="og:url" content="/2024/11/05/three-ways-to-write-fearless-spreadsheet-formulas/" /> <script async="" src=/assets/js/uswds-init.js></script>  <link rel="icon" type="image/png" sizes="16x16" href="/img/favicons/favicon-16x16.png"> <link rel="icon" type="image/png" sizes="32x32" href="/img/favicons/favicon-32x32.png"> <link rel="icon" type="image/png" sizes="192x192" href="/img/favicons/favicon-192x192.png"> <link rel="icon" type="image/svg+xml" href="/img/favicons/favicon.svg"> <link rel="shortcut icon" type="image/x-icon" href="/favicon.ico" /> <link rel="apple-touch-icon" sizes="180x180" href="/img/favicons/favicon-180x180.png">  <link href="https://unpkg.com/prismjs@1.20.0/themes/prism.css" rel="stylesheet"> <link rel="preload" as="style" href="/assets/styles/styles-UKMOJPRN.css" /> <link rel="stylesheet" href="/assets/styles/styles-UKMOJPRN.css" type="text/css" /> </head> <body class=" "> <a class="usa-skipnav" href="#main-content">Skip to main content</a> <div class="page-landing-page layout-demo "> <a class="usa-skipnav" href="#main-content">Skip to main content</a> <div class="usa-banner"> <div class="usa-accordion"> <section id="gov-banner-header-section" aria-label="USA Gov banner section header" class="usa-banner__header" > <div class="usa-banner__inner"> <div class="grid-col-auto"> <img src="/img/us_flag_small-VKjpzbKpq_.png" class="usa-banner__header-flag" alt="U.S. flag" loading="lazy" decoding="async"> </div> <div class="grid-col-fill tablet:grid-col-auto"> <p class="usa-banner__header-text"> An official website of the United States government </p> <p class="usa-banner__header-action" aria-hidden="true"> Here’s how you know </p> </div> <button class="usa-accordion__button usa-banner__button" aria-expanded="false" aria-controls="gov-banner" type="button" > <span class="usa-banner__button-text">Here's how you know</span> </button> </div> </section> <div class="usa-banner__content usa-accordion__content" id="gov-banner"> <div class="grid-row grid-gap-lg"> <div class="usa-banner__guidance tablet:grid-col-6"> <div class="usa-media-block__body"> <p> <strong>The .gov means it’s official.</strong> <br /> Federal government websites often end in .gov or .mil. Before sharing sensitive information, make sure you’re on a federal government site. </p> </div> </div> <div class="usa-banner__guidance tablet:grid-col-6"> <div class="usa-media-block__body"> <p> <strong>The site is secure.</strong> <br /> The <strong>https://</strong> ensures that you are connecting to the official website and that any information you provide is encrypted and transmitted securely. </p> </div> </div> </div> </div> </div> </div> <div class="usa-overlay"></div> </div> <header class="usa-header usa-header--basic usa-header--basic-megamenu" role="banner"> <div class="usa-nav-container"> <div class="usa-navbar"> <div class="usa-logo" id="header-logo"> <a href="/" title="Home"> <img src="/img/18f-logo-60i14872OF.svg" class="usa-logo-img" alt="18F home page" loading="lazy" decoding="async"> </a> </div> <button type="button" class="usa-menu-btn">Menu</button> </div> <nav class="usa-nav" aria-label="Menu navigation" > <button type="button" class="usa-nav__close"> <img src="/img/close-xPu02brD0d.svg" class="" alt="close" loading="lazy" decoding="async"> </button> <ul class="usa-nav__primary usa-accordion"> <li class="usa-nav__primary-item"> <a class="usa-nav__link " href="/our-work/" > <span>Our work</span> </a> </li> <li class="usa-nav__primary-item"> <a class="usa-nav__link " href="/work-with-us/" > <span>Work with us</span> </a> </li> <li class="usa-nav__primary-item"> <a class="usa-nav__link " href="/about/" > <span>About 18F</span> </a> </li> <li class="usa-nav__primary-item"> <a class="usa-nav__link " href="/guides/" > <span>Guides</span> </a> </li> <li class="usa-nav__primary-item"> <a class="usa-nav__link usa-current " href="/blog/" > <span>Blog</span> </a> </li> <li class="usa-nav__primary-item"> <a class="usa-button an18f-button--dark margin-top-3 desktop:margin-top-0 desktop:margin-x-2 " href="/contact/" > <span>Contact</span> </a> </li> </ul> <a href="https://search.usa.gov/search?utf8=%E2%9C%93&affiliate=18F-site&query=&commit=" class="usa-button padding-x-2" > <img src="/img/search--white-ZVwKBtv5eC.svg" class="" alt="Search" loading="lazy" decoding="async"> </a> </nav> </div> </header> <main id="main-content"> <article id="main-content" class="post-article margin-top-6" itemscope itemtype="http://schema.org/BlogPosting"> <div class="grid-container"> <div class="grid-row"> <div class="tablet:grid-col-12"> <header> <h1 itemprop="name headline">Three ways to write fearless spreadsheet formulas</h1> <p class="margin-top-1"> Published on <time datetime="Tue Nov 05 2024 00:00:00 GMT+0000 (Coordinated Universal Time)" itemprop="datePublished">November 5, 2024</time> </p> <span class="post-tags display-flex flex-wrap" itemprop="keywords"> <a href="/tags/modern-practices/" class="usa-label post-tag"> modern practices </a> <a href="/tags/technical-guides/" class="usa-label post-tag"> technical guides </a> <a href="/tags/tutorial/" class="usa-label post-tag"> tutorial </a> <a href="/tags/spreadsheets/" class="usa-label post-tag"> spreadsheets </a> </span> </header> </div> </div> <hr class="hr-1-dark"> <div class="grid-row grid-gap margin-top-8"> <div class="tablet:grid-col-7"> <div class="post-content" itemprop="articleBody"> <style> table { font-size: 1rem; } </style> <p><em>Reference to any non-federal entity does not constitute or imply its endorsement, recommendation, or favoring by GSA.</em></p> <p>Are you afraid of spreadsheet formulas? You're not alone. Many people consider spreadsheets to be anxiety-producing messes of formula code that spit out cryptic <code>#N/A</code>s at the most minor of changes.</p> <p>The problem is, <strong>we suspect that most government code runs in spreadsheets</strong>. Various unverifiable estimates floating around the internet suggest that between one and three billion people use spreadsheets, and that at least 500 million people write spreadsheet formulas. This would mean that spreadsheets are the most commonly-used programming systems in the world. That would also mean spreadsheets are where most government code lives — spreadsheet formulas are code, after all.</p> <p>If spreadsheet formulas are the most prolific government code, and if people fear their spreadsheet formulas — well, that combination can't be good for government, or for the public servants who develop, use, and maintain those spreadsheets.</p> <p><strong>The good news is, it doesn't have to be this way.</strong> We can make spreadsheet code easier to read by making it clearer and simpler. And when code is easier to read, it's easier to understand. If you understand your code, you can change it without fear.</p> <p>To create clear spreadsheets, we can use simple, well-tested programming practices to write spreadsheet formulas.</p> <p>Below, we'll work through an example problem, first using conventional spreadsheet formula-writing practice commonly used over the past 40 years or so. Then, we'll solve it again using simple, tried-and-true programming techniques that are now possible in modern spreadsheet programs.</p> <p>The result? Truly delightful spreadsheet formulas. Feeling suspicious? That's understandable.</p> <p>Let's get started.</p> <h3 id="our-scenario" tabindex="-1">Our scenario<a href="#our-scenario" class="heading-permalink" aria-label="Permalink for this section"><svg class="usa-icon" aria-hidden="true" focusable="false" role="img"><use xlink:href="#svg-link"></use></svg></a></h3> <p>Let's imagine we work for the National Park Service, in the fictional Igorville National Park. At the end of each day, we get a report of how many people visited the park that day. Our supervisor has asked us to create a cell in our spreadsheet that shows the average number of daily visitors to Igorville Park since the start of the fiscal quarter.</p> <p>The general solution is to:</p> <p>Divide the total number of visitors this quarter by the number of days elapsed in the quarter. Round up that number to the nearest integer. Then, add text at the beginning so it looks something like this:</p> <pre class="shiki github-light" style="background-color: #ffffff; color: #24292f"><div class="language-id">sql</div><div class='code-container'><code><div class='line'><span style="color: #24292F">visitors per </span><span style="color: #0550AE">day</span><span style="color: #24292F">: </span><span style="color: #0550AE">171</span></div></code></div></pre> <h2 id="writing-conventional-formulas" tabindex="-1">Writing conventional formulas<a href="#writing-conventional-formulas" class="heading-permalink" aria-label="Permalink for this section"><svg class="usa-icon" aria-hidden="true" focusable="false" role="img"><use xlink:href="#svg-link"></use></svg></a></h2> <p>Let's assume that the total visitors this quarter are in cell <code>B2</code>. The number of days elapsed this quarter are in cell <code>C2</code>.</p> <table> <thead> <tr> <th style="text-align:center"></th> <th>A</th> <th>B</th> <th>C</th> </tr> </thead> <tbody> <tr> <td style="text-align:center">1</td> <td><strong>Daily visitor rate for 2025 Q1</strong></td> <td><strong>Total visitors in 2025 Q1</strong></td> <td><strong>Days elapsed in 2025 Q1</strong></td> </tr> <tr> <td style="text-align:center">2</td> <td>TODO: Formula for daily visitor rate</td> <td>513</td> <td>3</td> </tr> <tr> <td style="text-align:center">3</td> <td></td> <td></td> <td></td> </tr> </tbody> </table> <p>To get the rounded visitor rate, we'll divide the total park visitors by the number of days in the quarter, then round.</p> <pre class="shiki github-light" style="background-color: #ffffff; color: #24292f"><div class="language-id">sql</div><div class='code-container'><code><div class='line'><span style="color: #CF222E">=</span><span style="color: #0550AE">ROUND</span><span style="color: #24292F">(B2 </span><span style="color: #CF222E">/</span><span style="color: #24292F"> C2)</span></div></code></div></pre> <p>Next, we'll add the presentation logic. We'll convert the number to text, even though that's not always necessary. Then we'll add lead-in text that says <code>visitors per day: </code>.</p> <pre class="shiki github-light" style="background-color: #ffffff; color: #24292f"><div class="language-id">sql</div><div class='code-container'><code><div class='line'><span style="color: #CF222E">=CONCATENATE</span><span style="color: #24292F">(</span><span style="color: #0A3069">"visitors per day: "</span><span style="color: #24292F">, </span><span style="color: #CF222E">TEXT</span><span style="color: #24292F">(</span><span style="color: #0550AE">ROUND</span><span style="color: #24292F">(B2 </span><span style="color: #CF222E">/</span><span style="color: #24292F"> C2)))</span></div></code></div></pre> <p>There's a hidden divide-by-zero (<code>#DIV/0!</code>) error waiting to happen here. Do you see it?</p> <p>On the first day of each quarter, zero days have elapsed, so <code>C2</code> will be zero. This means we'll get a divide-by-zero error the first day of each quarter. (This is also probably the day our supervisors will look at last quarter's numbers, so we don't want to show them a spreadsheet with ugly errors.)</p> <p>Instead, let's catch the error, and output a message that the data is still pending for this quarter. We could try wrapping the whole formula in an <code>IFERROR</code> to catch the error, and report that "Data is pending."</p> <pre class="shiki github-light" style="background-color: #ffffff; color: #24292f"><div class="language-id">diff</div><div class='code-container'><code><div class='line'><span style="color: #116329">+=IFERROR(</span></div><div class='line'><span style="color: #24292F"> CONCATENATE("visitors per day: ", TEXT(ROUND(B2 / C2))), "Data is pending"</span></div><div class='line'><span style="color: #116329">+)</span></div></code></div></pre> <p>But when we step back and read this code, the headaches begin.</p> <p>The <code>IFERROR</code> is far away from the divide operation, so readers might not understand that we're trying to catch a divide-by-zero error. In a week or two, we're likely to forget what this <code>IFERROR</code> is doing.</p> <p>Plus, we want to prevent only the divide-by-zero error, but the way this is written will catch <em>all</em> errors. We won't know if there's another type of error happening here that we should be aware of.</p> <p>Let's make the connection between the potential error and the error-handling code more clear. To do this, we could rearrange the error and the error-handling to be closer together, like this.</p> <pre class="shiki github-light" style="background-color: #ffffff; color: #24292f"><div class="language-id">sql</div><div class='code-container'><code><div class='line'><span style="color: #CF222E">=CONCATENATE</span><span style="color: #24292F">(</span><span style="color: #0A3069">"visitors per day: "</span><span style="color: #24292F">, </span><span style="color: #CF222E">TEXT</span><span style="color: #24292F">(</span><span style="color: #0550AE">ROUND</span><span style="color: #24292F">(IFERROR(B2 </span><span style="color: #CF222E">/</span><span style="color: #24292F"> C2, </span><span style="color: #0A3069">"Data is pending"</span><span style="color: #24292F">))))</span></div></code></div></pre> <p>This won't work either. On the first day of each quarter, the formula would evaluate to <code>ROUND("Data is pending")</code>. Since you can't round text, this would give us a <code>#VALUE!</code> error.</p> <p>Instead of handing the error like this, we can prevent the error from happening. We know that the error occurs under one specific condition — the first day of the quarter, when zero days have elapsed. So, let's add a pre-condition that prevents the error. This is known as a "guard clause."</p> <p>(If you're copying and pasting this code, remember to delete the comment denoted by <code>/* */</code>. These are my way to annotate the code, but the spreadsheet formula language will raise an error.)</p> <pre class="shiki github-light" style="background-color: #ffffff; color: #24292f"><div class="language-id">sql</div><div class='code-container'><code><div class='line'><span style="color: #CF222E">=IF</span><span style="color: #24292F">(C2 </span><span style="color: #CF222E">=</span><span style="color: #24292F"> </span><span style="color: #0550AE">0</span><span style="color: #24292F">, </span><span style="color: #6E7781">/* guard clause */</span></div><div class='line'><span style="color: #24292F"> </span><span style="color: #0A3069">"Data is pending."</span><span style="color: #24292F">,</span></div><div class='line'><span style="color: #24292F"> </span><span style="color: #CF222E">CONCATENATE</span><span style="color: #24292F">(</span><span style="color: #0A3069">"visitors per day: "</span><span style="color: #24292F">, </span><span style="color: #CF222E">TEXT</span><span style="color: #24292F">(</span><span style="color: #0550AE">ROUND</span><span style="color: #24292F">(B2 </span><span style="color: #CF222E">/</span><span style="color: #24292F"> C2)))</span></div><div class='line'><span style="color: #24292F">)</span></div></code></div></pre> <p>(You can make your formula span multiple lines by using Option + Return on Mac, or Control + Enter on PC.)</p> <p>With the guard clause in place, the divide-by-zero can never happen! And with that, our formula is all set.</p> <p>This formula is pretty clear when read aloud. In plain language, it reads something like: "if there are zero days in the quarter, data is pending, otherwise, show the rounded daily visitor rate."</p> <p>We could stop writing our formula here, and it would be fine — so long as this formula stayed about this simple. If we needed to make bigger changes, with more potential for errors, this could get overly complex very quickly.</p> <h3 id="four-steps-your-code-should-follow" tabindex="-1">Four steps your code should follow<a href="#four-steps-your-code-should-follow" class="heading-permalink" aria-label="Permalink for this section"><svg class="usa-icon" aria-hidden="true" focusable="false" role="img"><use xlink:href="#svg-link"></use></svg></a></h3> <p>Let's pause a moment and think about the "story" this code is telling.</p> <p>In his talk <a href="https://www.youtube.com/watch?v=T8J0j2xJFgQ" class="usa-link usa-link--external" rel="noreferrer">Confident Code</a>, developer Avdi Grimm explains that code has to do four things:</p> <ul> <li>Collect input</li> <li>Perform work</li> <li>Deliver results</li> <li>Handle failure</li> </ul> <p>When we read code, we should ideally see these four steps in order, telling a clear story.</p> <p>Try reading the code in the last example out loud. Here's how I'd read the story of this code:</p> <table> <thead> <tr> <th>Code in plain language</th> <th>Confident Code step</th> </tr> </thead> <tbody> <tr> <td>If C2, the number of days, is zero</td> <td>1. Collect input</td> </tr> <tr> <td>Report "Data is pending"</td> <td>4. Handle failure</td> </tr> <tr> <td>Otherwise show "visitors per day: " next to...</td> <td>3. Return results</td> </tr> <tr> <td>...the rounded daily visitor rate</td> <td>2. Perform work</td> </tr> </tbody> </table> <p>The story of this code puts the more important parts last. This formula calculates and reports the error rate, but not until the very end of the code. The "perform work" step comes last, instead of in second place like in Avdi's list. This is like reading a news article where the crucial information is introduced in the last paragraph.</p> <p>But writing formulas this way probably feels familiar. That makes sense, because it's the way formula programming language has encouraged us all to write for the last 40 years. And while the conventional way we've written this formula works for this simple example, it isn't sustainable for more complex problems.</p> <h2 id="three-techniques-for-writing-better-formulas" tabindex="-1">Three techniques for writing better formulas<a href="#three-techniques-for-writing-better-formulas" class="heading-permalink" aria-label="Permalink for this section"><svg class="usa-icon" aria-hidden="true" focusable="false" role="img"><use xlink:href="#svg-link"></use></svg></a></h2> <p>Let's approach this same problem using tried-and-true software design principles using three techniques. First, we'll tell a clear story that's easy to read and write. Then, we'll add comments to clarify what we've written. Last, we'll organize our code so readers can more easily understand our formula.</p> <p>To do this, we'll use the functions <code>LET</code> and <code>LAMBDA</code>. These might be unfamiliar, since spreadsheet programs introduced these only in the last few years.</p> <h3 id="technique-1-use-let-to-tell-a-clear-story" tabindex="-1">Technique 1: Use LET to tell a clear story<a href="#technique-1-use-let-to-tell-a-clear-story" class="heading-permalink" aria-label="Permalink for this section"><svg class="usa-icon" aria-hidden="true" focusable="false" role="img"><use xlink:href="#svg-link"></use></svg></a></h3> <h4 id="introducing-let" tabindex="-1">Introducing LET<a href="#introducing-let" class="heading-permalink" aria-label="Permalink for this section"><svg class="usa-icon" aria-hidden="true" focusable="false" role="img"><use xlink:href="#svg-link"></use></svg></a></h4> <p>Let's say we have a formula we want to put into our spreadsheet:</p> <pre class="shiki github-light" style="background-color: #ffffff; color: #24292f"><div class="language-id">sql</div><div class='code-container'><code><div class='line'><span style="color: #24292F">x </span><span style="color: #CF222E">+</span><span style="color: #24292F"> y</span></div></code></div></pre> <p>In conventional spreadsheet practice, we would replace these variables <code>x</code> and <code>y</code> with either literal values, such as <code>1</code> and <code>2</code>, or cell references, such as <code>A2</code> and <code>C2</code>.</p> <pre class="shiki github-light" style="background-color: #ffffff; color: #24292f"><div class="language-id">sql</div><div class='code-container'><code><div class='line'><span style="color: #CF222E">=</span><span style="color: #0550AE">1</span><span style="color: #24292F"> </span><span style="color: #CF222E">+</span><span style="color: #24292F"> </span><span style="color: #0550AE">2</span></div></code></div></pre> <pre class="shiki github-light" style="background-color: #ffffff; color: #24292f"><div class="language-id">sql</div><div class='code-container'><code><div class='line'><span style="color: #CF222E">=</span><span style="color: #24292F">A2 </span><span style="color: #CF222E">+</span><span style="color: #24292F"> C2</span></div></code></div></pre> <p>With <code>LET</code>, we can assign values to variables. In this example, we'll set <code>x</code> and <code>y</code> to <code>1</code> and <code>2</code> respectively, while leaving the formula as-is.</p> <pre class="shiki github-light" style="background-color: #ffffff; color: #24292f"><div class="language-id">sql</div><div class='code-container'><code><div class='line'><span style="color: #CF222E">=</span><span style="color: #24292F">LET(</span></div><div class='line'><span style="color: #24292F"> x, </span><span style="color: #0550AE">1</span><span style="color: #24292F">, </span><span style="color: #6E7781">/* set x to 1 */</span></div><div class='line'><span style="color: #24292F"> y, </span><span style="color: #0550AE">2</span><span style="color: #24292F">, </span><span style="color: #6E7781">/* set y to 2 */</span></div><div class='line'><span style="color: #24292F"> x </span><span style="color: #CF222E">+</span><span style="color: #24292F"> y </span><span style="color: #6E7781">/* evaluates to `1 + 2`, which returns `3` */</span></div><div class='line'><span style="color: #24292F">)</span></div></code></div></pre> <p>The first line assigns the value <code>1</code> to the variable <code>x</code>, and sets <code>y</code> to <code>2</code>. The last line is the formula, where everything previously assigned is evaluated.</p> <p><code>LET</code> can assign any other formula or expression to a variable, like this:</p> <pre class="shiki github-light" style="background-color: #ffffff; color: #24292f"><div class="language-id">sql</div><div class='code-container'><code><div class='line'><span style="color: #CF222E">=</span><span style="color: #24292F">LET(</span></div><div class='line'><span style="color: #24292F"> x, </span><span style="color: #0550AE">1</span><span style="color: #24292F"> </span><span style="color: #CF222E">+</span><span style="color: #24292F"> </span><span style="color: #0550AE">2</span><span style="color: #24292F"> </span><span style="color: #CF222E">+</span><span style="color: #24292F"> </span><span style="color: #0550AE">3</span><span style="color: #24292F">, </span><span style="color: #6E7781">/* set x to 6 */</span></div><div class='line'><span style="color: #24292F"> y, x </span><span style="color: #CF222E">*</span><span style="color: #24292F"> </span><span style="color: #0550AE">2</span><span style="color: #24292F">, </span><span style="color: #6E7781">/* set y to 12, using the already-set value for x */</span></div><div class='line'><span style="color: #24292F"> x </span><span style="color: #CF222E">+</span><span style="color: #24292F"> y </span><span style="color: #6E7781">/* returns `18` */</span></div><div class='line'><span style="color: #24292F">)</span></div></code></div></pre> <p>How does naming values like this help make our formulas better? Well, we can use this to tell the story of our formula in clear steps.</p> <h4 id="writing-the-story-of-our-code" tabindex="-1">Writing the story of our code<a href="#writing-the-story-of-our-code" class="heading-permalink" aria-label="Permalink for this section"><svg class="usa-icon" aria-hidden="true" focusable="false" role="img"><use xlink:href="#svg-link"></use></svg></a></h4> <p>First, let's write the story of our code, following the order of Avdi's steps.</p> <ul> <li><strong>Gather input</strong>: Get the number of visitors in the quarter and the number of days in the quarter</li> <li><strong>Perform work</strong>: Divide the number of visitors by the number of days to get the rate, then round the rate</li> <li><strong>Handle failure</strong>: Handle when there are zero days elapsed at the start of a quarter</li> <li><strong>Deliver results</strong>: Format and present the rate</li> </ul> <p>Let's translate these steps, in this order, into formula code.</p> <pre class="shiki github-light" style="background-color: #ffffff; color: #24292f"><div class="language-id">sql</div><div class='code-container'><code><div class='line'><span style="color: #CF222E">=</span><span style="color: #24292F">LET(</span></div><div class='line'><span style="color: #24292F"> </span><span style="color: #6E7781">/* 1. Gather input */</span></div><div class='line'><span style="color: #24292F"> visitors_in_quarter, B2,</span></div><div class='line'><span style="color: #24292F"> days_in_quarter, C2,</span></div><div class='line'></div><div class='line'><span style="color: #24292F"> </span><span style="color: #6E7781">/* 2. Perform work */</span></div><div class='line'><span style="color: #24292F"> daily_visitor_rate, </span><span style="color: #0550AE">ROUND</span><span style="color: #24292F">(visitors_in_quarter</span><span style="color: #CF222E">/</span><span style="color: #24292F">days_in_quarter),</span></div><div class='line'><span style="color: #24292F"> </span><span style="color: #CF222E">IF</span><span style="color: #24292F">(</span></div><div class='line'><span style="color: #24292F"> </span><span style="color: #6E7781">/* 3. Handle failure */</span></div><div class='line'><span style="color: #24292F"> days_in_quarter </span><span style="color: #CF222E"><=</span><span style="color: #24292F"> </span><span style="color: #0550AE">0</span><span style="color: #24292F">, </span></div><div class='line'><span style="color: #24292F"> </span><span style="color: #0A3069">"Data is pending."</span><span style="color: #24292F">,</span></div><div class='line'></div><div class='line'><span style="color: #24292F"> </span><span style="color: #6E7781">/* 4. Deliver results */</span></div><div class='line'><span style="color: #24292F"> </span><span style="color: #0A3069">"visitors per day: "</span><span style="color: #24292F"> & daily_visitor_rate</span></div><div class='line'><span style="color: #24292F"> )</span></div><div class='line'><span style="color: #24292F">)</span></div></code></div></pre> <p>This tells a much clearer story than the previous conventional formula. The inputs are named, so we know we're working with visitors and days in the quarter. The visitor rate is named, so when we present it with the "visitors per day: " text, it's clear what we're presenting. Even a first-time reader would have a pretty good idea of what's happening here.</p> <p>In conclusion, <code>LET</code> helps you name the inputs and steps in your formula code, and these names clarify your story.</p> <h3 id="technique-2-use-comments-to-clarify-readable-code" tabindex="-1">Technique 2: Use comments to clarify readable code<a href="#technique-2-use-comments-to-clarify-readable-code" class="heading-permalink" aria-label="Permalink for this section"><svg class="usa-icon" aria-hidden="true" focusable="false" role="img"><use xlink:href="#svg-link"></use></svg></a></h3> <h4 id="what-are-comments" tabindex="-1">What are comments?<a href="#what-are-comments" class="heading-permalink" aria-label="Permalink for this section"><svg class="usa-icon" aria-hidden="true" focusable="false" role="img"><use xlink:href="#svg-link"></use></svg></a></h4> <p>When software developers write code, they can leave comments in the code. Comments are narrative descriptions of the code, which other developers can read to better understand the code.</p> <p>If comments are being used instead of writing clear code, the code and developers suffer. You may hear anti-comment sentiments from some developers, but my understanding is that disdain for comments is a reaction to the negative experience when comments are used as a substitute for clear code.</p> <p>If comments are being used to supplement writing clear code, they can be quite helpful. Comments can provide context, explain assumptions, warn about common mistakes, and more. Comments can also summarize the formula, so that readers don't have to read all the code to understand what's going on.</p> <p>But spreadsheet programs don't let you leave comments — <em>or do they</em>?</p> <h4 id="writing-our-first-comment" tabindex="-1">Writing our first comment<a href="#writing-our-first-comment" class="heading-permalink" aria-label="Permalink for this section"><svg class="usa-icon" aria-hidden="true" focusable="false" role="img"><use xlink:href="#svg-link"></use></svg></a></h4> <p>In other languages, a special identifier like a hash (<code>#</code>), double-slash (<code>//</code>), or other syntax (<code>/* */</code>) will indicate a comment. Spreadsheet language doesn't let you do <em>quite</em> that, which you may already know if you tried to copy and paste some of the prior code examples into a spreadsheet.</p> <p>But believe it or not, you can use <code>LET</code> to leave a comment!</p> <pre class="shiki github-light" style="background-color: #ffffff; color: #24292f"><div class="language-id">sql</div><div class='code-container'><code><div class='line'><span style="color: #CF222E">=</span><span style="color: #24292F">LET(</span></div><div class='line'><span style="color: #24292F"> comment, </span><span style="color: #0A3069">"</span></div><div class='line'><span style="color: #0A3069"> Calculates the daily visitor rate for the current quarter.</span></div><div class='line'><span style="color: #0A3069"> On the first day of the quarter (0 days elapsed), it will say that data is pending.</span></div><div class='line'><span style="color: #0A3069"> "</span><span style="color: #24292F">,</span></div><div class='line'><span style="color: #24292F"> visitors_in_quarter, B2,</span></div><div class='line'><span style="color: #24292F"> days_in_quarter, C2,</span></div><div class='line'><span style="color: #24292F"> daily_visitor_rate, </span><span style="color: #0550AE">ROUND</span><span style="color: #24292F">(visitors_in_quarter</span><span style="color: #CF222E">/</span><span style="color: #24292F">days_in_quarter),</span></div><div class='line'><span style="color: #24292F"> </span><span style="color: #CF222E">IF</span><span style="color: #24292F">(</span></div><div class='line'><span style="color: #24292F"> days_in_quarter </span><span style="color: #CF222E"><=</span><span style="color: #24292F"> </span><span style="color: #0550AE">0</span><span style="color: #24292F">,</span></div><div class='line'><span style="color: #24292F"> </span><span style="color: #0A3069">"Data is pending."</span><span style="color: #24292F">,</span></div><div class='line'><span style="color: #24292F"> </span><span style="color: #0A3069">"visitors per day: "</span><span style="color: #24292F"> & daily_visitor_rate</span></div><div class='line'><span style="color: #24292F"> )</span></div><div class='line'><span style="color: #24292F">)</span></div></code></div></pre> <p>If your spreadsheets are large and complex, or built by a team of people, you can make a team decision to use a standard comment format. If you use a standard, your team won't have to waste time developing its own commenting conventions. As a Ruby developer, I've repurposed the Ruby comment standard <a href="https://www.rubydoc.info/gems/yard/file/docs/Tags.md#Tag_List" class="usa-link usa-link--external" rel="noreferrer">YARD</a> for use in my more complex sheets.</p> <p>In conclusion, commenting summarizes and clarifies your formulas, and helps your team members and yourself remember important things about your code.</p> <h3 id="technique-3-use-lambda-to-organize-your-code" tabindex="-1">Technique 3: Use LAMBDA to organize your code<a href="#technique-3-use-lambda-to-organize-your-code" class="heading-permalink" aria-label="Permalink for this section"><svg class="usa-icon" aria-hidden="true" focusable="false" role="img"><use xlink:href="#svg-link"></use></svg></a></h3> <p>Let's return to our scenario, calculating the daily visitor rate. Here's where we left off using <code>LET</code>:</p> <pre class="shiki github-light" style="background-color: #ffffff; color: #24292f"><div class="language-id">sql</div><div class='code-container'><code><div class='line'><span style="color: #CF222E">=</span><span style="color: #24292F">LET(</span></div><div class='line'><span style="color: #24292F"> visitors_in_quarter, B2,</span></div><div class='line'><span style="color: #24292F"> days_in_quarter, C2,</span></div><div class='line'><span style="color: #24292F"> daily_visitor_rate, </span><span style="color: #0550AE">ROUND</span><span style="color: #24292F">(visitors_in_quarter</span><span style="color: #CF222E">/</span><span style="color: #24292F">days_in_quarter),</span></div><div class='line'><span style="color: #24292F"> </span><span style="color: #CF222E">IF</span><span style="color: #24292F">(</span></div><div class='line'><span style="color: #24292F"> days_in_quarter </span><span style="color: #CF222E"><=</span><span style="color: #24292F"> </span><span style="color: #0550AE">0</span><span style="color: #24292F">,</span></div><div class='line'><span style="color: #24292F"> </span><span style="color: #0A3069">"Data is pending."</span><span style="color: #24292F">,</span></div><div class='line'><span style="color: #24292F"> </span><span style="color: #0A3069">"visitors per day: "</span><span style="color: #24292F"> & daily_visitor_rate</span></div><div class='line'><span style="color: #24292F"> )</span></div><div class='line'><span style="color: #24292F">)</span></div></code></div></pre> <p>Let's say our fictional supervisor has discovered that the system that sends our spreadsheet the visitor numbers has a bug. Every day, that system over-counts by 10 visitors per day. Here's a table with a few examples.</p> <table> <thead> <tr> <th>Day in quarter</th> <th>Visitor count reported by buggy system</th> <th>Visitor count, actual</th> </tr> </thead> <tbody> <tr> <td>1</td> <td>141</td> <td>131</td> </tr> <tr> <td>2</td> <td>170</td> <td>160</td> </tr> <tr> <td>3</td> <td>202</td> <td>192</td> </tr> </tbody> </table> <p>Our supervisor has asked us to display the corrected number, but to also report the un-adjusted numbers so the executives understand what's going on, since they also look at the old system.</p> <p>So, on day 3 in the above table, we'd expect to see:</p> <pre class="shiki github-light" style="background-color: #ffffff; color: #24292f"><div class='code-container'><code><div class='line'><span style="color: undefined">visitors per day: 161 (adjusted from 171)</span></div></code></div></pre> <p>What a nightmare! Or is it? Can we turn this nightmare into a delight?</p> <p>When writing formulas, simpler is better. So if you have a complex problem to solve, it's helpful to break it into simpler problems. The practice of solving complex problems by breaking them into smaller and simpler problems is called "decomposition." Each piece will be easier to think about and solve, and then you can compose all the pieces together into the solution.</p> <p>Let's add the adjusted numbers and rates into our formula.</p> <pre class="shiki github-light" style="background-color: #ffffff; color: #24292f"><div class="language-id">diff</div><div class='code-container'><code><div class='line'><span style="color: #24292F">=LET(</span></div><div class='line'><span style="color: #24292F"> visitors_in_quarter, B2,</span></div><div class='line'><span style="color: #24292F"> days_in_quarter, C2,</span></div><div class='line'><span style="color: #116329">+ adjusted_visitors_in_quarter, visitors_in_quarter - (10 * days_in_quarter),</span></div><div class='line'><span style="color: #24292F"> daily_visitor_rate, ROUND(visitors_in_quarter/days_in_quarter),</span></div><div class='line'><span style="color: #116329">+ adjusted_daily_visitor_rate, ROUND(adjusted_visitors_in_quarter/days_in_quarter),</span></div><div class='line'><span style="color: #24292F"> IF(</span></div><div class='line'><span style="color: #24292F"> days_in_quarter <= 0,</span></div><div class='line'><span style="color: #24292F"> "Data is pending.",</span></div><div class='line'><span style="color: #24292F"> "visitors per day: " & adjusted_daily_visitor_rate & "(adjusted from " & daily_visitor_rate & ")"</span></div><div class='line'><span style="color: #24292F"> )</span></div><div class='line'><span style="color: #24292F">)</span></div></code></div></pre> <p>Notice how we do the same rate calculation twice, but with different numbers — we're dividing, then rounding.</p> <p>It's common practice in software development to try to reduce the amount of duplication. We call it DRY, which stands for Don't Repeat Yourself. We can reduce duplication with <code>LAMBDA</code>, a relatively new feature in spreadsheet programs.</p> <p>In the next code example, we'll add a lambda, then reduce duplicated code by using the lambda instead.</p> <pre class="shiki github-light" style="background-color: #ffffff; color: #24292f"><div class="language-id">diff</div><div class='code-container'><code><div class='line'><span style="color: #24292F">=LET(</span></div><div class='line'><span style="color: #24292F"> visitors_in_quarter, B2,</span></div><div class='line'><span style="color: #24292F"> days_in_quarter, C2,</span></div><div class='line'></div><div class='line'><span style="color: #116329">+ visitor_rate, LAMBDA(visitor_count, ROUND(visitor_count/days_in_quarter)),</span></div><div class='line'><span style="color: #24292F"> adjusted_visitors_in_quarter, visitors_in_quarter - (10 * days_in_quarter),</span></div><div class='line'></div><div class='line'><span style="color: #116329">+ daily_rate, visitor_rate(visitors_in_quarter),</span></div><div class='line'><span style="color: #116329">+ adjusted_daily_rate, visitor_rate(adjusted_visitors_in_quarter),</span></div><div class='line'></div><div class='line'><span style="color: #24292F"> IF(</span></div><div class='line'><span style="color: #24292F"> days_in_quarter <= 0,</span></div><div class='line'><span style="color: #24292F"> "Data is pending.",</span></div><div class='line'><span style="color: #24292F"> "visitors per day: " & adjusted_daily_rate & "(adjusted from " & daily_rate & ")"</span></div><div class='line'><span style="color: #24292F"> )</span></div><div class='line'><span style="color: #24292F">)</span></div></code></div></pre> <p>We've just created our first lambda! Lambdas are mini-formulas you can use and reuse within a main formula. We've created a lambda named <code>visitor_rate</code> that takes any visitor count and divides it by the number of days elapsed. We then use that lambda twice, giving it different numbers to use to calculate the rounded rate.</p> <p>Let's make one more change, and move the details of how we format or present the rate into its own <code>LAMBDA</code>. Moving complexity out of the main body of the formula can help clarify the code. We can trust the body of the code to be the most important part, and then we can go back and read the lambdas if we need to get into the details of how they work.</p> <pre class="shiki github-light" style="background-color: #ffffff; color: #24292f"><div class="language-id">diff</div><div class='code-container'><code><div class='line'><span style="color: #24292F">=LET(</span></div><div class='line'><span style="color: #24292F"> visitor_rate, LAMBDA(visitor_count, ROUND(visitor_count/days_in_quarter)),</span></div><div class='line'><span style="color: #116329">+ format, LAMBDA(adjusted, original, IF(</span></div><div class='line'><span style="color: #116329">+ days_in_quarter <= 0,</span></div><div class='line'><span style="color: #116329">+ "Data is pending.",</span></div><div class='line'><span style="color: #116329">+ "visitors per day: " & adjusted & "(adjusted from " & original & ")"</span></div><div class='line'><span style="color: #116329">+ )</span></div><div class='line'><span style="color: #116329">+ ),</span></div><div class='line'></div><div class='line'><span style="color: #24292F"> visitors_in_quarter, B2,</span></div><div class='line'><span style="color: #24292F"> days_in_quarter, C2,</span></div><div class='line'><span style="color: #24292F"> adjusted_visitors_in_quarter, visitors_in_quarter - (10 * days_in_quarter),</span></div><div class='line'><span style="color: #24292F"> daily_rate, visitor_rate(visitors_in_quarter),</span></div><div class='line'><span style="color: #24292F"> adjusted_daily_rate, visitor_rate(adjusted_visitors_in_quarter),</span></div><div class='line'><span style="color: #116329">+ format(adjusted_daily_rate, daily_rate)</span></div><div class='line'><span style="color: #24292F">)</span></div></code></div></pre> <p>Let's annotate this code again using Avdi's story steps. We'll omit some of the code (indicated by "...") for brevity.</p> <pre class="shiki github-light" style="background-color: #ffffff; color: #24292f"><div class="language-id">sql</div><div class='code-container'><code><div class='line'><span style="color: #CF222E">=</span><span style="color: #24292F">LET(</span></div><div class='line'><span style="color: #24292F"> visitor_rate, ...,</span></div><div class='line'><span style="color: #24292F"> </span><span style="color: #0550AE">format</span><span style="color: #24292F">, ...,</span></div><div class='line'></div><div class='line'><span style="color: #24292F"> </span><span style="color: #6E7781">/* 1. Gather input */</span></div><div class='line'><span style="color: #24292F"> visitors_in_quarter, B2,</span></div><div class='line'><span style="color: #24292F"> days_in_quarter, C2,</span></div><div class='line'></div><div class='line'><span style="color: #24292F"> </span><span style="color: #6E7781">/* 2. Perform work */</span></div><div class='line'><span style="color: #24292F"> adjusted_visitors_in_quarter, visitors_in_quarter </span><span style="color: #CF222E">-</span><span style="color: #24292F"> (</span><span style="color: #0550AE">10</span><span style="color: #24292F"> </span><span style="color: #CF222E">*</span><span style="color: #24292F"> days_in_quarter),</span></div><div class='line'><span style="color: #24292F"> daily_rate, visitor_rate(visitors_in_quarter),</span></div><div class='line'><span style="color: #24292F"> adjusted_daily_rate, visitor_rate(adjusted_visitors_in_quarter),</span></div><div class='line'></div><div class='line'><span style="color: #24292F"> </span><span style="color: #6E7781">/* 3. Handle failure - not relevant */</span></div><div class='line'></div><div class='line'><span style="color: #24292F"> </span><span style="color: #6E7781">/* 4. Deliver results */</span></div><div class='line'><span style="color: #24292F"> </span><span style="color: #0550AE">format</span><span style="color: #24292F">(adjusted_daily_rate, daily_rate)</span></div><div class='line'><span style="color: #24292F">)</span></div></code></div></pre> <p>Reading this, we understand that there's a visitor rate step and a format step — but we don't necessarily need to know all the details of them for now.</p> <p>In conclusion, <code>LAMBDA</code> helps you organize and reuse your code, especially in response to changing needs. This helps keep the readers' focus on what's important, and they can always dig into details as needed.</p> <h3 id="bonus-technique-use-custom-functions-to-name-formulas" tabindex="-1">Bonus technique: Use custom functions to name formulas<a href="#bonus-technique-use-custom-functions-to-name-formulas" class="heading-permalink" aria-label="Permalink for this section"><svg class="usa-icon" aria-hidden="true" focusable="false" role="img"><use xlink:href="#svg-link"></use></svg></a></h3> <p>Even with our readable code, one problem remains. Every time someone looks at this formula, they have to read 16 lines of code to understand it.</p> <p>In this case, the easiest-to-read solution would be to name the formula. How about something like, "daily visitor rate"?</p> <p>With the custom formula creator available in major spreadsheet programs, we can do exactly that.</p> <p><strong>In Google Sheets:</strong></p> <ul> <li>Right-click on the cell containing the formula</li> <li>Click "View more cell actions", then "Define named function"</li> <li>Give the function a name like <code>DAILY_VISITOR_RATE</code>.</li> <li>In "Argument placeholders," add <code>visitors_in_quarter</code> and <code>days_elapsed_in_quarter</code>.</li> <li>In the formula definition, delete the two lines that set those variables. Those variables will now be added directly to the formula, so we don't need the cell references.</li> </ul> <pre class="shiki github-light" style="background-color: #ffffff; color: #24292f"><div class="language-id">sql</div><div class='code-container'><code><div class='line'><span style="color: #24292F"> visitors_in_quarter, B2,</span></div><div class='line'><span style="color: #24292F"> days_elapsed_in_quarter, C2,</span></div></code></div></pre> <p>Then, save the function.</p> <p>Now you can replace the formula with the named function:</p> <pre class="shiki github-light" style="background-color: #ffffff; color: #24292f"><div class="language-id">sql</div><div class='code-container'><code><div class='line'><span style="color: #CF222E">=</span><span style="color: #24292F">DAILY_VISITOR_RATE(B2, C2)</span></div></code></div></pre> <p>Before, anyone would have to read all the code or comments to understand what was happening. Now, it's probably clear from just the name. And if they need to understand the inner workings of this formula, they can look it up in the list of custom formulas.</p> <h3 id="summary" tabindex="-1">Summary<a href="#summary" class="heading-permalink" aria-label="Permalink for this section"><svg class="usa-icon" aria-hidden="true" focusable="false" role="img"><use xlink:href="#svg-link"></use></svg></a></h3> <p>We started with this:</p> <pre class="shiki github-light" style="background-color: #ffffff; color: #24292f"><div class="language-id">sql</div><div class='code-container'><code><div class='line'><span style="color: #CF222E">=IF</span><span style="color: #24292F">(C2 </span><span style="color: #CF222E">=</span><span style="color: #24292F"> </span><span style="color: #0550AE">0</span><span style="color: #24292F">,</span></div><div class='line'><span style="color: #24292F"> </span><span style="color: #0A3069">"Data is pending."</span><span style="color: #24292F">,</span></div><div class='line'><span style="color: #24292F"> </span><span style="color: #CF222E">CONCATENATE</span><span style="color: #24292F">(</span><span style="color: #0A3069">"visitors per day: "</span><span style="color: #24292F">, </span><span style="color: #CF222E">TEXT</span><span style="color: #24292F">(</span><span style="color: #0550AE">ROUND</span><span style="color: #24292F">(B2 </span><span style="color: #CF222E">/</span><span style="color: #24292F"> C2)))</span></div><div class='line'><span style="color: #24292F">)</span></div></code></div></pre> <p>We're ending with this:</p> <pre class="shiki github-light" style="background-color: #ffffff; color: #24292f"><div class="language-id">sql</div><div class='code-container'><code><div class='line'><span style="color: #CF222E">=</span><span style="color: #24292F">DAILY_VISITOR_RATE(B2, C2)</span></div></code></div></pre> <p>And behind that named function is a formula with clear variable names using <code>LET</code>, separated components using <code>LAMBDA</code>, and comments to clarify the code. This approach is a relatively new option available to us.</p> <p>Using software design principles and practices, our formula is much easier to read, understand, and change. Future readers will thank us — including ourselves, because we're future readers too!</p> <p>Code that is easier to read is easier to understand. And code that is easier to understand is easier to change without fear and maintain over time. Go forth and write fearless formulas!</p> </div> </div> <aside class="tablet:grid-offset-1 tablet:grid-col-4"> <div class="bg-primary-darker padding-3 text-white usa-dark-background radius-md"> <h2 class="font-sans-lg text-bold margin-bottom-2">Follow 18F</h2> <ul class="usa-list usa-list--unstyled"> <li class="margin-bottom-2"> <a href="https://github.com/18F" class="usa-link--alt usa-link--external" rel="noreferrer" > <img src="/img/github-lightest-dnvvdJeNxl.svg" class="maxw-3 margin-right-2 text-sub" alt="" loading="lazy" decoding="async">18F on GitHub </a> </li> <li class="margin-bottom-2"> <a href="https://twitter.com/18F" class="usa-link--alt usa-link--external" rel="noreferrer" > <img src="/img/twitter-lightest-MyHkS8cnLa.svg" class="maxw-3 margin-right-2 text-sub" alt="" loading="lazy" decoding="async">18F on Twitter </a> </li> <li class="margin-bottom-2"> <a href="https://www.linkedin.com/company/gsa18f" class="usa-link--alt usa-link--external" rel="noreferrer" > <img src="/img/linkedin-lightest-hDYpqtBBKD.svg" class="maxw-3 margin-right-2 text-sub" alt="" loading="lazy" decoding="async">18F on LinkedIn </a> </li> <li class="margin-bottom-2"> <a href="/feed.xml" class="usa-link--alt " > <img src="/img/rss-lightest-29xxdTjXfa.svg" class="maxw-3 margin-right-2 text-sub" alt="" loading="lazy" decoding="async">RSS feed </a> </li> </ul> </div> </aside> </div> </div> </article> <section class="margin-top-6 post-pagination"> <div class="grid-container"> <hr class="hr-1-base-lighter"> <div class="grid-row grid-gap"> <div class="grid-col-6"> <a href="/2024/10/08/18f-year-of-launches/" class="text-no-underline post-pagination__link" > <h2 class="post-pagination__heading"> <svg class="icon caret" xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" version="1.1" x="0px" y="0px" width="512px" height="512px" viewBox="0 0 444.531 444.531" style="enable-background:new 0 0 444.531 444.531;" xml:space="preserve" role="img" aria-hidden="true"> <title>Arrow left</title> <desc>Arrow pointing to the left</desc> <g> <path d="M213.13,222.409L351.88,83.653c7.05-7.043,10.567-15.657,10.567-25.841c0-10.183-3.518-18.793-10.567-25.835 l-21.409-21.416C323.432,3.521,314.817,0,304.637,0s-18.791,3.521-25.841,10.561L92.649,196.425 c-7.044,7.043-10.566,15.656-10.566,25.841s3.521,18.791,10.566,25.837l186.146,185.864c7.05,7.043,15.66,10.564,25.841,10.564 s18.795-3.521,25.834-10.564l21.409-21.412c7.05-7.039,10.567-15.604,10.567-25.697c0-10.085-3.518-18.746-10.567-25.978 L213.13,222.409z" fill="#046b99"/> </g> </svg> Previous post </h2> <p class="measure-1 text-bold text-primary-darkest">18F’s year of launches</p> </a> </div> <div class="grid-col-6 text-right"> <a href="/2024/12/05/2024-12-5-18f-project-defaults/" class="text-no-underline post-pagination__link" > <h2 class="post-pagination__heading"> Next post <svg class="icon caret" xmlns="http://www.w3.org/2000/svg" width="444.819" height="444.819" viewBox="0 0 444.819 444.819" role="img" aria-hidden="true"> <title>Arrow right</title> <desc>Arrow pointing to the right</desc> <path fill="#046b99" d="M352.025 196.712L165.885 10.848C159.028 3.615 150.468 0 140.185 0s-18.84 3.62-25.696 10.848l-21.7 21.416c-7.045 7.043-10.567 15.604-10.567 25.692 0 9.897 3.52 18.56 10.566 25.98L231.544 222.41 92.785 361.168c-7.04 7.043-10.563 15.604-10.563 25.693 0 9.9 3.52 18.566 10.564 25.98l21.7 21.417c7.043 7.043 15.612 10.564 25.697 10.564 10.09 0 18.656-3.52 25.697-10.564L352.025 248.39c7.046-7.423 10.57-16.084 10.57-25.98.002-10.09-3.524-18.655-10.57-25.698z"/> </svg> </h2> <p class="measure-1 float-right text-bold text-primary-darkest text-right">18F project defaults</p> </a> </div> </div> <hr class="hr-1-base-lighter"> </div> </section> <section class="margin-top-3 margin-bottom-6"> <div class="grid-container"> <ul class="grid-row grid-gap usa-list--unstyled"> <li class="grid-col-12 tablet:grid-col-6 tablet-lg:grid-col-4 margin-bottom-5 "> <a href="/2024/07/17/working-with-oracle-databases-in-open-source-projects/" class="text-no-underline"> <h3 class="border-top-05 border-primary hover-primary-dark padding-top-3 margin-bottom-3 text-bold "> Working with Oracle databases in open-source projects </h3> </a> Follow this step-by-step tutorial to gain access to data locked inside proprietary Oracle databases, so you can use it in your open-source project. </li> <li class="grid-col-12 tablet:grid-col-6 tablet-lg:grid-col-4 margin-bottom-5 "> <a href="/2024/12/10/how-we-measure-coaching/" class="text-no-underline"> <h3 class="border-top-05 border-primary hover-primary-dark padding-top-3 margin-bottom-3 text-bold "> How we measure the success of coaching engagements </h3> </a> We use tried and true techniques to demonstrate how we're serving the American public. </li> <li class="grid-col-12 tablet:grid-col-6 tablet-lg:grid-col-4 margin-bottom-5 "> <a href="/2016/04/08/how-we-get-high-availability-with-elasticsearch-and-ruby-on-rails/" class="text-no-underline"> <h3 class="border-top-05 border-primary hover-primary-dark padding-top-3 margin-bottom-3 text-bold "> How we get high availability with Elasticsearch and Ruby on Rails </h3> </a> If you’re already using Ruby on Rails and Elasticsearch, check out our replacement Rake tasks for the Elasticsearch Rails gem. </li> </ul> </div> </div> </section> </main> <footer> <div class="grid-container padding-y-6"> <hr class="hr-1-dark footer-hr-alignment"> <div class="grid-row grid-gap"> <div class="tablet:grid-col-3"> <img src="/img/18f-logo-60i14872OF.svg" class="maxw-5" alt="18F logo" loading="lazy" decoding="async"> <p> Work with us to plan successful projects, choose better vendors, build custom software, or learn how to work in new ways. </p> <a href="/contact/" class="usa-button an18f-button--dark" > Contact us </a> </div> <div class="tablet:grid-offset-1 tablet:grid-col-2"> <h2 class="font-heading-md text-medium margin-top-4 tablet:margin-top-0 margin-bottom-2 tablet:margin-bottom-4"> Pages </h2> <ul class="usa-list usa-list--unstyled font-sans-sm list-item-spacing-2"> <li><a href="/our-work/">Our work</a></li> <li><a href="/work-with-us/">Work with us</a></li> <li><a href="/about/">About 18F</a></li> <li><a href="/guides/">Guides</a></li> <li><a href="/blog/">Blog</a></li> <li><a href="/contact/">Contact</a></li> </ul> </div> <div class="tablet:grid-col-2"> <h2 class="font-heading-md text-medium margin-top-4 tablet:margin-top-0 margin-bottom-2 tablet:margin-bottom-4"> Policies </h2> <ul class="usa-list usa-list--unstyled font-sans-sm list-item-spacing-2"> <li><a href="/linking-policy/">Linking policy</a></li> <li><a href="/open-source-policy/">Open source policy</a></li> <li><a href="/vulnerability-disclosure-policy/">Vulnerability disclosure</a></li> <li><a href="/code-of-conduct/">Code of conduct</a></li> </ul> </div> <div class="tablet:grid-col-2"> <h2 class="font-heading-md text-medium margin-top-4 tablet:margin-top-0 margin-bottom-2 tablet:margin-bottom-4"> Contact </h2> <ul class="usa-list usa-list--unstyled font-sans-sm list-item-spacing-2"> <li> <a href="/contact/" > Get in touch </a> </li> <li> <a href="/about/#for-press" > Press </a> </li> <li> <a href="https://github.com/18F/18f.gsa.gov/issues/new/choose" class="usa-link--external" > Report a bug </a> </li> <li> <a href="/join/" > Join 18F </a> </li> </ul> </div> <div class="tablet:grid-col-2"> <h2 class="font-heading-md text-medium margin-top-4 tablet:margin-top-0 margin-bottom-2 tablet:margin-bottom-4"> Social </h2> <ul class="usa-list usa-list--unstyled font-sans-sm list-item-spacing-2"> <li class="display-flex flex-align-center"> <a href="https://github.com/18F" class="usa-link--external" rel="noreferrer" > <img src="/img/github-dark-2-w9jCrv94IZ.svg" class="maxw-205 margin-right-1 text-tbottom" alt="" loading="lazy" decoding="async">GitHub</a> </li> <li class="display-flex flex-align-center"> <a href="https://twitter.com/18F" class="usa-link--external" rel="noreferrer" > <img src="/img/twitter-dark-Pq04PjchNH.svg" class="maxw-205 margin-right-1 " alt="" loading="lazy" decoding="async">Twitter</a> </li> <li class="display-flex flex-align-center"> <a href="https://www.linkedin.com/company/gsa18f" class="usa-link--external" rel="noreferrer" > <img src="/img/linkedin-dark-ILbHB_Cgfp.svg" class="maxw-205 margin-right-1 " alt="" loading="lazy" decoding="async">LinkedIn</a> </li> </ul> </div> </div> </div> <div class="usa-identifier padding-top-2"> <section class="usa-identifier__section usa-identifier__section--masthead" aria-label="Agency identifier"> <div class="usa-identifier__container"> <div class="usa-identifier__logos"> <a href="https://www.gsa.gov/" class="usa-identifier__logo"> <img src="/img/gsa-logo-blue-YKGHyQURdg.svg" class="usa-identifier__logo-img" alt="gsa logo" loading="lazy" decoding="async"> </a> </div> <div class="usa-identifier__identity text-base-lightest"> <p class="usa-identifier__identity-domain">18f.gsa.gov</p> <p class="usa-identifier__identity-disclaimer text-base-lightest">An official website of the <a href="https://www.gsa.gov/about-us/organization/federal-acquisition-service/technology-transformation-services"> GSA’s Technology Transformation Services </a> </p> </div> </div> </section> <nav class="usa-identifier__section usa-identifier__section--required-links" aria-label="Important links"> <div class="usa-identifier__container"> <ul class="usa-identifier__required-links-list"> <li class="usa-identifier__required-links-item"> <a class="usa-identifier__required-link usa-link" href="https://www.gsa.gov/about" title="About GSA"> About GSA </a> </li> <li class="usa-identifier__required-links-item"> <a class="usa-identifier__required-link usa-link" href="https://www.gsa.gov/website-information/accessibility-aids" title="View accessibility statement"> Accessibility support </a> </li> <li class="usa-identifier__required-links-item"> <a class="usa-identifier__required-link usa-link" href="https://www.gsa.gov/reference/freedom-of-information-act-foia" title="Submit a Freedom of Information Act (FOIA) request"> FOIA requests </a> </li> <li class="usa-identifier__required-links-item"> <a class="usa-identifier__required-link usa-link" href="https://www.gsa.gov/reference/civil-rights-programs/the-no-fear-act" title="View No FEAR Act data"> No FEAR Act data </a> </li> <li class="usa-identifier__required-links-item"> <a class="usa-identifier__required-link usa-link" href="https://www.gsaig.gov/" title="Office of the Inspector General"> Office of the Inspector General </a> </li> <li class="usa-identifier__required-links-item"> <a class="usa-identifier__required-link usa-link" href="https://www.gsa.gov/reference/reports/budget-performance" title="View budget and performance reports"> Performance reports </a> </li> <li class="usa-identifier__required-links-item"> <a class="usa-identifier__required-link usa-link" href="https://www.gsa.gov/website-information/website-policies" title="Our privacy policy"> Privacy policy </a> </li> </ul> </div> </nav> <section class="usa-identifier__section usa-identifier__section--usagov" aria-label="U.S. government information and services"> <div class="usa-identifier__container"> <div class="usa-identifier__usagov-description text-base-lightest">Looking for U.S. government information and services?</div> <a href="https://www.usa.gov/" class="usa-link">Visit USA.gov</a> </div> </section> </div> </footer>  <script async src="/assets/js/app-IYN3AA5J.js"></script>  <script id="_fed_an_ua_tag" src="https://dap.digitalgov.gov/Universal-Federated-Analytics-Min.js?agency=GSA&subagency=TTS,18F"></script>  <script async src="https://www.googletagmanager.com/gtag/js?id=G-HBYXWFP794"></script> <script> window.dataLayer = window.dataLayer || []; function gtag() { dataLayer.push(arguments); } gtag('js', new Date()); gtag('config', 'G-HBYXWFP794', { 'anonymize_ip': true }); </script> </body> </html>

CINXE.COM

18F: Digital service delivery | Three ways to write fearless spreadsheet formulas