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

csliger931

New Member
Joined
Jul 22, 2020
Messages
8
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
 

Attachments

  • MR EXCEL FORUM_112020.JPG
    MR EXCEL FORUM_112020.JPG
    181.5 KB · Views: 8

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,240
Office Version
  1. 365
Platform
  1. Windows
How about in N2 copies down
Excel Formula:
=IF(N2="Pump on",B2-LOOKUP("pump off",N$2:N2,B$2:B2),"")
 

csliger931

New Member
Joined
Jul 22, 2020
Messages
8
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
How about in N2 copies down
Excel Formula:
=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.
 

Attachments

  • MR EXCEL FORUM_112020.JPG
    MR EXCEL FORUM_112020.JPG
    159.5 KB · Views: 2

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,240
Office Version
  1. 365
Platform
  1. Windows
Works for me
+Fluff v2.xlsm
BNOPQ
1
23pump off 
39 
44 
54 
68 
71 
84pump on1
91 
104 
114pump off 
123 
131 
144 
158 
168 
172 
189pump on5
193 
206 
217 
Main
Cell Formulas
RangeFormula
Q2:Q21Q2=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

New Member
Joined
Jul 22, 2020
Messages
8
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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?
 

Attachments

  • Capture.JPG
    Capture.JPG
    215.7 KB · Views: 2

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,240
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jul 22, 2020
Messages
8
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.
 

Attachments

  • Capture.JPG
    Capture.JPG
    204.9 KB · Views: 2

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,240
Office Version
  1. 365
Platform
  1. Windows

Watch MrExcel Video

Forum statistics

Threads
1,123,206
Messages
5,600,317
Members
414,376
Latest member
NickYOW

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top