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

Question

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.

Answer 1

Here's an alternative approach without scripts:

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

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 ''")))