Find previous value = "PUMP ON" and subtract from current row

[csliger931]

Hi,

I have what is probably an easy Excel question that is giving me some difficulty. I have a spreadsheet of Date/Time in Column A, Tank levels in column B, and in some of the rows I have an identifier PUMP OFF in Column N, and in some rows I have an identifier PUMP ON in Column N (Pump On/Off was based on whether I had continuous positive or negative numbers in Column B...that part is unimportant). In all rows where Column N = PUMP ON, I want to subtract the Tank Level in Cell B from that row from the Tank level in the most recent row where I have Column N = PUMP OFF. For example, Lets say in Row 55 I have Cell B = 2.18 and Cell N = PUMP OFF. Then in Rows 56-65, I have various values in Cell B and Cell N is blank. Then, in Row 66, I have a value in Cell B = 2.479, and Cell N = PUMP ON. I want Cell O66 to subtract 2.479-2.18 = 0.299. And I want Column O to be automated where I don't have to type "=BXY-BXX" at each instance of Pump On. I want Excel to find the PUMP ON for me, and then find the previous Tank Level where PUMP OFF. As I get further down the spreadsheet, I will have more instances of "PUMP ON" and "PUMP OFF", but it is never a fixed amount of rows between each PUMP ON and PUMP OFF, so I can't just type in cell O130 "=B130-B120" and then drag down....it may be that the next instance is =B142-B137, or =B146-B137, etc. I need Excel to recognize when the next immediate instance of PUMP ON occurs, and then to subtract the Tank Level in the most recent row where PUMP OFF from the Tank Level where PUMP ON.
Any ideas? Even if you just tell me the formula or operator to Google, that would give me a headstart. I can't download XL2BB to my computer at work, so I've attached my spreadsheet as an image.

Thanks!
Chris

 

[Fluff]

How about in N2 copies down

=IF(N2="Pump on",B2-LOOKUP("pump off",N$2:N2,B$2:B2),"")

 

[csliger931]

How about in N2 copies down

=IF(N2="Pump on",B2-LOOKUP("pump off",N$2:N2,B$2:B2),"")

That is close, but the second half (LOOKUP("pump off",N$2:N2,B$2:B2),"") is wanting to pull only from the first instance where I have PUMP OFF. For example, if I drag that down to row 200, and in row 200 I have Pump On, its pulling the correct number for pump on, but then its subtracting the very first instance in my spreadsheet where I have PUMP OFF. I want it to subtract the most recent instance of PUMP OFF, not the first instance. So the problem is somewhere in the "N$2:N2"...the "N$2" would need to start in the next row after I have calculated a PUMP ON (or something to keep it from pulling only the first instance of PUMP OFF). See image attached.

 

[Fluff]

Works for me

+Fluff v2.xlsm
	B												N	O	P	Q
1
2	3												pump off
3	9
4	4
5	4
6	8
7	1
8	4												pump on			1
9	1
10	4
11	4												pump off
12	3
13	1
14	4
15	8
16	8
17	2
18	9												pump on			5
19	3
20	6
21	7
Main

Cell Formulas
Range	Formula
Q2:Q21	Q2	=IF(N2="Pump on",B2-LOOKUP("pump off",N$2:N2,B$2:B2),"")

Check that your pump off cells don't have any extra spaces.

 

[csliger931]

So I was able to duplicate your formula/results when I manually typed in "PUMP ON" AND "PUMP OFF" in Column N. The formula worked fine. But, in my existing data, the "PUMP ON" and "PUMP OFF LABELS" are generated through IF statements [i.e. N2=if(B2<2.2, "PUMP ON","")] and CONCATENATE statements. For some reason, when I use your formula for that situation (where PUMP ON and PUMP OFF are generated through IF statements), the formula isn't recognizing PUMP OFF. Instead, It is pulling from the cell directly above it. I've attached an image showing what is happening when using IF statements and CONCATENATE statements to generate PUMP ON and PUMP OFF. The result that it is predicting (I.e. the formula you suggested) is in Column K, while the PUMP ON and PUMP OFF labels are in Column I. As you can see in Cell K87, the formula is subtracting 2.479-2.466 - 0.013. It should be subtracting 2.479-2.18=0.299. Any ideas?

 

[Fluff]

In your original example you were wanting to look above the pump on to find the last pump off, in your new example there is no pump off above, hence you are getting the wrong result.

 

[csliger931]

In your original example you were wanting to look above the pump on to find the last pump off, in your new example there is no pump off above, hence you are getting the wrong result.

I must have attached the wrong image. Correct image is attached. As you can see in J61, the formula is subtracting 2.479-2.466 = 0.013, which is wrong. I don't know why it is pulling 2.466 (that is the number directly above the "PUMP ON" value) instead of pulling 2.18 (which is the value corresponding to the most recent PUMP OFF row. The correct answer should 2.479-2.18=0.299. And since I will have multiple "PUMP ON" and "PUMP OFF" as I go through the spreadsheet, I need it to be automated to where it chooses the most recent PUMP OFF value. I appreciate your help on this by the way.

 

[Fluff]

The fact you have a formula in there shouldn't make a difference.

+Fluff v2.xlsm
	B	C							J	K
1
2	3	1
3	9	2
4	4	3
5	1	4							Pump Off
6	8	5
7	1	6
8	4	7
9	10	8							Pump on	9
10	4	9
11	4	10
12	3	11
13	1	12							Pump Off
14	4	13
15	8	14
16	8	15
17	7	16							Pump on	6
18	9	17
19	3	18
20	6	19
21	7	20							Pump Off
Main

Cell Formulas
Range	Formula
J2:J21	J2	=IF(MOD(C2,8)=0,"Pump on",IF(MOD(C2,4)=0,"Pump Off",""))
K2:K21	K2	=IF(J2="Pump on",B2-LOOKUP("pump off",J$2:J2,B$2:B2),"")