7 votes

Converting "4.8 million" into an actual number

You can use a formula like this one: =IF(A1="","",IF(ISNUMBER(A1),A1,VALUE(REGEXEXTRACT(A1,"[0-9\.]*"))*IF(REGEXMATCH(A1,"million"),1000000,IF(REGEXMATCH(A1,&...
Martín's user avatar
  • 1,366
6 votes

Formula to count rows between positive values

Use reduce(), like this: =reduce(tocol(æ, 2), A2:A, lambda(a, c, let( na, "NA", pattern, "[" & na & "]*$", streak, regexextract(join("", a), ...
doubleunary's user avatar
  • 14.5k
4 votes
Accepted

Converting "4.8 million" into an actual number

Optimized Array Formula This formula is faster than my comparable array formula at the end. I favor shorter code where possible but apparently, that doesn't always go hand-in-hand with shorter ...
Blindspots's user avatar
  • 5,150
4 votes

Formula to count rows between positive values

doubleunary's formula is a good one. If you don't want an array formula, here is a single formula that can be copied down column B stating in B1: =IF(A1>0, IF(ROW()<>1, ROW()-IFNA(XMATCH(...
Blindspots's user avatar
  • 5,150
3 votes

Formula to count rows between positive values

This is "amateur standard" (my level) but may fill the bill. Formula for cell [B 1]: =if(A1<0,"NA",row()-0) Formula for cell [B 2]: =if(A2<0,"NA",row()-sum($B$1:$B1)...
Fe2O3's user avatar
  • 151
3 votes

Formula to calculate the sum of consecutive 0's and -1's

Here's an alternative simpler answer, if you are interested: =min(scan(0,A1:A10,lambda(acc,arr,ifs(arr=-1,acc-1,arr=0,acc,true,0)))) scan the array, subtracting 1 from the accumulator each time a '-1'...
The God of Biscuits's user avatar
3 votes

alphanumerics in duration in sheets - how to convert to a number of minutes

Use regexextract(), like this: =let( get_, lambda(s, ifna(regexextract(A2, "(\d+)" & s), 0)), duration, value(join(":", get_("h"), get_("m"), get_(&...
doubleunary's user avatar
  • 14.5k
2 votes

Multiply/Concatenate String Arrays

This formula is very efficient in case you need to use larger data sets: LET(a,A1:A, b,B1:B, c,C1:C, qr,LAMBDA(q, FILTER(q,q<>"")), calc,LAMBDA(x,y, TOCOL(TOROW( BYROW(x, ...
Blindspots's user avatar
  • 5,150
2 votes
Accepted

Can an Google Sheet Filter function base its include on a whole row?

Formula =LET(arr,QUERY(A1:C, "WHERE Col1 <> ''", 1), itm,INDEX(arr,,1), kcols,CHOOSECOLS(arr, SEQUENCE(1,COLUMNS(arr)-1,2)), key,BYROW(kcols, LAMBDA(r, TEXTJOIN("-",...
Blindspots's user avatar
  • 5,150
1 vote

I wanted to set the maximum value of each unique input as active

Put the following formula in C1 (headings row) so that your data and any manipulation don't interfere with it: ={"Status"; LET(rng,A2:B, ARRAYFORMULA( IF( COUNTIF( ...
Blindspots's user avatar
  • 5,150
1 vote
Accepted

How do I make dropdown selections add value to a totals column in Google Sheets

One way is to use map() and ifs(), like this: =sum(map(D2:2, lambda(week, ifs( week = "N/A", 0, week = "", 0, week = "Week 4", 10, week = "Week 7", ...
doubleunary's user avatar
  • 14.5k
1 vote
Accepted

Formula to calculate the sum of consecutive 0's and -1's

=LET(x,TOCOL(A1:A, 1), -MAX( INDEX( LEN( SPLIT( JOIN(, IF(x<-1, 2, IF(x>0, 2, SUBSTITUTE(ABS(x), 0,)))), 2))))) As a ...
Blindspots's user avatar
  • 5,150
1 vote

Multiply/Concatenate String Arrays

You can create a cartesian product by just concatting (&) first array with the TRANSPOSEd second array and then FLATTENning the result: =LET( cart_prod, LAMBDA( a, b, ...
TheMaster's user avatar
  • 3,352
1 vote
Accepted

Multiply/Concatenate String Arrays

You are asking how to create a Cartesian product of three sets. Assuming the data is in A2:C, try something like this: =let( table, A2:C, blank, iferror(ø), first_, lambda(array, tocol(...
doubleunary's user avatar
  • 14.5k

Only top scored, non community-wiki answers of a minimum length are eligible