How to find _any_ formula error in a Google Sheet via a standard command (and not a script)?

For complex Google Sheets with many tabs (my teams effectively employ Google Sheets as a sort of database in these scenarios), is there a relatively-easy way to search for any and all formula errors (across all tabs) without having to create a script?

I’m thinking something like a Find and replace option that offers a Find formula errors checkbox.

The purpose: when adjusting formulas, named ranges, and the like: it’s quite cumbersome for a spreadsheet designer/maintainer to manually look through all formulas in all tabs to find if anything is broken. We’d rather employ some automated-find-any-errors mechanism to avoid such efforts.

simplifying expressions – Trigonometric Simplification and Double Angle Formula

I am trying to achieve the formula hbar / 2 * cos(wt) * sin(theta). However, my Mathematica expression doesn’t seem to simplify the half/double angle formulas. I am wondering how I could refine my approach. Here’s my code:

Sz(J_) := (HBar)*
  Table( KroneckerDelta(i, j)*(J + 1 - i), {i, 2*J + 1}, {j, 2*J + 1})
Sx(J_) := Chop((HBar)*.5*
   Table((KroneckerDelta(i, j + 1) + 
       KroneckerDelta(i + 1, j))*((J + 1)*(i + j - 1) - i*j)^.5, {i, 
     2*J + 1}, {j, 2*J + 1}))
Sy(J_) := (HBar)*
  Table(I/2*(KroneckerDelta(i, j + 1) - 
      KroneckerDelta(i + 1, j))*((J + 1)*(i + j - 1) - i*j)^.5, {i, 
    2*J + 1}, {j, 2*J + 1})
Sn(J_, theta_, phi_) := 
 Simplify(Sx(J)*Sin(theta)*Cos(phi) + Sy(J)*Sin(theta)*Sin(phi) + 
   Sz(J)*Cos(theta), 
  Assumptions -> {0 <= theta <= Pi, 0 <= phi < 2*Pi, 
    Element(theta, Reals)})
U(H_, t_) := 
 Simplify(Chop(ComplexExpand(MatrixExp(-I*H*t/(HBar)))), 
  Assumptions -> Element(t, Reals))

{nvals, nvecs} = Eigensystem(Sn(.5, theta, 0));
psi = Simplify(U(w Sz(.5), t).nvecs((2)), 
   Assumptions -> Element(w, Reals));
Simplify(ComplexExpand(Conjugate(psi).Sx(.5).psi))

And here’s my output:

((HBar) Sin(
  theta) ((-2. - 2. Cos(theta)) Cos((0.5 + 0. I) t w)^2 + (2. + 
      2. Cos(theta)) Sin((0.5 + 0. I) t w)^2))/(-1. + Cos(2 theta))

What mathematical formula does audio downmix?

What mathematical formula do video editors use when they create a final mix of audio tracks?

Suppose I have a mono track pcm_f32le and 2 stereo tracks pcm_f64le. How do they calculate the waveform of the output file? I’d like to do the same in numpy or Mathematica.

enter image description here

google sheets – Auto hide row based on cell text (formula based)

My plan is to hide an entire row if a specific cell in the row showed specific value. I already found the script and the script work amazing. But the problem now, since the script use onEdit function, it only worked when I input the cell manually. Since we handle a large amount of data, this manual input is not reliable and impossible to manage.

So, do google script have a function where a script can be triggered by formula based value? I already try onOpen, onChange, myFunction but all failed. Can anyone please help me?

Here is the script that I used for my sheet.

var SHEET = "Allocation";
var VALUE = "0";
var COLUMN_NUMBER = 1

function onEdit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var activeSheet = ss.getActiveSheet();

  if(SHEET == activeSheet.getName()){
    var cell = ss.getActiveCell()
    var cellValue = cell.getValue();

    if(cell.getColumn() == COLUMN_NUMBER){
      //If the cell matched the value we require,hide the row. 
      if(cellValue == VALUE){
        activeSheet.hideRow(cell);
      };
    };
  };
}

As for now, we are using the filter function. It works just fine, but its good to have this script to work.

google sheets – How to get the previous edit of a cell in another cell using formula or script

I’m trying to figure out a way to bring the previous edit of a particular cell in another cell. I cannot use version history because every cell may e edited at a different time in different versions. I cannot take all version create subsheet and do a vlookup, So is there any way to run script or update a formula for the same?

google sheets – Locking a formula when adding rows

Currently I have this formula in there

=IF('Form Responses 3'!G$20="","",COUNTIF('Form Responses 3'!G$1:G$167,F20))

It does what I want it to do if I go in a tweak it later, but I don’t want to go in a tweak it

Because it’s a form responses tab, every time a new form is submitted it adds a new row in and my reference cell changes. I’ve done some research and tried out the INDIRECT function (=IF(INDIRECT('Form Responses 3'!G$20)="","",COUNTIF('Form Responses 3'G$1:G$167,F20)), but I can’t seem to get the reference cell to stay G20.

My formula is on a different tab in cell C4.

ag.algebraic geometry – $L^r_M = i_* circ hat{L}^{r-1}_M circ i^*$ by the projection formula and the Poincare duality

This is a question arising when I am reading

M. A. A. de Cataldo, L. Migliorini – The Hard Lefschetz Theorem and the topology of semismall maps, Ann. sci. École Norm. Sup., Serie 4 35 (2002) 759-772.

I give a reduction of the semantics. Let $M$ be a line bundle (in fact it is a lef line bundle), $Y$ is the zero locus of a non zero section of $M$. Assume the Chern class of $M_{vert Y}$ satisfies the condition of the Hard Lefschetz. What we want to prove is that
$$L^r_M = i_* circ hat{L}^{r-1}_M circ i^*,$$
where $L$ means wedge product with a Chern class and $hat{L}_M = L_{M_{vert Y}}$.

I could give a proof my self. Let $Y = {f_1 = 0}$ and $f_2$ is another section of $M$ traversing $Y$. Note that $c_M = (sqrt{-1}partial bar{partial} f_1 ) = (sqrt{-1}partial bar{partial} f_2 )$ and $c_{M_{vert Y}} = (sqrt{-1}partial bar{partial} f_2circ i )= i^*(sqrt{-1}partial bar{partial} f_2 ) = i*c_M$. So
$$i_* circ hat{L}^{r-1}_M circ i^* omega = i_* i^*({c_M^{r-1} wedge omega}).$$
It remains to show that $i_*i^* = L_M$. This can be seen by $<i_*i^* alpha , beta >_X = <i^* alpha , i^* beta>_Y = <i^* (alpha wedge bar{ beta}) , 1>_Y = <alpha wedge bar{ beta}, c_M>_X = <alpha, beta wedge c_M>_X =<alpha wedge c_M, beta>_X$. So $i_*i^* = L_M$.

However, in the article it is said that this is proved by the projection formula and the Poincaré duality. I don’t understand how it works and some explanation could help a lot, not only on this question but also the understanding of the cohomology theory. It’s more appreciated if you can work on derived categories.

google sheets – Formula to output the date for next Friday once it has passed 20:00 on Friday

Alsors, try this:

=ArrayFormula(IF((WEEKDAY(TODAY())=6)*(NOW()-TODAY()>=TIMEVALUE("8:00 PM")),TODAY()+7,FILTER(SEQUENCE(7,1,TODAY()),WEEKDAY(SEQUENCE(7,1,TODAY()))=6)))

HOW IT WORKS

The formula assumes Sunday is counted as weekday 1 and, therefore, that Friday is counted as 6.

The first part of the IF checks to see if two conditions are true: That today is Friday and that the current time is greater than or equal to 8:00 PM. If those are both TRUE, then the date is set to seven days in the future (i.e., next Friday).

If this small window of time is not the case, then the second half of the IF statement takes effect. A SEQUENCE is formed of seven dates starting with today’s date. FILTER filters in only those days whose weekday is 6 (i.e., Friday). And since there will only be one Friday within any seven-day SEQUENCE of dates, it will pull whatever the “next Friday” is.

(Note: If today is Friday before 8:00 PM, then the “next Friday” in the SEQUENCE is today, so it stays showing today’s date until after 8:00 PM.)

microsoft excel – Making formula work only when adding to a value

I am trying to have a number automatically update itself on our inventory spreadsheet (since we seem to forget to do so ourselves)…but I can’t seem to get it quite right.

So our inventory process has three steps:

  1. Recieve raw product (+B17)
  2. Send raw product to sterilization (-B17,+B10)
  3. Sell sterile product (-B10)

It took me FOREVER to find this formula for B17…

=0-B10+0

and now when I add to B10 it automatically subtracts from B17 and there’s FINALLY no Circular Reference, but now how do I keep B17 from changing when I eventually have to sell the sterile product and subtract from B10?

I also need to know how to go about adding to B17 when we eventually order more raw product again.

Spreadsheet Example

Google Sheets: Need conditional format formula to parse number-hyphen-number input into “2-Tuesday” type

One solution I have come up with does not use conditional formatting. Instead, it uses a column of hyphenated, single-digit numbers to fill another column.

Test case

Cell E5: 1-3

Cell F5: =CONCATENATE(LEFT($E5,2),CHOOSE(RIGHT($E5,1),"MONDAY","TUESDAY","WEDNESDAY","THURSDAY","FRIDAY","SATURDAY","SUNDAY"))

RESULT: 1-WEDNESDAY

I have verified this will work for any digit 1…7 on the right-hand-side.
This indexing system was nudged from 0…6 to 1…7 because the CHOOSE function begins with 1, not 0.

A conditional format formula would still be preferable to this because then we would not need the extra column to do the conversion.