Google Sheets - Grabbing first two values from array with N/A's and subtract second value from-CodePudding

I have a sheet that has values of averages over the last 5 days and I need to subtract the 2nd value from the first value when a value is present. The rows look like this:

14.7    #N/A    15.3    #N/A    #N/A
#N/A    0.2     #N/A    0.3     #N/A
5.7     5.8     #N/A    5.7     5.5
4.4     4.4     #N/A    #N/A    #N/A
7.7     #N/A    7       #N/A    7.7
#N/A    #N/A    #N/A    #N/A    2.2
#N/A    #N/A    #N/A    #N/A    0
3       #N/A    2.4     #N/A    1.9
#N/A    #N/A    #N/A    #N/A    2
9.6      7.8    #N/A    7.5     #N/A
10.3    #N/A    10.3    #N/A    9.2
#N/A    #N/A    #N/A    #N/A    2.8
9.1     #N/A    8.7     #N/A    8.7
#N/A    #N/A    #N/A    #N/A    9.1
#N/A    #N/A    #N/A    #N/A    6.4
#N/A    #N/A    #N/A    6.1     2.3
#N/A    #N/A    #N/A    #N/A    0.5

The number can be negative, It just needs to subtract the second value it finds from the first value it finds.

For example row 1 would subtract column 3 from column 1.

Or row 2 would subtract column 4 from column 2

This formula grabs the most recent if present:

    =IFS((QUERY(sheetwithvalues!A2:B, "Select B where A contains " & """" & A2       & """" & 
""))>0,(QUERY(sheetwithvalues!A2:B, "Select B where A contains " & """" & A2       & """" & 
"")),(QUERY(sheetwithvalues!A2:C, "Select C where A contains " & """" & A2       & """" & 
""))>0,(QUERY(sheetwithvalues!A2:C, "Select C where A contains " & """" & A2       & """" & 
"")),(QUERY(sheetwithvalues!A2:D, "Select D where A contains " & """" & A2       & """" & 
""))>0,(QUERY(sheetwithvalues!A2:D, "Select D where A contains " & """" & A2       & """" & 
"")),(QUERY(sheetwithvalues!A2:E, "Select E where A contains " & """" & A2       & """" & 
""))>0,(QUERY(sheetwithvalues!A2:E, "Select E where A contains " & """" & A2       & """" & 
"")),(QUERY(sheetwithvalues!A2:F, "Select F where A contains " & """" & A2       & """" & 
""))>0,(QUERY(sheetwithvalues!A2:F, "Select F where A contains " & """" & A2       & """" & 
"")))

But I have no idea how to use something like this to grab the second value it finds and subtract it from the first. Is this possible in google sheets?

I am also open to a google script that runs a sort of drop N/A on a copied sheet if that works. I just cannot seem to think far enough outside of the box to come up with even an idea yet.

If anyone even has an idea I can try to make it work. Thanks in advance.

CodePudding user response：

I believe your goal is as follows.

The number can be negative, It just needs to subtract the second value it finds from the first value it finds. For example row 1 would subtract column 3 from column 1.

Or row 2 would subtract column 4 from column 2

About rows 3 and 6,

row three would subtract column 2 from col 1

row 6 would be N/A if possible.

And, you want to achieve this using Google Apps Script.

In the above logic, how about the following sample script?

Sample script:

Please copy and paste the following script to the script editor of Spreadsheet and save the script.

function SAMPLE(values) {
  const c = 10000; // This is for subtraction.
  return values.map(r => [r.reduce((o, e) => {
      if (e != "#N/A" && o.res == "#N/A") {
        if (o.temp == 0) {
          o.temp = e;
        } else {
          o.res = (o.temp * c - e * c) / c;
        }
      }
      return o;
    }, { res: "#N/A", temp: 0 }).res
  ]);
}

When you use this script, please put a custom function of =SAMPLE(A1:E17) to a cell as the following sample result.

References:

EDIT: I noticed the above formula will return #N/A also when the difference is 0. Try the following instead:

=arrayformula(if(len(substitute(
transpose(query(transpose(if(isna(A1:E17),,"z")),,9^9))," ",))=1,na(),
query(iferror(1/split(
transpose(query(transpose(ifna(A1:E17)),,9^9))," ")^-1,na()),
"select Col1-Col2 label Col1-Col2 ''")))