Ranking Function Within Excel

[jcl6543]

Hello all,

I have a spreadsheet that I am working on and could use some help if anyone has the time.

This sheet has two tabs. The first tab is for racing RESULTS. As our season progresses column A will keep a total of the points for each driver as we enter them in week 1, week 2, week 3, etc. (The formulas on the first tab work as I already added formulas so you can try it out. Just use the drop down box for week 1)

The 2nd tab I would like to use for STANDINGS. I would like the driver with the most points ranked first and so on and so on. The driver’s name would be in column B and his/her points in column C.

Column D I would like for the number of his/her wins turned into an asterisk instead of a number. 1 win = *, 2 wins = **, 3 wins = ***, etc etc.

I am unfamiliar with the rank formula and was wondering if anyone could look at my two tabs and tell me how I can transfer the data from the RESULTS to the STANDINGS tab so we can keep track of our drivers throughout the season?

I appreciate any help, advice or feedback.

Thank you in advance.

Jeff

test point chart.xlsx

[sktneer]

Hi jcl,
Welcome to the Forum.

Please find the attached sheet to see if this is what you are trying to achieve.

[Glenn Kennedy]

Hi there. How does this meet your expectations?

[micope21]

Hi

Glenn Kennedy got a correct result file!

Only problem is only go up to 10 win ********** if driver name get say 13 win *?

Only thing need change is how many win ****?

D2

=REPT("*",SUMPRODUCT(('Modified Results'!$B$3:$B$100=B2)*('Modified Results'!$C$3:$X$100="1st")))

copy down.

This will get you 13 win *************

Regard

[jcl6543]

sktneer, Glenn Kennedy & micope21,

That's exactly what I was trying to achieve. I was trying to use the wrong formula and now see the correct way to do it.

You've taught me some valuable knowledge today and I appreciate your time and your expertise.

10 wins is sufficient because the last time a driver had more than 10 wins in a single season was in 1962. With today's competition and tight racing, the chances of a 10 win season is highly unlikely.

Thank you again. You guys helped me learn a lot.

Jeff

[sktneer]

You're welcome. Thanks for the feedback.
If that takes care of your question, please mark your thread as Solved by selecting Thread Tools (just above your first post) --> Mark thread as solved.

[Glenn Kennedy]

Thanks... you seem to know your racing!!!

[jcl6543]

You're welcome. Thanks for the feedback.
If that takes care of your question, please mark your thread as Solved by selecting Thread Tools (just above your first post) --> Mark thread as solved.

Done!

Thanks... you seem to know your racing!!!

I love it. Dirt Track Modified Racing in NY is awesome. This spreadsheet will help take the 'manual labor' and calculator out of the point updates each week and get us out of the Stone Age and vault us into the 21st century.

Thanks again.

\1

44847.jpg

[jcl6543]

May I ask one more question regarding this spreadsheet you guys helped me with earlier?

Our season is 22 weeks which is reflected on the RESULTS tab. 4 of those weeks, we run for double points.

Would the spreadsheet be able to calculate the double points for specific weeks only? I added the double points and standings in columns AD & AE.

For instance, say week 1 and week 2 are regular points. Week 3, we race for double points and then week 4 & 5 etc we run for regular.

Are spreadsheets smart enough to calculate the double points for week 3 and then go back to scoring regular points for week 4 and 5?

Is that overly complicated to do? I have to be honest, I would not even begin to know how to do that.

OCFS Points 2015.xlsx <----I renamed my sheet.

[Glenn Kennedy]

I have a few ideas that I'll look at. Are the double-point weeks fixed. If so, which weeks are they?

I can't help feeling that this is going to be a bit awkward. The problem (so far as I can see) is that it might be difficult to allow SUMPRODUCT to choose one set of points or another. Maybe someone else can see any easy way through this. It can be done by brute force & ignorance, but - even then - there would have to be a way of marking/identifying the double-point races.

[jcl6543]

I have a few ideas that I'll look at. Are the double-point weeks fixed. If so, which weeks are they?

I can't help feeling that this is going to be a bit awkward. The problem (so far as I can see) is that it might be difficult to allow SUMPRODUCT to choose one set of points or another. Maybe someone else can see any easy way through this. It can be done by brute force & ignorance, but - even then - there would have to be a way of marking/identifying the double-point races.

This season the double points nights were Week, 1, 5, 11, 15 and 19. This sheet here is for next season and the schedule won't be posted until February or March of 2015, but they will be fixed once it is posted.

Don't pull your hair out trying to figure it out. We could always manually adjust for double point nights. Plus, I have all winter long to try and make it so it works.

I really appreciate your time and effort. Some of the stuff you guys did already would take me hours to learn, but after seeing the formulas it makes it much clearer and easier to understand.

Thank you very much!

[jcl6543]

OMG Glenn. That is terrific! Your wild guess was right on the money.

Thank you 1000 times.

This is a great forum.

I really appreciate it.

[Glenn Kennedy]

Sorted... Actually it was simple (once I took a wild guess - to be honest). Take a look and try to break it.

[Glenn Kennedy]

You're welcome. Well... the thread's already marked as solved. I don't know if the forum will let you hit the Rep button again, but you might like to try! Time to go out for an evening in a "fine dining" restaurant.... It might be a late night...

[jcl6543]

Hello again.

I have one more thing to add to my spreadsheet that you guys helped me out with last week.

I added something called “Heat Points” in column D. Our drivers collect heat points dependent on their finishing order of heat races:

1st Place = 5 points
2nd Place = 4 points
3rd Place = 3 points
4th place = 2 points
5th place = 1 point

I made drop down boxes for column D.

All I simply want to do is add those “Heat Points” over to the “Season Points” in column A.

Example: Billy VanInwegen wins the feature race for 100 points. He also finished first in his heat which would give him 5 addition points added to the 100.

I’m just not sure where to add the SUM function in the formula in column A.

Can someone let me know how I can do this?

I really appreciate the help.

Thank you very much.

Jeff

OCFS Points 2015 EDIT.xlsx

[Glenn Kennedy]

Will every race have heat points awareded? If so, it might be more logical to have them first, then the points for the main race... Let me know.

[jcl6543]

Will every race have heat points awareded? If so, it might be more logical to have them first, then the points for the main race... Let me know.

Yes. Each week/race every driver will have heat race points awarded. (We run heat races to determine the starting order of the feature race and the heat points are an added incentive to pass cars)

Thanks very much, Glenn. I appreciate all of your help.

Jeff

[micope21]

Hi

Sort it!!

Modified Results sheet

Column BE I change it to 1st,2nd,3rd,4th,5th

D4

=IFERROR(VLOOKUP(C4,$BE$5:$BF$9,2,0),"")

copy down
Then copy cross every 2 column say HP.

I didn't want to mess up Glenn Kennedy array formula column A.

So
Modified Standings

I change Column A2

=IFERROR(INDEX('Modified Results'!$B$4:$B$101,MATCH(1,INDEX(('Modified Results'!$A$4:$A$101=LARGE('Modified Results'!$A$4:$A$101,ROWS(B$1:B1)))*(COUNTIF(B$1:B1,'Modified Results'!$B$4:$B$101)=0),),0)),"")

copy down.

Column B

=IF($B2="","",SUM(IF('Modified Results'!$B$4:$B$101=$B2,'Modified Results'!$A$4:$AT$101)))

This is array formula! Press same time: Ctrl+Shift+Enter, NOT ENTER. copy down.

Column D2

=IF($B2="","",SUM(IF('Modified Results'!$B$4:$B$101=$B2,'Modified Results'!$D$4:$AT$101)))

Array formula C,S,E copy down.

See the file!

Regard

[jcl6543]

Micope21,

Thank you for your time/efforts in doing that, but I should have explained my dilemma in more detail.

The Heat Points are not based off of the driver’s finish in Week 1, Week 2, Week 3, etc.

The Week 1, Week 2, Week 3, etc finish is where the drivers finish in their feature race. That’s where they get their SEASON POINTS from.

The HEAT POINTS would simply just be added to those SEASON POINTS, not based off their finish in Week 1, Week 2, Week 3, etc.

When racing begins, we run heat races. Heat races determine the driver’s starting position in the feature race. Their feature race finish determines how many SEASON POINTS they receive.

The HEAT POINTS I would just simply add; 5 points for finishing 1st in their heat, 4 points for finishing 2nd in their heat, 3 points for finishing 3rd in their heat, 2 points for finishing 4th in their heat and 1 point for finishing 5th in their heat.

I don’t need to keep track of HEAT POINTS, I merely just need to add them to their SEASON POINTS.

Example:

Driver Billy VanInwegen wins the feature race and I enter "1st" for him, giving him 100 SEASON POINTS in column A4. Earlier in the night, Billy also won his heat race collecting 5 additional points to his overall SEASON POINTS.

I just need those 5 addition points added to the SEASON POINTS.

I hope this explains a little better. I apologize for the work you did. I am very grateful for your help and expertise.

Thank you very much. I appreciate it.

Jeff.

[micope21]

Hi

Sorry should have know better lol.

See the file!

Regard

[Glenn Kennedy]

Is it OK now? I'm away from a PC until tonight...

[jcl6543]

That's exactly what I was looking for!

Tomorrow I am going to test it out and see how everything works and then make some tweaks with colors and borders for aesthetics.

Thank you so much. You guys have helped me learn a lot from the formulas you helped me out with. I couldn't have done it without you. I spent literally hours trying to figure some of this stuff myself and read and watched tutorials. Actually looking at the formulas has helped me a great deal.

I appreciate your time, efforts and knowledge. This is a great site.

I will test it out tomorrow because right now I'm off to, where else...the racetrack for the final race of the season.

Be well gentlemen and have a great weekend!

Jeff

[jcl6543]

I tested my sheet this weekend and discovered one minor issue.

Question: Is there a way to add the HEAT POINT number in column D over to the SEASON POINTS in column A?

Without doing that, it doesn’t sort the MODIFIED STANDINGS tab correctly.

If you open the sheet you’ll see:

CREEDEN finished 1st, but received zero HEAT POINTS.
RUGGIERO finished 2nd and also received 5 HEAT POINTS.

CREEDEN collected 100 points on the night
RUGGIERO collected 101 points on the night.

When you click MODIFIED STANDINGS you’ll see it shows RUGGIERO in 2nd place instead of 1st.

If the HEAT POINTS were added to the SEASON POINTS in the MODIFIED RESULTS tab, I think it would fix that.

How can I add those heat points to the season points?

Thank you in advance for any help on that. If someone could show me how to do it in ROW 4, I can do the rest of the document.

I appreciate any help/advice/suggestions.

Sincerely,

Jeff

jcl6543.xlsx

[Glenn Kennedy]

I've gone back to my previously posted version and twiddled with it, as you asked. I think that it's OK now. The only major visible change is in the header rows. Try to break it...

[micope21]

Hi

Sort it

See the file!

[jcl6543]

Thank you very much, guys for all of your help and knowledge.

When work is over tonight I will have a look at these and test them out.

I had a dream last night that I was working on a giant Excel spread sheet trying to manipulate the formulas. That's pretty scary. I must need a break from this.

I will be in touch soon.

Again, can't thank you guys enough for your help!