Formula to return highest value from duplicate entries

[JungleJme]

Hi,

BACKGROUND

I have a large table of data listing jobs with individual values where the same job can appear twice or more in the table as a job can contain several parts or segments.

PROBLEM
We have a measure for how long the job took which also appears next to each entry. Where there are more than one segment for a job in the table i want to add a column that will check the "Days" value and if it is the highest number for that job reference then it will return that value. However, if it finds another job in the table with a higher "days" then it will return zero.

This means i can find out how long a job really took and ignore smaller values that are going to mess up averaging in top level pivots.

Attahced is an example of what i am after but i am stumped how to achieve it. You will see Job 99 has two segments and the column i want to calculate has zeroed the lowest entry and returned the highest.

Thanks in advance for any help!

J

[JungleJme]

For anyone else searching, i figured out by using an array formula and max if functions.

See attached for solution.

Ta

J