Finding the last highest value, and subtracting from @row value
Hi,
I have a sheet where CNC operators enter the hours the machine has worked each day, and i want to avoid them having to reset the time clock eacht time.
I have been struggling with a formula that helps me achieve the following objective:
 Find the highest number of hours entered for the same machine on the current row
 Subtract this from the time entered on the current row
This needs to be written in a way that i can convert it to a column formula (i.e. using @row terminolagy...)
I have been using the below formula so far, and this works for finding the highest number, as shown below. This is the CORRECT result in this instance, as 1050 for the 4221 VGroove is the highest number, and the sum is correct. (result is 5 in "Recorded Cutting Hours" column)
Formula: =SUM([Cumulative Hours]@row  (SUMIFS([Cumulative Hours]:[Cumulative Hours], [Machine Type]:[Machine Type], =[Machine Type]@row, [Cumulative Hours]:[Cumulative Hours], LARGE([Cumulative Hours]:[Cumulative Hours], 2))))
However, if i change the machine type to 6321 VGroove, shown below, it just simply produces "0", and does not go and look for the next largest value that is a 4221 VGroove. This is INCORRECT. The correct answer would be 10 (1055  1045=10)
Can anyone assist with a better way of doing this formula?
Answers

Try adding a COLLECT function to the LARGE portion.
LARGE(COLLECT([Cumulative Hours]:[Cumulative Hours], [Machine Type]:[Machine Type], @cell = [Machine Type]@row), 2)
This way your LARGE function is also only looking at that specific machine type as opposed to every machine type.
Help Article Resources
Categories
Check out the Formula Handbook template!