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

Question

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)

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?

Dorotheadorothee

Not an answer to your question, but possibly a solution for your problem. You can do this by creating an appropriate macro that can calculate that and output the progress bar for you. For full instructions, check this page out. – Canicula

I fell years later on this question. Google still leads there, so it's worth mentioning that LET now exists in Google Spreadsheet formula and works exactly as the OP described it: support.google.com/docs/answer/13190535?hl=en – Campion

Answer 1

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.

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

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.

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.

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

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

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:

(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.

Sundsvall

Answer 2

Years later, around 2020, Microsoft Excel then Google Spreadsheet introduced the LET formula function, working exactly as the OP described it, and which is documented here.

Syntax

LET(name1, value_expression1, [name2, …], [value_expression2, …], formula_expression )

Sample 1

LET(avg, AVERAGE(B2:D2), IF(avg>=4, "Great", IF(avg>=3, "Good", "Poor")))

Sample 2

LET(criteria, "Fred", range, FILTER(A2:D8, A2:A8=criteria), ARRAYFORMULA(IF(ISBLANK(range), "-", range)))

Campion

Answer 3

Can the spreadsheet formula SPARKLINE be a solution for you?

=SPARKLINE(10,{"charttype","bar";"max",20})

Kathikathiawar

I'll be damned! Yes it can: =SPARKLINE(F3; {"charttype"\"bar"; "max"\max(F$1:F$999)}) I don't know what backslash means here, but with comma it didn't work. Thanks a lot! But I'll leave this question open for a generic way to define variables in complex formulas. – Dorotheadorothee

This answer doesn't actually address the question. – Domiciliary