Formula assistance please

Copper Contributor

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.  

19 Replies
It sounds do-able. Do you happen to have a sample workbook you can share?

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.

@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?

@Martin_Weiss  I am using 2016.  Having issues with how to upload.  suggestions?

Hi @Race_Mom 

 

under each reply, there is a little hyperlink where you can open the full text editor:

Martin_Weiss_0-1675179326179.png

In that editor, there is some space at the bottom where you can upload your files:

Martin_Weiss_1-1675179372982.png

 

@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.

@Patrick2788 Attached is my workbook as requested.

 

Please let me know what you think.  Thanks

@Race_Mom 

 

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.

 

@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.

@Race_Mom 

Here's an example of a situation where the points is the same for finishers:

Patrick2788_0-1675815203933.png

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?

@Patrick2788 sorry for the late response. It has been a crazy week at work. There will not be any additional points awarded to break the tie. I just need to put them in order accordingly. I input your earlier formula but I received an error saying #Name.

@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.

The 'newest' function used in that formula above is IFNA (It came out with Excel 2013). Which version of Excel do you have?
It works awesome now. I think I put in the draft formula as the one I see now has more items

@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 

Can the feature line up come from any of the 3 heats listed above?

@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!

I believe this is possible in Excel 2016 but it's going to involve a contorted formula because of the possibility of ties and the lack of 365 functions.
Now you have totally lost me