I love this site and have taught myself a great deal from everything I've read here, but I'm now stuck with a very difficult problem that I feel is beyond my expertise. It's the last piece in a large puzzle, and it's the hardest piece! I need help writing the formula will calculate comparative values in realtime. I'll try to explain what needs explaining.
Excel Worksheet can be found here: http://www.sendspace.com/file/3fhr43
I've created a score sheet for a hypothetical athletic event, with a hypothetical outcome. For all intents and purposes, we'll just call it "High Jump". Per the rules of the event, each athlete is given two attempts at each height. Successes are marked by 1s and failures by 0s. An athlete may elect to skip any height. If successful at the height of his choosing, all previously skipped height intervals will be considered to have been completed successfully on the first attempt. If he fails at the height of his choosing, all skipped heights will also be considered failed.
Here's the tricky part... for tie breaking, if multiple athletes achieve the same distance, the winner will be determined by which athlete was successful in the fewest number of attempts. Should athletes still be tied at the greatest height, this same ideology will then default to the previous length, and so and so forth until a clear winner can be declared. Should athletes remain tied through all levels, all such competitors will share the same point value allotted to their finishing position, while leaving vacant the appropriate number of places thereafter (i.e. if two people are tied for 1st, the next closest competitor will receive the points allotted to 3rd place).
I'm working on completing the formulas in Column J ("Rank"). The current formula in each cell has been pulled through from previous work I'd done, and I've isolated the specific aspect of that needs to be written to account for the tie breaking conditions above. Using cell J8 as our focus we look at the function reading "IF(H8=0,$E$30,IF(H8=H7,J7,COUNT(J$7:J6)+1))", the J7 needs to be replaced with a function that will compare the "# of attempts" rows of the respective athletes such that it satisfies the tie breaking rules I've written above.
The lower green highlighted rows are where I manually input my hypothetic successes/failures. Everything else calculates automatically and feeds into the upper table arrays "Scoring" and "Sorting"
Please help! Also, if I've left anything out, please tell/ask me. Thanks.
Excel Worksheet can be found here: http://www.sendspace.com/file/3fhr43
I've created a score sheet for a hypothetical athletic event, with a hypothetical outcome. For all intents and purposes, we'll just call it "High Jump". Per the rules of the event, each athlete is given two attempts at each height. Successes are marked by 1s and failures by 0s. An athlete may elect to skip any height. If successful at the height of his choosing, all previously skipped height intervals will be considered to have been completed successfully on the first attempt. If he fails at the height of his choosing, all skipped heights will also be considered failed.
Here's the tricky part... for tie breaking, if multiple athletes achieve the same distance, the winner will be determined by which athlete was successful in the fewest number of attempts. Should athletes still be tied at the greatest height, this same ideology will then default to the previous length, and so and so forth until a clear winner can be declared. Should athletes remain tied through all levels, all such competitors will share the same point value allotted to their finishing position, while leaving vacant the appropriate number of places thereafter (i.e. if two people are tied for 1st, the next closest competitor will receive the points allotted to 3rd place).
I'm working on completing the formulas in Column J ("Rank"). The current formula in each cell has been pulled through from previous work I'd done, and I've isolated the specific aspect of that needs to be written to account for the tie breaking conditions above. Using cell J8 as our focus we look at the function reading "IF(H8=0,$E$30,IF(H8=H7,J7,COUNT(J$7:J6)+1))", the J7 needs to be replaced with a function that will compare the "# of attempts" rows of the respective athletes such that it satisfies the tie breaking rules I've written above.
The lower green highlighted rows are where I manually input my hypothetic successes/failures. Everything else calculates automatically and feeds into the upper table arrays "Scoring" and "Sorting"
Please help! Also, if I've left anything out, please tell/ask me. Thanks.
