Forum Discussion

Brass Contributor

Jul 31, 2023

Solved

OFFICE 365 - Subtraction between last entered value meeting criteria and another cell

Greetings to the forum! Please find attached file for your reference. In cell E3 in the "Summary" tab I would like to find the LATEST entry in column D ("Raw Data" tab) from the value in cell B...

ZPC DAILY OIL TOP UP SERVICE REPORT JULY V2.xlsx82 KB

Formulas and Functions

Patrick2788

Jul 31, 2023

vitoaiaco

Try this one:

=LET(
    lookup, FleetNo & "Service" & "Engine",
    lookup_arr, Raw_Fleet & TransType & Component,
    last_raw, XLOOKUP(lookup, lookup_arr, KMHRS, 0, , -1),
    IFERROR(LatestMileage - last_raw, LatestMileage)
)

ZPC DAILY OIL TOP UP SERVICE REPORT JULY V2.xlsx87 KB

PeterBartholomew1

Silver Contributor

Jul 31, 2023

vitoaiaco

Similar approach but, rather than concatenating search criteria, I filtered out rows that do not correspond to an engine service. There are still issues such as the last recorded mileage being lower than the latest service mileage.

= LET(
    engineService?,  (dataTransType="service")*(dataComponent="engine"),
    filtered,        FILTER(dataFleetNum, engineService?),
    interval,        FILTER(distance, engineService?),
    priorVal,        XLOOKUP(fleetNum, filtered, interval, 0, 0,-1),
    HSTACK(priorVal, IFERROR(latestMileage - priorVal, ""))
  )