Forum Discussion
vitoaiaco
Jul 31, 2023Brass Contributor
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
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) )
- vitoaiacoBrass ContributorThank you Gentlemen and sorry for the late reply!
Both your solutions worked, I'm extremely thankful
Vito - PeterBartholomew1Silver Contributor
- PeterBartholomew1Silver Contributor
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, "")) )
- Patrick2788Silver Contributor
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) )
- vitoaiacoBrass ContributorThank 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- Patrick2788Silver Contributor
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, "") )