How to apply + and  to numbers in Google Sheets
How to apply a positive or negative to values from one column to another column? (see image: for example in the first gray column and first white column, I want "3,2" to apply to "3182" so that the result would automatically output in another column as "3480").
One of the programmers I hired (who no longer works on the project) deleted tabs in Google Sheets without my knowledge which showed the entire spreadsheets values and instead added some type of formula (why he did this I have no idea since all the raw data needs to be imported into MySQL).
2 answers

try:
=REGEXEXTRACT(A24, "(.+)")+REGEXEXTRACT(B24, "(.+),")&""& REGEXEXTRACT(A24, "(.+)")+REGEXEXTRACT(B24, ",(.+)")
for arrayformula do:
=ARRAYFORMULA(REGEXEXTRACT(A2:A, "(.+)")+REGEXEXTRACT(B2:B, "(.+),")&""& REGEXEXTRACT(A2:A, "(.+)")+REGEXEXTRACT(B2:B, ",(.+)"))

Another way without regex:
= arrayformula( query ({split(B2:B3,","),split(SUBSTITUTE(A2:A3,"",","),",")}, "Select Col1+Col3 label Col1+Col3 ''") & query ({split(B2:B3,","),split(SUBSTITUTE(A2:A3,"",","),",")}, "Select Col2+Col4 label Col2+Col4 ''"))
See also questions close to this topic

End of line not followed by dot
I need to find 2nd and 3rd line, except 1st.
test. test, test_
But pattern
(?!\.)$
is not working.

javascript regular expression global flag
I am validating an array of IPv4 addresses using javascript regex.
I am getting unexpected result when I use theglobal
flag.
I run the code using Node 10 & 13 and on chrome browser and they all gave the same result.var ips = ['22.231.113.64', '22.231.113.164', '22.231.113.164'] const pv4 = /^(?:(?:25[05]2[04][09][01]?\d{1,2})\.){3}(?:25[05]2[04][09][01]?\d{1,2})$/g console.log( ips.map(x => pv4.test(x)) )
the above code is giving me
[true, false, true]
Notice that the one ip that gave me false is exactly the same as the one behind it that gave me true (on index 1,2)
If I run it without the global flag, and I got what I expect
[true, true, true]

How to concatinate string with star character Ansible?
I have a list of paths stored in files_path variable. This is a task where I am trying to concatenate paths from variable and adding the rest of the path using join.
 name: Changing supervisor files path replace: path: /etc/supervisor/supervisord.conf regexp: 'files(.*)' replace: 'files = /etc/supervisor/conf.d/*.conf {{ supervisor_files_path  join(' ')/shared/supervisor/*.conf }} notify: restart supervisor when: files_path is defined
The error I get:
fatal: [127.0.0.1]: FAILED! => {"msg": "template error while templating string: unexpected '*'. String: files = /etc/supervisor/conf.d/*.conf {{ files_path  join(' ')/shared/supervisor/*.conf }}"}
The issue is with the second star in code here:
/shared/supervisor/*.conf
I was trying to escape using
\*
or[*]
, but getting the same error.How should I change this?

All Combinations of Items in Boxes
There are lots of questions and answers for combination algorithms but I can't find one on the problem that I'm trying to solve.
If I have a set of items and a set of boxes, how do I find all the combinations of items in boxes? All the items must be used, order doesn't matter and all boxes are treated the same with no size limit. I can sit down with a pen and paper to draw out all the combinations but I can't work out what algorithm my brain is using.std::vector<char> unsorted = { 'a', 'b', 'c', 'd' }; int box_count = 3; std::vector<std::vector<std::vector<char>>> sorted = {}; sorted = FillBoxes(unsorted, box_count); output << sorted = { { {a,b,c,d}, {}, {}}, { {a,b,c}, {d}, {} }, { {a,b,d}, {c}, {} }, { {a,b}, {c,d}, {} }, { {a,b,d}, {b}, {} }, { {a,c}, {b,d}, {} }, { {a,d}, {b,c}, {} }, { {a}, {b,c,d}, {} }, { {a,b}, {c}, {d} }, { {a,c}, {b}, {d} }, { {a,d}, {b}, {c} }, { {a}, {b,c}, {d} }, { {a}, {b,d}, {c} }, { {a}, {c,d}, {b} } }
I have had a few ideas involving binary trees and iterative pointers but none of them have worked.
It looks like a solvable problem but if it is mathematically impossible, I'd appreciate the feedback too.
Thanks!
(prefered language c++ but I can read most common programming languages) 
Weird sign issue with mapping from cartesian to bispherical coordinates
I want to map cartesian coordinates (x,y,z) to bispherical coordinates (sigma,tau,phi) and back accourding to wikipedia.
Here are my functions:
public static Vector3 ProjectBisphericalToCartesian(Vector3 bispherical, double a) { var sigma = (double)bispherical.X; var tau = (double)bispherical.Y; var phi = (double)bispherical.Z; var d = Math.Cosh(tau)  Math.Cos(sigma); var s = a / d; var x = s * Math.Sin(sigma) * Math.Cos(phi); var y = s * Math.Sin(sigma) * Math.Sin(phi); var z = s * Math.Sinh(tau); return new Vector3( (float)x, (float)y, (float)z ); } public static Vector3 ProjectCartesianToBispherical(Vector3 cartesian, double a) { var x = (double)cartesian.X; var y = (double)cartesian.Y; var z = (double)cartesian.Z; var R = Math.Sqrt(x * x + y * y + z * z); var s = R * R + a * a; var t = 2.0 * a * z; var Q = Math.Sqrt(s * s  t * t); var sigma = Math.Acos((R * R  a * a) / Q); var tau = Asinh(t / Q); var phi = Math.Atan(y / x); return new Vector3( (float)sigma, (float)tau, (float)phi ); } // sinh^1 ("areasinus hyperbolicus") private static double Asinh(double x) { return Math.Log(x + Math.Sqrt(x * x + 1.0)); }
I test the function by generating random numbers for (x,y,z) and a, convert them to bispherical coordinates and back to cartesian and check whether or not they are equal to the original cartesian corrdinates (up to some small tolerance).
It turns out that some cartesian coordinates flip the sign of its x and y component (z is okay) while other's do not. For example {X:5,3434 Y:2,569566 Z:1,195607} flips the sign of the x and y component while {X:7,586471 Y:6,154929 Z:1,494778} works fine.
I can not find an error in my code unfortunately. The issue appears to be caused by negative x values in the original cartesian coordinates which will cause the sign of x and y to be flipped after conversion and back. I just don't see why that happens.
Does anybody have an idea what is going on here ?

How to randomly choose coefficients of a linear combination in Python?
I have a linear combination of some functions as
I want to randomly select the coefficients
c_i
with the constraintAs a minimal example in Python:
import numpy as np x = 0.1 func_list = [np.sin(x), np.cos(x), x**2] def linear_comb(a, b, c): return a*func_list[0] + b*func_list[1] + c*func_list[2]
How should I sample
a
,b
andc
from uniform distributions with the constrainta^2+b^2+c^2=1
? 
How can I filter by comparing a column to its own row items
I have a bunch of data being pushed in a google sheet row by row. Each row has a reference number (COL A) and a date (COL D). For every reference number, I want to find the latest updated row. It's necessary that this is done by arrayformula.
So far I have a combination of arrayformula and filter. However it doesn't work as expected as one of the filter arguments is A2:A=A2:A. Obviously this is always true. What I want it to do is compare A2:A=A2 for row 2, then compare A2:A=A3 for row 3 etc. Without dragging down. Since the data is growing automatically
=arrayformula(if(len(A2:A),max(filter({D2:D},A2:A=A2:A)),))
https://docs.google.com/spreadsheets/d/16pbGiisFcsrHfrFKowzzkpw03Lw79yACjut96xxyWW4/edit?usp=sharing

Create a Google Form that responses go into a specific column on an existing spreadsheet
I have an existing Google Sheet which I would like to create a form for so users can input data more easily.
The form would have three questions and I would like them to go into their corresponding columns on the existing spreadsheet.
Question 1 would go into Column 1, Question 2 would go into Column 3 on the same row and Question 3 would go into Column 5 on the same row.
Does anyone know how I can do this or an Apps script for this please?

How to use the ezsheets python module with Google Sheets
I am having trouble getting the ezsheets python module working. My code gives me an error
OSError: [WinError 10013] An attempt was made to access a socket in a way forbidden by its access permissions
import ezsheets ezsheets.listSpreadsheets()
I have obtained the credentials.json file and renamed it to credentialssheets.json as required. The docs also specify
Once you have a credentialssheets.json file, run the import ezsheets module. The first time you import the EZSheets module, it will open a new browser window for you to log in to your Google account. Click Allow.
After you log in, the browser window will prompt you to close it, and the tokensheets.pickle and tokendrive.pickle files will appear in the same folder as credentialssheets.json. You only need to go through this process the first time you run import ezsheets
However nothing happens even if I have a single line of code for
import ezsheets
. No pickle files are created and no browser window opens. How can I get this working? 
I am trying to get data in google sheets from one sheet to another and manipulate the received data on the new sheet
I have a sheet from a forms survey. The data in the sheet has the following headers:
B :
Name1
,
C :Name1_Grade
,
D :Name1_Shoe_Size
,
E :Name2
,
F :Name2_Grade
,
G :Name2_Shoe_Size
,
H :Name3
,
I :Nanme3_Grade
,
J :Name3_Shoe_Size
The response form sheet has much more data in it. So, I use a query to get this data into a new sheet,
=query('Data !B2:J150,"Select B, C, D, E, F, G, H, I where (C=4 OR F=4 OR I=4)"
.This gets my data into the new sheet, however, once it's in the sheet I need to move all of the Names, grades and shoe size to 3 columns so the end result, once the data is on the new sheet, would look like:
Names  Grade  Shoe_Size John F  4  6.5
The formula above works, however, I need to get all the names, grades, and shoe sizes into one column. Not everyone has multiple entries. So the formula needs to be able to check if something is in the other columns. The form that generates the data is set up so that one person can fill out the form for a whole family with up to 5 kids.
Is there an easy way to accomplish this in one formula?
Here is a link to a sample spreadsheet: https://docs.google.com/spreadsheets/d/1P9qgh0o3gjDikuaX75bBr9RyYz9ofxedAuJy0tXuRko/edit?usp=sharing

Conditional formatting if cell contains time format hh:mm
I have tried:
=$H4="8:159:00"
but failed.What I want is to color a row if a certain cell contains i.e 8:159:00 or 12:0012:25 or the like.
I have searched almost all Google's suggestions and stack overflow but I can't seem to get it right. Is it possible?

How to calculate Current/Longest Streak of income vs Goal in Google Sheets
I have a list of People, tracking the number of units sold per month. They have their goals listed in the sheet, so each month has 2 columns ("units sold" and "vs goal" which is calculated
units sold  goal
). I want to calculate the current streak, counting meeting their goal or exceeding it as a win and not meeting as a loss, and their longest win streak.Sheet included with manually entering what the values should be for current and longest streak. https://docs.google.com/spreadsheets/d/1YE8EzBKIeHetKSXP6J7KiDfBkiZDv9dSurUDLHmLS0U/edit?usp=sharing

How to Concatenate a Select with the query()?
I'm use the query function, and what I want is to combine columns A, B, and C into a string with hyphens separating them. i.e. "246".
ABC  246
How could I do this?
I've tried the following but it returns an error:
=query(raw!A1:C, "select 'A & '' & B & '' &C")