Formula to find first entry of the month

[StuartR]

I have en Excel workbook where I track vehicle fuel consumption and distance travelled.

Column B has the date
Column C has the total distance travelled, in miles, as read from the car mileometer
Column D has amount of fuel purchased in litres
Column E has the cost of fuel purchased.

I put a new line in the worksheet whenever I purchase fuel
I also put a new line in the worksheet on the first day of each month, even if no fuel was purchased, this may only have data in columns B and C, if I didn't purchase any fuel.

I need to know the total distance travelled each month. I have a way to do this, but it involves using an extra column and I would prefer not to.

Currently column A has the following formula (this is from row 171)
=IF(AND(TEXT($B171,"mmm")=TEXT($B170,"mmm"),$B171<>$B170),$A170,$C171)
this means that column A has the mileometer reading from the first of the month, with each reading repeated until a new month starts

Then column I has the formula
=IF(A171=A170,"",$A171-$A170)
this displays the monthly distance travelled on rows that represent the first of each month.

Can anyone suggest a formula to calculate the total monthly distance travelled without using an extra column?

[HansV]

I'll assume that row 1 contains column headings.
In I2, enter the formula

=$C$2

to copy the start value. In I3, enter the formula

=IF(DAY($B3)=1,$C3-MAX($I$2:$I2),"")

Fill down as far as needed.

[HansV]

Another option: enter the following formula in I3:

=IF(DAY(B3)=1,C3-VLOOKUP(EDATE(B3,-1),$B$2:$C$1000,2,FALSE),"")

(change the row number 1000 to the last row used)

Note: in Excel 2003 or before, you need to install the Analysis ToolPak add-in in order to use the EDATE function.

[StuartR]

I guess this is where I get to confess that I left out some details to avoid making this more complex, and this affects the formula in ways that I hadn't realized!

In column H, I manually enter my business miles for the month, and I subtract the value in H from the value in I, so the real formula in column I is currently
=IF(A171=A170,"",$A171-$A170-H171)
This causes problems for your first suggestion

The second one nearly works, but for two issues

• If I have two separate entries for the first of the month (one for fuel and a separate one for the end of month mileage) then it doesn't quite do the right thing. I can fix this by adding a check that B3<>B2 so that it reads...
  =IF(AND(DAY(B3)=1,B3<>B2),C3-VLOOKUP(EDATE(B3,-1),$B$2:$C$1000,2,FALSE),"")
• It reports #VALUE for all rows which are not the first of the month (except for the very first one which has #N/A, breaking down the formula I can see that the VLOOKUP is generating #N/A. I guess I could fix this by checking whether VLOOKUP generates an error but this would make for a very long formula

[HansV]

In that case, I guess it's better to keep the extra column.

[StuartR]

Fair enough, it was not that urgent. I had tried to do it in one column and thought maybe I was missing something simple.