Finding two values in a column and calculating the difference between adjacent cells
I've two columns as follows.
A B C
1 1/9/2016 12:57:48 PM Text A
2 1/9/2016 1:04:48 PM Text A
3 1/9/2016 1:05:04 PM Text A
4 1/9/2016 1:05:14 PM Text A
5 1/9/2016 1:05:15 PM Text B A5 – A1
6 1/9/2016 1:05:38 PM Text A
7 1/11/2016 9:57:12 AM Text A
8 1/15/2016 10:42:55 AM Text A
9 1/15/2016 10:45:21 AM Text B A9 – A6
10 1/15/2016 10:58:12 AM Text A
Query: I need the difference of Cell: A5 and Cell: A1 in cell C5, A9 - A6 in C9, etc. So basically, we have to look at column B to change the text value and subtract the corresponding values in column A. Hopefully my explanation of the problem is clear. If not, let me know. I can help to understand better if possible.
source to share
Here are two small changes to the C2 formula in @ Agata Powalka's answer, which is good:
=IF(B2 = "Text B", IF(B1 = "Text B", A2, A2-D2), "")
This handles the case where a row Text B
occurs immediately below another, where it will display whatever is in the column A
. I also changed A2-D1
to A2-D2
just for clarity; it doesn't change the results.
source to share
In Google Sheets, this is a little tricky, but can only be done with formulas. I am assuming there is no title and text to search for 'B'
, but rather A:A
is date and B:B
is text. Add a column D
and set D1
below:
=index(
$A:$A,
index(
transpose(split("1 "&join(" ", arrayformula(if($B:$B="B", row($B:$B), ""))), " ")),
countif($B$1:$B1, "B"),
1
)
)
The above formula will result in the latest date that has text 'B'
. Now copy D1
and drag down. Now the result column C
is trivial. Enter below C1
and drag down.
=if(B1="B", A1-D1, "")
An example of a file is here , and below is a screenshot of it. I set the column format C
to duration
and highlighted some dates in the column A
.
If you prefer a custom function or run a script, the tenth line of GAS can do the job as well. I personally prefer formulas, even if they are disgusting.
source to share