google-sheetsformulalocal-variables

Can I define a local value (or variable) in a Google Spreadsheet formula?

Sometimes I come up with long spreadsheet formulas, such as this one to create "data bars" using Unicode characters (addresses are relative to G3):

= rept("█"; floor(10 * F3 / max(F$1:F$999)))
  & mid(" ▏▎▍▌▋▊▉█"; 
        1 + round(8 * (        10 * F3 / max(F$1:F$999)
                       - floor(10 * F3 / max(F$1:F$999)))); 
        1)

data bars

It would be nice to have some kind of let() to define local variables:

= let('x', 10 * F3 / max(F$1:F$999), 
      rept("█"; floor(x))
      & mid(" ▏▎▍▌▋▊▉█"; 1 + round(8 * (x - floor(x))); 1))

Does such a thing exist?

If not, are there any clever hacks to achieve the same result inside the formula? (without using another cell)

Edit: this is not a good example, because the sparkline() function already does this kind of bar chart (thanks Harold!) but the question still stands: how to clean up complex formulas and avoid repetition, apart from using additional spreadsheet cells?

Solution

  • I know the OP had their problem solved, but in case someone else finds this while searching, to answer the question in the title:

    Yes, just like in other spreadsheet programs, you can name variables for Google Sheets. To do so, in the "name box" (above the spreadsheet, to the left of "fx") where you would typically put in the address of a cell or range of cells.

    If you click on the box (or hit Ctrl+J) then type a name in this box that does not match a cell address, it will name the highlighted cell (or range) that value and store it in the sheet. You can also manage these named variables with Data / Named Ranges in the menu.

    enter image description here

    This will bring up a sidebar where you can press "+ Add a range" to add a named range / variable.

    enter image description here

    You can then type in what you want to name you range / variable as well as select the range you want it to refer to, then press done.

    enter image description here

    That being said, that would not solve the OPs issue - the OP wants a variable localized within a formula. That is also something you can do in Google Sheets (that, last I checked, you CAN'T do in Excel). In Google Sheets, you can now used named functions by going to Data / Named functions.

    enter image description here

    This brings up the "Named functions" sidebar - and you can select "Add new function" at the bottom.

    enter image description here

    This then brings up the "New named function" sidebar.

    enter image description here

    Here, you can enter the name of your function, any variables you want to be "local" to the function (as "argument placeholders"), and then the actual formula.

    This is how it would look for the OPs desired function:

    enter image description here

    (NOTE: Being a US user, I had to use commas instead of semicolons. The OP must be from a country that uses semicolons as a separator instead. Use the appropriate separator for your region)

    Now to use the function, you just need to call it like any other spreadsheet function in the cell(s) you need it. For the OPs question, it would look like this in the appropriate cell:

    =DATABAR( 10 * F3 / max(F$1:F$999) )

    In the end, SPARKLINE did the job the OP was trying to do, but if someone is searching for a more generic application, I hope this answer helps them.