Find Previous Match

dallen8028

New Member
Joined
Jan 28, 2013
Messages
30
Attempting to calculate Mileage difference ("D") by finding the previous "Fuel" event amongst a variety of events listed ("A") in order to fill in the missing data in Column "B" for the purpose of calculating MPG ("F")

A, B, C, D, E, F,
Event, Start, Stop, Miles, Gals, MPG,
Rvcd Vehicle, 51329.0, 51329.0, , , ,
Fuel, 51435.0, 51435.0, , 15.8, ,
Maint, 51435.0, 51465.0, 30.0, , ,
Field Trip, 51465.0, 51644.0, 179.0, , ,
Fuel, ______ , 51645.0, _____, 12.6, ,
Bus Route, 51645.0, 51665.0, 20.0, , ,
Bus Route, 51665.0, 51685.0, 20.0, , ,
field Trip, 51685.0, 52006.0, 321.0, , ,
Fuel, _______, 52007.0, _____, , ,

I hope you can read this. Thanks in advance
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,260
Office Version
2007
Platform
Windows
You need this?

<table style="font-family:Arial; font-size:12pt; border-style: groove ;border-color:#0000FF;background-color:#fffcf9; color:#000000; "><tr><td ><b>sheet</b></td></tr></table><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:81.74px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Event</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; "> Start</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; "> Stop</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; "> Miles</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; "> Gals</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; "> MPG</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >Rvcd Vehicle</td><td style="text-align:right; ">51329</td><td style="text-align:right; ">51329</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Fuel</td><td style="text-align:right; ">51435</td><td style="text-align:right; ">51435</td><td > </td><td style="text-align:right; ">15.8</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >Maint</td><td style="text-align:right; ">51435</td><td style="text-align:right; ">51465</td><td style="text-align:right; ">30</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >Field Trip</td><td style="text-align:right; ">51465</td><td style="text-align:right; ">51644</td><td style="text-align:right; ">179</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >Fuel</td><td style="background-color:#92d050; text-align:right; ">51435</td><td style="text-align:right; ">51645</td><td style="background-color:#92d050; text-align:right; ">210</td><td style="text-align:right; ">12.6</td><td style="background-color:#92d050; text-align:right; ">13.2911392</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >Bus Route</td><td style="text-align:right; ">51645</td><td style="text-align:right; ">51665</td><td style="text-align:right; ">20</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >Bus Route</td><td style="text-align:right; ">51665</td><td style="text-align:right; ">51685</td><td style="text-align:right; ">20</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td >field Trip</td><td style="text-align:right; ">51685</td><td style="text-align:right; ">52006</td><td style="text-align:right; ">321</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td >Fuel</td><td style="background-color:#92d050; text-align:right; ">51645</td><td style="text-align:right; ">52007</td><td style="background-color:#92d050; text-align:right; ">362</td><td > </td><td style="background-color:#92d050; text-align:right; ">28.7301587</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B6</td><td >=INDEX($C$1:$C$100,SUMPRODUCT(MAX((INDIRECT("$A$2:A" & ROW() -1)="Fuel")*ROW((INDIRECT("$A$2:A" & ROW() -1))))))</td></tr><tr><td >D6</td><td >=C6-B6</td></tr><tr><td >F6</td><td >=D6/INDEX($E$1:$E$100,SUMPRODUCT(MAX((INDIRECT("$A$2:A" & ROW() -1)="Fuel")*ROW((INDIRECT("$A$2:A" & ROW() -1))))))</td></tr></table></td></tr></table> <br /><br />
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,090,370
Messages
5,414,058
Members
403,513
Latest member
Vivek pare

This Week's Hot Topics

Top