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
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,810
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:

Forum statistics

Threads
1,077,991
Messages
5,337,591
Members
399,156
Latest member
RaudMees

Some videos you may like

This Week's Hot Topics

Top