Hi all,
I am trying to create a table column that will designate when an athlete achieves a Personal Record (PR) on a particular test. Within my list, there are numbers where higher values are better (longer/higher distances, faster speeds etc...) and values that are better when they are lower (sprint times). My current formula is as follows:Formula:Please Login or Register to view this content.
In short, the first part of the formula is saying that if the test matches any of the listed test names, to do a Max If (I had to build it because although I have Office 2016, I believe there may be an issue with my licensing that doesn't give me the actual maxifs formula). The second part says that if it doesn't equal any of those test names, do a Min If formula.
My question is how can I alter this formula to only give an athlete a PR the first time they accomplish it, and then to leave the PR designation column blank should they match that value anytime after that? I believe a sumproduct formula integrated within that formula should work, but all ideas welcome.
I have attached my workbook, and filtered it down to an example athlete who had several values that were PRs, but I would only like for the first time he achieved 32 on the max output test to be designated as a PR, and the subsequent tests to just show as blanks.
Bookmarks