## A list of approximate formulas for prime gaps

A prime gap $$g(n)$$ between two consecutive primes is defined as
$$g(n)=p_{n+1}-p_n$$
where $$p_n$$ is the $$n$$th prime number. An interesting property of prime gaps is that the “spikes” of its plot occurs at multiples of $$6$$. From its graph, it seems very improbable that a continuous function could approximate. Some formulas for prime numbers are known (they are not exact) are known (there is a whole wikipedia article on them) and some approximations for the prime counting function are known, but my question is:

What are some approximations for $$g(n)$$?

An approximation in terms of continuous functions isn’t necessary (such a formula is highly improbable); an approximation in terms of other discontinuous functions might also help.

## Google Sheets: When inserting a row in between previously auto-generated formulas, how to fill certain columns with those formulas?

My Google Sheet has a text header, and then a few columns (let’s say F and I) have a formula in them.
So as an example row 1 is all text, and row 2 would have `=LEFT(H2,4)&" "&MID(H2,5,6)`. This was dragged down (auto filled) below so row 3 has `H3` in the formula etc all the way to say 100.

If I go insert a row at 50, is there a way for it to automatically fill in the formula `H50`. (Currently when I insert row, all the cells are blank).

Two ‘test case’ formulas:

`=LEFT(H2,4)&" "&MID(H2,5,6)&"!"`

`=IF (ISBLANK(H2),"", ARRAY_CONSTRAIN(ARRAYFORMULA(IF( (MOD(SUM(INT(MID(REPT("0",20-LEN(H2))&H2,ROW(\$1:\$31),1)*(MOD(ROW(\$1:\$31),2)+1)/10)+MOD(MID(REPT("0",20-LEN(H2))&H2,ROW(\$1:\$31),1)*(MOD(ROW(\$1:\$31),2)+1),10)),10)=0), "✔", "❌")), 1, 1))`
(This formula is from here – to test out in your own spreadsheet, H simply needs any valid credit card number such as 343280696646912 )

Posted on Categories Articles

## formulas – How do I import data from one Google spreadsheet to another

The issue to your question has nothing to do with the `IMPORTRANGE` function.

You need to enclose the name of the sheet in single quotes, because the name of the sheet itself has a blank space between the words.

As from the official help site on “Get data from other sheets in your spreadsheet“:

If a sheet name contains spaces or other non-alphanumeric symbols, include single quotes around it

In your case use `'Retail Sales'!G9:G39` and surround this with double quotes.

``````=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1SakMhmfUS-N7_zjOHLWL-dS75sxxqFtxn7aGfPsy3IA/edit#gid=190222386","'Retail Sales'!G9:G39")
``````

You can also read more about `IMPORTRANGE`

Posted on Categories Articles

## formulas – How to pull data from one sheet to another

@M. Schultz, if every name in I:I has one and only one phone number in J:J (or never has a phone number in J:J for that name), then you can use this:

``````=SORT(UNIQUE('Employee Data'!I8:J95), 1, TRUE)
``````

However, if you have cases where one person is listed several times with something different in J:J for the phone number, you have to think about which phone number to pair with the name. Otherwise, you’ll still wind up with duplicates.

Let me illustrate.

Suppose you have the following I:J combos:

Sally Smith | 555-555-1212
Sally Smith | 555-444-1313
Sally Smith |

If you request UNIQUE of just I:I, you’ll get “Sally Smith.”

But if you request UNIQUE of I:J, you’ll get all three of those rows returned as they were originally; because each combination is considered unique (i.e., A|1 A|2 A| ).

Often, if you have “Sally Smith” cases like I’ve shown above, and if you have a column with a timestamp of when the entry came in, you can write a formula that uses the date to retrieve the most recent name+phone combination for each person. But in order to help you do that, you’d need to share a link to your sheet (or a copy of your sheet, or a sheet containing an adequate “sanitized” data set from your sheet with everything exactly where it is in your original sheet but with personal data replaced with unique mock data).

If you do choose to share that link, be sure that when you set up the “Share” permission, you choose “Anyone with the link can edit.”

Hope this helps.

## google sheets – Using ARRAYFORMULA extensively in complex formulas

I have a formula which looks like this:

``````=ARRAYFORMULA(IF(A4:A = "", , IF(AND(ISNUMBER(I4), ISNUMBER(J4), ISNUMBER(K4), ISNUMBER(L4)), SUM(I4:L4), "???")))
``````

The idea is that for each row in the sheet, those four cells are added if and only if they are all valid number, otherwise `???` is displayed.

What happens in the above formula is that each row is set to the sum for the 4th row, and only if values in the 4th row are numbers.

If I put a range in `ISNUMBER`, then it checks if ALL of the column is a valid number, and I don’t really know how to define a `SUM` for this case.

How do I make it act as I described? I need the `ARRAYFORMULA` since this is a part of the sheet that takes the responses from another sheet and does calculations on the input. Since each response is added as a new row, all my formulas get shifted (and thus, they omit the new response), unless I use `ARRAYFORMULA`.

Posted on Categories Articles

## riemannian geometry – Identify an ordered-eigenvalue simplex with tetrahedral dihedral angle \$cos ^{-1}left(frac{1}{3}right)\$ in volume, area formulas

Let us order the four eigenvalues ($$lambda_1 geq lambda_2 geq lambda_3 geq lambda_4$$, $$Sigma_{i-1}^4 lambda_i=1$$) of a Hermitian, trace-one, positive-definite $$4 times 4$$ matrix (a “two-qubit density matrix”, in quantum parlance). Those density matrices for which the inequality
$$lambda_1-lambda_3 < 2 sqrt{lambda_2 lambda_4}$$ holds are termed “absolutely separable” (that is, can not be entangled by any global unitary transformation).
In terms of the Hilbert-Schmidt probability distribution $$9081072000 Pi_{j on the simplex of ordered eigenvalues (HilbertSchmidt), the volume/probability of the absolutely separable states has been found to be (Evaluate Explicit)
$$begin{equation} frac{29902415923}{497664}-frac{50274109}{512 sqrt{2}}-frac{3072529845 pi }{32768 sqrt{2}}+frac{1024176615 cos ^{-1}left(frac{1}{3}right)}{4096 sqrt{2}} approx 0.00365826 end{equation}$$
while the area/probability of the boundary absolutely separable states (the locus of $$lambda_1-lambda_3 =2 sqrt{lambda_2 lambda_4}$$ ) has been further shown to be (Confirm)
$$begin{equation} -frac{837276448115}{663552}+frac{86031670725 pi }{32768 sqrt{2}}-frac{86031670725 cos ^{-1}left(frac{1}{3}right)}{16384 sqrt{2}} approx 0.15339, end{equation}$$
where, notably, $$cos ^{-1}left(frac{1}{3}right)$$ is the dihedral angle of the regular tetrahedron.

Might these formulas provide any possible insight into the underlying geometry of the two-qubit absolutely separable states?

Posted on Categories Articles

## formulas – If colE contains value then import colAY from same sheet

I am trying to import 2 columns from one sheet into another but trying to make sure I import only the cells from col2 when col1 has a value (all different values).

I imported my first column needed (column E) with the following formula `=QUERY(IMPORTRANGE("LINK", "ALL Product Overview!E3:E"), "select Col1 where Col1 is not Null", 0)`

Now I am trying to import my second col (column AY) but only the cells in Column AY when the same cell number in column E has value.

## Establish the equivalence of two inverse trigonometric function based formulas

In a comment to

3D5D

user JimB provided an answer to the question posed there of finding the "two-quater(nionic)bit" absolute separability Hilbert-Schmidt probability. (Previously, in TwoQubit,
he had obtained the "two-qubit" counterpart.)

The answer now took the form

``````327574875999612773528659/95105071448064 - 2951081236201839/(524288 Sqrt(2)) - (15390446918294583135 (Pi))/(17179869184 Sqrt(2)) + (15390446918294583135 ArcCsc(18/Sqrt(50 + 17 Sqrt(2))))/(2147483648 Sqrt(2))
``````

``````-((13 (-216449750678398795533760757497856 +
176860737736399592490919645937664 Sqrt(2) +
279292548969739228073088142369304501839785 Sqrt(2) (Pi) -
558572941247617043110461841280869072896000 Sqrt(2)
ArcCot(Sqrt(2)) +
23637916932187025487103667523337320 Sqrt(2)
ArcCot(2 Sqrt(2)) -
16178155879591789043088455851252390200 Sqrt(2)
ArcCot(3 + Sqrt(2)) -
558589165778586158484606527963549721006600 Sqrt(2)
ArcTan(Sqrt(2))))/816946343106356485029888)
``````

of somewhat different form, to the very same question had been provided in eq. (36) in

2009paper

Both formulas above evaluate (N(,50)) to

``````0.000039870347068019928855365404975780992652117606213067
``````

However, the FullSimplify command does not reveal the formulas’ evident equivalence.

It might seem that some inverse trigonometric function transformations would be required to accomplish that.

So, can the evident equivalence of the two formulas be formally established employing Mathematica?

Posted on Categories Articles

## formulas – How to show Day of Week in Order by Day of Week in Google Sheets

I have the following formula that displays a total by Day of Week (weekday) from a date in `J` and amount in `K`

Example
`J` contains 1/1/2020 … until 9/29/2020
`K` contains numeric value 123 … 1999

``````=Query(ArrayFormula({{unique(text(J2:J,"DDDD"))},
{sumif(text(J2:J,"DDDD"),
unique(text(J2:J,"DDDD")),K2:K)}}),"Select * where Col2<0")
``````

All the values are correct, they just are not in the order of days, ie
Sunday Monday Tuesday Wednesday … Saturday

Instead, I have the data showing like this

``````Friday    1,220.00
Monday    1,234.00
Tuesday   1,999.00
Thursday  1,555.00
Saturday  1,666.00
Wednesday 1,777.00
``````

What I would want is

``````Monday    1,234.00
Tuesday   1,999.00
Wednesday 1,777.00
Thursday  1,555.00
Friday    1,220.00
Saturday  1,666.00
``````

## logic – Which of these formulas are sentences?

Let V be the vocabulary {+,<,1,2,3} where + is an arbitrary function, < is a binary relation, and 1,2, and 3 are constants. We write (x+y) for +(x,y) and x<y for <(x,y). Consider the following V-formulas:

1. ∀x∃y((x+y)=1)
2. ∀x¬(x<1)
3. ((1+1)=2)
4. 2<1
5. ∀x(2<1) →(x+2<x+1)
6. ∀x∀y∃z(x+y=z)
7. ∀x∀y∀z(((x+3=y)∧(x+3=z))->(y=z))
8. ∀x∀y∀z(((x+y=3)∧(x+z=3))->(y=z))
9. ∀x∀y(((x+3)<(y+3))->(x<y))
10. ∀x∀y((x<2)->((x+3)=4))

Question: Which of these 10 formulas are sentences?
I have the following definitions from the book:
Definition. A vocabulary is a set of function, relation and constant symbols.
Definition. A sentence of first-order logic is a formula having no free variables.
In contrast to the free variables of a formula p, the bound variables of p are those that have quantifiers, (∃,∀).
I tried to interpret this information and it seems that the formulas 1-10 have no free variables and each of them is therefore a sentence. Is this right or wrong?