Miles driven since last entry . . .

[oomfh]

Have 31 columns (for 31 days.)

Vehicle mileages are entered randomly through-out the month.

If mileage is entered 2 consecutive days in a row then =IF(R24="","", +R24-Q24) in cell R25 will work.

If mileage isn't entered for, say 2 weeks, I believe I will need a long series of Elseif statements (nested?) to go backwards until Excel finds the next mileage entry (of false statement?)

Eek

 

[jasonb75]

Say for example your entry for the 1st of the month is in B24,

In B25 and drag / fill to the right as needed.

=IF(B24="","",Max($B24:B24)-C24)

This formula is based on the assumption that row 24 contains odometer readings, so a new entry will never be less than the previous.

 

[oomfh]

The IF portion of the forumula works.

To simplify things, assume the odometer readings are entered randomly in columns D-AH, row 24.

The formula that is entered in row 25 =IF(R24="","",MAX($R24:R24)-Q24) only calculates correctly when odometer readings are entered into two consecutuve columns. If the next odometer reading is entered in say, column Y, instead of subtracting R24 from Y24, it only enters the number that is entered into Y24.

I tried =IF(Y24="","",MAX($D24:AH24)-X24) but receive the same results.

 

[jasonb75]

In D25

=IF(D24="","",Max($D24:D24)-E24)

Then use autofill to drag it to the other cells so that in E25 it reads =IF(E24="","",Max($D24:E24)-F24), along to AG25 =IF(AG24="","",Max($D24:AG24)-AH24)

That should work.

 

[oomfh]

I am understanding this a little better . . .

I modifed your formula to subtract the previous column from the current column, =IF(T24="","",MAX($D24:T24)-S24)

Bottom line, the formula is always subtracting the previous column from the current column - even when the previous column is blank.

I need the formula to keep looking back at previous columns until it finds the column that last had a number entered into it - then subtract that number from the current number in the current column . . .

So if H24 was the last column that had a number entered into it - then H24 would be subtracted from S24.

 

[Aladin Akyurek]

I am understanding this a little better . . .

I modifed your formula to subtract the previous column from the current column, =IF(T24="","",MAX($D24:T24)-S24)

Bottom line, the formula is always subtracting the previous column from the current column - even when the previous column is blank.

I need the formula to keep looking back at previous columns until it finds the column that last had a number entered into it - then subtract that number from the current number in the current column . . .

So if H24 was the last column that had a number entered into it - then H24 would be subtracted from S24.

Is

=S24-LOOKUP(9.99999999999999E+307,$D24:T24)

what you want to implement?

 

[jasonb75]

My mistake, Doh!

I read your original formula backwards

It should be

=IF(T24="","",T24-MAX($D24:S24))

 

[T. Valko]

I am understanding this a little better . . .

I modifed your formula to subtract the previous column from the current column, =IF(T24="","",MAX($D24:T24)-S24)

Bottom line, the formula is always subtracting the previous column from the current column - even when the previous column is blank.

I need the formula to keep looking back at previous columns until it finds the column that last had a number entered into it - then subtract that number from the current number in the current column . . .

So if H24 was the last column that had a number entered into it - then H24 would be subtracted from S24.

Maybe this...

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Verdana,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=0><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 33px"><COL style="WIDTH: 33px"><COL style="WIDTH: 33px"><COL style="WIDTH: 33px"><COL style="WIDTH: 33px"><COL style="WIDTH: 33px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">24</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">100</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">122</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">131</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">144</TD></TR></TBODY></TABLE>


Assuming there are at least 2 entries...

=LOOKUP(1E100,24:24)-LARGE(24:24,2)

That formula returns 13. 144 minus 131 = 13

 

[oomfh]

Apparently I am not explaining the application clearly

The last 3 suggestions seem to calculate the largest difference between the latest number entered and any previously entered number.

I have 31 columns (D-AH) where I will enter odometer readings in row 24 at irregular intervals.

Using T.Valko's example, I25 would give 13, H25 would give "", G25 would give 9, F25 would give 22, E25 would give "".


If this doesn't help I will try to figure out how to post my spreadsheet.

 

[jasonb75]

Which is what my suggestion in #7 does, that was the formula for column T, so you enter that in T25 then fill it left / right as needed and excel will adjust the columns correctly for you.

Using T.Valko's example

<TABLE style="WIDTH: 336pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=448><COLGROUP><COL style="WIDTH: 48pt" span=7 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #cacaca; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=64>24

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64>100</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64>122</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64>131</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64>144</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #cacaca; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=64>25</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 align=right>22</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 align=right>9</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 align=right>13</TD></TR></TBODY></TABLE>