It’s a little tricky because of the nested IFs but here is my answer (confirmed in Google Spreadsheets): =IF(AND(A2>=0, A2<500), “Less than 500”, IF(AND(A2>=500, A2<1000), “Between 500 and 1000”, IF(AND(A2>=1000, A2<1500), “Between 1000 and 1500”, IF(AND(A2>=1500, A2<2000), “Between 1500 and 2000”, “Undefined”))))
Short answer Use an absolute cell reference or a named range Explanation Instead of E1, which is a relative cell reference, use $E$1 which is an absolute cell reference. An alternative is to to assign a name to the cell E1, let say, “constant” In the first case the formula will be =A2*$E$1 In the … Read more
I wrote a simple general custom function, which is 100% reusable you can unpivot / reverse pivot a table of any size. In your case you could use it like this: =unpivot(A1:D4,1,1,”customer”,”sales”) So you can use it just like any built-in array function in spreadsheet. Please see here 2 examples: https://docs.google.com/spreadsheets/d/12TBoX2UI_Yu2MA2ZN3p9f-cZsySE4et1slwpgjZbSzw/edit#gid=422214765 The following is the … Read more
You may replace all non-digit characters using the \D+ regex and an empty string replacement with =REGEXREPLACE(A11,”\D+”, “”) or with casting it to a number: =VALUE(REGEXREPLACE(A11,”\D+”, “”))
you can either use alternative to GOOGLEFINANCE (depends on what exactly are you up to) or if you want to stick with it you can wrap it into IFERROR: =IFERROR(GOOGLEFINANCE(your_formula_here), GOOGLEFINANCE(same_formula_here)) or even: =IFERROR(IFERROR( GOOGLEFINANCE(your_formula_here), GOOGLEFINANCE(same_formula_here)), GOOGLEFINANCE(same_formula_here))
No, there’s no equivalent to Excel’s EVALUATE() in Google Sheets. There’s long history behind this one, see this old post for instance. If you’re just interested in simple math (as shown in your question), that can be done easily with a custom function. function doMath( formula ) { // Strip leading “=” if there if … Read more
Let’s say your arrays are: A1:A3 = {1;2;3} and B1:B3 = {4;5;6} Write somewhere: ={A1:A3;B1:B3} Semicolons ; are used to separate rows, and commas , are for columns. Here is the documentation: Using arrays in Google Sheets
One option: =COUNTIF(B:B; “Mammal”) + COUNTIF(B:B; “Bird”) According to the documentation: Notes COUNTIF can only perform conditional counts with a single criterion. To use multiple criteria, use COUNTIFS or the database functions DCOUNT or DCOUNTA. COUNTIFS: This function is only available in the new Google Sheets. Example: =DCOUNTA(B:B; 2; {“Type”; “Mammal”; “Bird”})
If you want something that just works in Google Spreadsheets (as the title suggests), you can use open-ended ranges: =SUM(A2:A) In Excel, you can specify the maximum rows for that version; for example, for 2007 and 2010: =SUM(A2:A1048576) This will work in Google Spreadsheets as well, and is beyond the current theoretical row limit in … Read more
Edit: this one is simplier: =REGEXEXTRACT(A1,”[^/]+$”) You could use this formula: =REGEXEXTRACT(A1,”(?:.*/)(.*)$”) And also possible to use it as ArrayFormula: =ARRAYFORMULA(REGEXEXTRACT(A1:A3,”(?:.*/)(.*)$”)) Here’s some more info: the RegExExtract function Some good examples of syntax my personal list of Regex Tricks This formula will do the same: =INDEX(SPLIT(A1,”/”),LEN(A1)-len(SUBSTITUTE(A1,”/”,””))) But it takes A1 three times, which is not … Read more