• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

how to create formula for automatic subtraction

legolas

New Member
I'm trying to prepare vehicle mileage report based on vehicle number (column B) and date (A) , pls see attached
is it possible to have in E a formula to do the subtraction of last and first mileage in C based on same month in A ?

i just need to calculate how many kms a vehicle travelled each month

i hope it is clear enough and thanks for your help
 

Attachments

Peter Bartholomew

Well-Known Member
This is a 365 solution.
Code:
= LET(
    startMonth, 1+EOMONTH(+date,-1),
    distinct,   UNIQUE(HSTACK(startMonth,vehicle)),
    start,      CHOOSECOLS(distinct, 1),
    end,        EOMONTH(start, 0),
    plate,      CHOOSECOLS(distinct, 2),
    max, MAXIFS(mileage, vehicle, plate, date, "<="&end),
    min, MINIFS(mileage, vehicle, plate, date, ">="&start),
    HSTACK(TEXT(start,"mmm"), plate, max-min, max)
  )
81109
 

Attachments

Peter Bartholomew

Well-Known Member
My code was a worksheet formula, not VBA. It is functionality that has been introduced to Excel over the past 4 years and is only available in Excel 365. HSTACK is only just coming out of beta-testing but the results could be returned column by column without it. I use defined names for all references, so 'date' and 'vehicle' are created using Name Manager. If the sheet I attached did not work, you most likely have a legacy version of Excel.
 
Top