Forum Discussion
Solved
subtract filtered data - need help with formula
Hello. I need some help 🙂
I want a functional formula in column E (difference) where, after filtering by machine in column B (machine no), the program will recognize and subtract hours greater (below) from hours smaller (above) in column E. That's the main rule.
So, I have a problem when I want to filter and get the hours to calculate the average consumption in the column G (l/h).
If we were to filter machine number 354, a regular formula in column E would subtract from the first following row above, not from the first following row of the filtered machine, which would produce an incorrect result.
The "Difference (h)" represents the difference in hours (how many hours the machine worked) compared to the previous refueling. When you divide the new refueled quantity of fuel by these hours, you get the average consumption per hour of machine operation.
I hope I explained it nicely.
Best regards,
Andrej
LOOKUP and VLOOKUP are limited compared to XLOOKUP.
Try this in E2. It is an array formula, to be confirmed by pressing Ctrl+Shift+Enter.
Then fill down.
=IF(MAX((B$1:B1=B2)*ROW(B$1:B1))=0,"",D2-INDEX(D$1:D1, MAX((B$1:B1=B2)*ROW(B$1:B1))))
4 Replies
Does this do what you want?
In E2:
=IFERROR(D2-XLOOKUP(B2, B$1:B1, D$1:D1, , 0, -1), "")
Fill down.
I'm using Excel 2016 Professional Plus. However, when I enter this formula with corrections ( ; insted of ,) I end up with an empty cell, probably because I don't have the XLOOKUP function.
I tried using the LOOKUP function in the direction you were thinking, but I didn't succeed.
LOOKUP and VLOOKUP are limited compared to XLOOKUP.
Try this in E2. It is an array formula, to be confirmed by pressing Ctrl+Shift+Enter.
Then fill down.
=IF(MAX((B$1:B1=B2)*ROW(B$1:B1))=0,"",D2-INDEX(D$1:D1, MAX((B$1:B1=B2)*ROW(B$1:B1))))
