Forum Discussion

vitoaiaco's avatar
vitoaiaco
Brass Contributor
Jul 31, 2023

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 B3 ("Summary" tab).

In order for the subtraction to take place, the following criteria needs to be met:

 

1. Matching Fleet number in the raw data and summary
2. Matching Transaction Type (Column E in raw data) as "SERVICE"
3. Matching Component (Column F in raw data) as "ENGINE"

 

Your help is appreciated as always!

 

Many Thanks

Vito 

  • vitoaiaco's avatar
    vitoaiaco
    Brass Contributor
    Thank you Gentlemen and sorry for the late reply!
    Both your solutions worked, I'm extremely thankful

    Vito
  • 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, ""))
      )
    • vitoaiaco's avatar
      vitoaiaco
      Brass Contributor
      Thank you very much!
      I'm trying change the IFERROR function to return as an empty cell rather than performing the subtraction, but it's not working. Would you be able to help with this last request?

      Thank you
      • Patrick2788's avatar
        Patrick2788
        Silver Contributor

        vitoaiaco 

        The formula changes a bit. The XLOOKUP returns "" in the event of error then we check for number and do the subtraction.

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

Resources