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

MongoDB : use string manipulation in aggregation pipeline
I have a little app built with NestJS latest and i'm using Mongoose v5.
I have filled the database with 500+ jobs references. I writed a search function with aggregation pipeline to match the correct elements from my collection :
const query = await this.jobsModel.aggregate([ { $match : { name: { $regex: jobName, $options: 'i' } }}, { $project : { id:1, name:1, familyId:1, familyColor:1, svgListIcon:1, _id:1}}, { $addFields : { searchName : this.createSearchName('$name') }}, { $sort : { 'name': 1 }}, ]) return query;
And i would like to parse each $name field to use a regex and replace all \r\n from $name field :
createSearchName(jobName) { return jobName.replace(/\r\n/," "); }
Unfortunately, this doesn't work because the .replace() doesn't take the jobName parameter, and only using '$name'...
What is the correct way to achieve this action ?
Do i need to do 1 query, parse the result, then do an aggregation pipeline ?

Batch renaming zipfiles using REGEX
I read some articles here but REGEX is not my fortÃ©.
My problem is: I need to rename a set of google drive zip files. I tested and I know that this REGEX here ^([\w\s]+)(?:(\d{4}\d{2}\d{2}T(\d+Z)))(\d*)(.zip) does part of the JOB.
The actual naming of one of those files is Andre machado ATfinancas Comportamentais20200923T101614Z001.zip When I apply this regex over this string, for testing purposes, the result is a set of 4 groups and my intention here is having the final name as <Group #1 Group #2 Group #5>.
The problem is that I dont know how to do it. Can anyone help me?
EDIT: Expected output
 ATfinancas Comportamentais001.zip
When I apply the REGEX I get:
 Group #1  ATfinancas Comportamentais
 Group #2  20200923T101614Z
 Group #3  101614Z
 Group #4  001
 Group #5  .zip
I have lots of files with that kind of naming rules.

How to "find" and "replace" in Regex python
How to find a string in a content and replace the string with some other string ??
for file in list_of_file: # iterate through each file with open(path.join(featureFile_path, file), 'r') as fh: # open file to read file_content = fh.read() for step in file_content: if 'Hi this is the string to find' in step: # have to replace the old step step_sentence = step.replace('this is the string to replace')```

Where does the 0.5 come from or how to get the parameter is 0.5?
From the Javadoc of java.util.HashMap:
Ideally, under random hashCodes, the frequency of nodes in bins follows a Poisson distribution (http://en.wikipedia.org/wiki/Poisson_distribution) with a parameter of about 0.5 on average for the default resizing threshold of 0.75, although with a large variance because of resizing granularity. Ignoring variance, the expected occurrences of list size k are (exp(0.5) * pow(0.5, k) / factorial(k)).
Where does the 0.5 come from or how to get the parameter is 0.5? I want to konw the process.

I want to know the physical principles about Android dynamic animation
Android Developers official document:
Whenever possible, your animations should apply realworld physics so they are naturallooking. For example, they should maintain momentum when their target changes, and make smooth transitions during any changes.
To provide these behaviors, the Android Support library includes physicsbased animation APIs that rely on the laws of physics to control how your animations occur.
There are two main animation methods here. I want to know their physical principles.
FlingAnimation.java
MassState updateValueAndVelocity(float value, float velocity, long deltaT) { mMassState.mVelocity = (float) (velocity * Math.exp((deltaT / 1000f) * mFriction)); mMassState.mValue = (float) (value  velocity / mFriction + velocity / mFriction * Math.exp(mFriction * deltaT / 1000f)); if (isAtEquilibrium(mMassState.mValue, mMassState.mVelocity)) { mMassState.mVelocity = 0f; } return mMassState; }
SpringForce.java
Reference/** * Internal only call for Spring to calculate the spring position/velocity using * an analytical approach. */ DynamicAnimation.MassState updateValues(double lastDisplacement, double lastVelocity, long timeElapsed) { init(); double deltaT = timeElapsed / 1000d; // unit: seconds lastDisplacement = mFinalPosition; double displacement; double currentVelocity; if (mDampingRatio > 1) { // Overdamped double coeffA = lastDisplacement  (mGammaMinus * lastDisplacement  lastVelocity) / (mGammaMinus  mGammaPlus); double coeffB = (mGammaMinus * lastDisplacement  lastVelocity) / (mGammaMinus  mGammaPlus); displacement = coeffA * Math.pow(Math.E, mGammaMinus * deltaT) + coeffB * Math.pow(Math.E, mGammaPlus * deltaT); currentVelocity = coeffA * mGammaMinus * Math.pow(Math.E, mGammaMinus * deltaT) + coeffB * mGammaPlus * Math.pow(Math.E, mGammaPlus * deltaT); } else if (mDampingRatio == 1) { // Critically damped double coeffA = lastDisplacement; double coeffB = lastVelocity + mNaturalFreq * lastDisplacement; displacement = (coeffA + coeffB * deltaT) * Math.pow(Math.E, mNaturalFreq * deltaT); currentVelocity = (coeffA + coeffB * deltaT) * Math.pow(Math.E, mNaturalFreq * deltaT) * mNaturalFreq + coeffB * Math.pow(Math.E, mNaturalFreq * deltaT); } else { // Underdamped double cosCoeff = lastDisplacement; double sinCoeff = (1 / mDampedFreq) * (mDampingRatio * mNaturalFreq * lastDisplacement + lastVelocity); displacement = Math.pow(Math.E, mDampingRatio * mNaturalFreq * deltaT) * (cosCoeff * Math.cos(mDampedFreq * deltaT) + sinCoeff * Math.sin(mDampedFreq * deltaT)); currentVelocity = displacement * mNaturalFreq * mDampingRatio + Math.pow(Math.E, mDampingRatio * mNaturalFreq * deltaT) * (mDampedFreq * cosCoeff * Math.sin(mDampedFreq * deltaT) + mDampedFreq * sinCoeff * Math.cos(mDampedFreq * deltaT)); } mMassState.mValue = (float) (displacement + mFinalPosition); mMassState.mVelocity = (float) currentVelocity; return mMassState; }

Finding a coordinate which doesn't form a rectangle out of multiple given coordinates
We are given 4N+1 coordinate points out of which 4N coordinate points form N rectangles and one remaining point doesn't contribute to forming any rectangle. Is there a way to find this remaining last coordinate?
1<=N<=100000,
0<=X,Y<=1000000
Time limit = 2 sec

When user delete spreadsheet addon, the document properties store added with the addon continues to remain?
I'm developing Google Sheets Addon.
I was just wandering that the document properties store is deleted when users uninstall the addon. GAS don't have Trigger like 'onUninstalled'. If the properties continue to remain, I don't know where I write the program that delete them.
Who knows my problem??

importhtml not working with URL in Google Sheets
In a google sheets I try to read and import a table with this command:
=importhtml("https://www.hermesairports.com/flightinfo/pafos/arrivalsanddepartures"; "table"; 2)
result is:
#N/A
How i read the table with departures flights of the day in website https://www.hermesairports.com/flightinfo/pafos/arrivalsanddepartures ?

Extract url domain root in Google Sheet
In a table, I have lists of full urls like :
https://www.example.com/page1/productx?utmsource=google
Objective : I want to extract the domain name part of the url only :
I was using this following formula :
=REGEXEXTRACT(A1;"^(?:https?:\/\/)?(?:[^@\n]+@)?(?:www\.)?([^:\/\n?]+)")
The regex is working fine when testing it :
https://www.example.com/
However in Google sheet, It displays like :
example.com
 Why the resutls are not the same for the identical regex ?
 How to correct it in google Sheet ?

Creating a red/yellow/green conditional format for cell based on percentage increase of other cell value
I'm looking to use a conditional formatting in cells, using the Green/Amber/Red range based on a percentage increase in cell value from another target cell.
So far I've tried using a conditional format of applying red to B2 using conditional format
=$C2>=0.9
I'm not sure this is working correctly for Red (alone). How can I apply my three conditional formats to the one cell, please?

How to scrape data from font style with IMPORTXML
I want to scrape Quick Ratio data from https://www.gurufocus.com/term/quick_ratio/TSLA/QuickRatio/ with Google Sheets formula.
Required data is located here in font style:
My formula is:
=VALUE(REGEXEXTRACT(IMPORTXML("https://www.gurufocus.com/term/quick_ratio/"& A2& "/QuickRatio","//*[@id='def_body_detail_height']/font[1]"), ": ([\d.]+)"))
A2 = TSLA
But it returns an error.
Thank you in advance!

Create single formula for several different lines (array) (Google Sheets)
In the
AD
column I have this sequence of values:2 3 4
These values refer to rows in a column on another page.
In each line in
AE
column i use this formula:=IF(AD1="","",IFERROR(SUM(FILTER(INDIRECT("'Registro Geral'!O2:O"&AD1)/100,REGEXMATCH(INDIRECT("'Registro Geral'!H2:H"&AD1),SUBSTITUTE(SUBSTITUTE(JOIN("",$V$1:$V$4),"",""),"",""))=TRUE)))) =IF(AD2="","",IFERROR(SUM(FILTER(INDIRECT("'Registro Geral'!O2:O"&AD2)/100,REGEXMATCH(INDIRECT("'Registro Geral'!H2:H"&AD2),SUBSTITUTE(SUBSTITUTE(JOIN("",$V$1:$V$4),"",""),"",""))=TRUE)))) =IF(AD3="","",IFERROR(SUM(FILTER(INDIRECT("'Registro Geral'!O2:O"&AD3)/100,REGEXMATCH(INDIRECT("'Registro Geral'!H2:H"&AD3),SUBSTITUTE(SUBSTITUTE(JOIN("",$V$1:$V$4),"",""),"",""))=TRUE))))
When I try to add
ARRAYFORMULA
so that I don't have to have a formula on each line, leaving only inAE 1
, the values that return on all lines are exactly the same value.Test Formula Fail:
=ARRAYFORMULA(IF(AD1:AD="","",IFERROR(SUM(FILTER(INDIRECT("'Registro Geral'!O2:O"&AD1:AD)/100,REGEXMATCH(INDIRECT("'Registro Geral'!H2:H"&AD1:AD),SUBSTITUTE(SUBSTITUTE(JOIN("",$V$1:$V$4),"",""),"",""))=TRUE)))))
Link to Spreadhseet example:
https://docs.google.com/spreadsheets/d/1qIv6KnLvEwJQXRrk7ucuqYXuJhkIHOCtih9FpAg6U/edit?usp=sharing 
Why is this Countifs function not working?
Can someone please tell me why this formula is not working?
=Sum(Countifs($B$8:$B$26,{"Sgt";"Cpl";"Gnr";"2*"},$D$8:$D$26,72,$D$6,{"MON","TUE","WED","THU","FRI"}))
From all the testing I've done it seems to be the range of cells. I've checked online already and from everything I've read it should work!
Thank you in advance

Spreadsheet  I want to append data from one sheet to another, they only have one column that can be used as a reference
I have two sheets that only have one column in common  ID column.
I'd like to compare those two columns and if id's match, to append data into Sheet 1 from the matching row.
I don't know if I'm clear enough so here is what I'm trying to achieve:
I've tried looking for any solution, but it's a bit too specific.
Hopefully someone here can explain how can I achieve this?