Jan 29 2023 09:55 AM
My son races a sprint car and this year our group will be using passing points from a heat race to come up with the feature race line up. I will be responsible for posting this information for the group. I will already have the drivers names and starting position for their heat race on one tab and wanting to come up with a formula that once I put in their finish from the heat that it automatically calculate their points looking at the passing points tab (2nd tab) from where they stated and finished and pull their points. Any suggestions? Thanks for your assistance.
Jan 30 2023 06:27 AM
Jan 30 2023 06:30 AM
Hi @Race_Mom
could you please upload your example file with some descriptions in it, so we could better understand what it looks like and provide maybe a solution.
And also please let us know which version of Excel you are use, because in some cases this might be affect a proposed solution.
Jan 30 2023 04:44 PM
@Patrick2788 Wonderful! I figured if I can think it Excel could do it. I am using Microsoft Office Professional 2016. I am having issues on how to attach my workbook. suggestions?
Jan 30 2023 04:46 PM
@Martin_Weiss I am using 2016. Having issues with how to upload. suggestions?
Jan 31 2023 07:36 AM
Hi @Race_Mom
under each reply, there is a little hyperlink where you can open the full text editor:
In that editor, there is some space at the bottom where you can upload your files:
Jan 31 2023 06:22 PM
@Martin_Weiss Thanks for the assistance on uploading. That is what I was looking for but didn't see it before. Attached is my spreadsheet, it has 2 tabs.
Feb 06 2023 05:27 PM
Feb 07 2023 09:26 AM
This can be done with an INDEX-MATCH (I've created some named items to make the formula more readable.
=IFNA(INDEX(points,MATCH(E4,Finish,0),MATCH(D4,StartingPosition,0)),"")
You'll have to fill me in on Pill# and if that needs to be addressed for ties.
Feb 07 2023 02:28 PM
@Patrick2788 thank you so much for getting me going in the right direction. The pill # is a number the racer pulls for their starting position for the heat race. Therefore if a driver ends up with the same number of passing points then whoever has the smaller pill number will get placed before the other.
Feb 07 2023 04:14 PM
Here's an example of a situation where the points is the same for finishers:
Driver 'h' has the lower pill# with 1, while 'a' has 3. Both have 83 points. Would you like to assign 'h' more points to break the tie or change something else?
Feb 10 2023 05:30 PM
Feb 10 2023 05:56 PM - edited Feb 13 2023 04:19 PM
@Patrick2788Just checked out the workbook you attached and it worked perfectly. This will really help me out when preparing the line ups for the feature. Now my next question is to come up with the easiest way to get my feature. My only thought was to copy the 3 heat finishes into a column with their points & pill # and do a sort and then just cut and paste into the feature line up section. I know you are probably shaking your head at me now. LOL. Thanks so much for helping me. Hoping you can come up with the next step, getting it automatically to the feature line up.
Feb 11 2023 01:43 AM
Feb 11 2023 06:59 AM
Feb 14 2023 05:12 PM
@Patrick2788 I love how that formula came out. I really owe you one. Any suggestions on how to get the line of for the feature to fill in automatically from the points that got figured from the heat race? I really need to send you a gift card or something for all your help. Thanks
Feb 15 2023 05:33 AM
Feb 15 2023 02:52 PM
@Patrick2788 Hi Patrick! The feature race will come from all 3 heats. Highest points start first etc to 22nd being the lowest after you remove the last 2. We will only race 22 cars max. As mentioned before if anyone has a tie in points then the driver with the lowest pill for heat lineup goes ahead of others tied with. Not sure how difficult this will be to accomplish but I have no problem sorting them as a last resort. Thanks again and good luck!
Feb 16 2023 08:28 AM