Finding the last highest value, and subtracting from @row value

GDavies

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:

1. Find the highest number of hours entered for the same machine on the current row
2. 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 V-Groove 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 V-Groove, shown below, it just simply produces "0", and does not go and look for the next largest value that is a 4221 V-Groove. This is INCORRECT. The correct answer would be 10 (1055 - 1045=10)

Can anyone assist with a better way of doing this formula?

Paul Newcome

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.