Scoreboard Creation - 5000 Game
Solved
margot8888
Posted messages
12
Registration date
Status
Member
Last intervention
-
ccm81 Posted messages 11033 Status Member -
ccm81 Posted messages 11033 Status Member -
Hello everyone,
I've been working on this topic for several days, without success... Let me explain:
Between friends, we play a dice game called "The 5000". The principle is simple: we roll dice and we have to score points (points can either be hundreds (100, 200, 300...) or in 50s (50, 150, 250...). The goal of the game is to be the first to reach exactly 5000 points. Otherwise, any excess points over 5000 are deducted (Example: my score is 4850 points and I score 350 points, so my score drops to 4800 points. From there, if I score 200 points, I reach 5000 points and I've won; if I score 300 points, my score drops to 4900 points, and if a competitor makes me lose 100 points, my score drops to 4700 points).
To simplify the calculations (which can be numerous during a game!), I started creating an Excel spreadsheet. Here's what I want: a table in which I note the points of various players in each round (positive if they earn points, and negative if a competitor makes them lose points), and a cell displaying the total score of each player. This total score must not exceed 5000 points, in which case any extra points (the surplus) should be deducted from 5000.
That’s the whole problem; I can’t find the formula to get the total score (and especially how to deduct the surplus). I’m missing the last step... (or maybe I’m not heading in the right direction ^^). I have a formula (IF function) that says:
-> if the total sum of the player's points is less than 5000, then the total score of the player equals the sum of their points
-> otherwise, do "5000 - (sum of player points - 5000)" (which is equivalent to saying: subtract from 5000 the surplus over 5000).
In summary, the formula is: TOTAL SCORE =IF(total sum of points > 5000, total sum of points, 5000 - (total sum of points - 5000))
So far, this works. Except that the second formula can only apply once. Indeed, since I am basing it on "5000 minus the difference from 5000", as I add points, this difference increases, and the points are deducted from 5000 instead of being added to it...
What should I do? Double condition? Create a ceiling? A max threshold? A macro...?
Thank you in advance for your help!!
I've been working on this topic for several days, without success... Let me explain:
Between friends, we play a dice game called "The 5000". The principle is simple: we roll dice and we have to score points (points can either be hundreds (100, 200, 300...) or in 50s (50, 150, 250...). The goal of the game is to be the first to reach exactly 5000 points. Otherwise, any excess points over 5000 are deducted (Example: my score is 4850 points and I score 350 points, so my score drops to 4800 points. From there, if I score 200 points, I reach 5000 points and I've won; if I score 300 points, my score drops to 4900 points, and if a competitor makes me lose 100 points, my score drops to 4700 points).
To simplify the calculations (which can be numerous during a game!), I started creating an Excel spreadsheet. Here's what I want: a table in which I note the points of various players in each round (positive if they earn points, and negative if a competitor makes them lose points), and a cell displaying the total score of each player. This total score must not exceed 5000 points, in which case any extra points (the surplus) should be deducted from 5000.
That’s the whole problem; I can’t find the formula to get the total score (and especially how to deduct the surplus). I’m missing the last step... (or maybe I’m not heading in the right direction ^^). I have a formula (IF function) that says:
-> if the total sum of the player's points is less than 5000, then the total score of the player equals the sum of their points
-> otherwise, do "5000 - (sum of player points - 5000)" (which is equivalent to saying: subtract from 5000 the surplus over 5000).
In summary, the formula is: TOTAL SCORE =IF(total sum of points > 5000, total sum of points, 5000 - (total sum of points - 5000))
So far, this works. Except that the second formula can only apply once. Indeed, since I am basing it on "5000 minus the difference from 5000", as I add points, this difference increases, and the points are deducted from 5000 instead of being added to it...
What should I do? Double condition? Create a ceiling? A max threshold? A macro...?
Thank you in advance for your help!!
11 answers
-
Hello
With what I understood
http://www.cjoint.com/c/EFrjPHA2hsg
But with this, you won't make any progress in mental math ;-)
Best regards-
Wow ccm81!!!! It's perfect, it's exactly the formula I was looking for!! You've taken a huge weight off my shoulders ^^!! A big thank you!
However, if it's not too much to ask.... do you think that the last value displayed as a score can appear in the header, under the players' names? Something like "only display the last value of the column 'total' (column name in your example)"? Or "display the value for which the next one equals"? And that on the other hand, all intermediate scores are not visible? That would be super classy ;-) .
As for mental math, after several years of playing the "5000 game" by hand, don't worry, we have to be good at mental math from the start :-D !!
For your information, here is my initial document: http://www.cjoint.com/c/EFrkVwfPUXa
Thanks again, and thanks in advance for the rest of the questions.
-
-
Something like this?
http://www.cjoint.com/c/EFrnhZNRUjg
Best regards-
Yes ccm81, great! Thank you very much! :-D
In the meantime, I searched on my side and found two different solutions from yours on other forums that work to display the last value of the total cell in a new cell (below the player's "total" cell):
- either the formula =SEARCH(9^9, E4:E31)
- or the formula =VLOOKUP(9^9, G4:G31, 1)
Yours being: =OFFSET(E3, COUNTA(D4:D32), 0, 1, 1)
All three work! I don't know what the searched value "9^9" corresponds to for it to work, but it works, so I'm not going to question it ^^, but I'm sharing the info anyway.
As for hiding the score details, I simply "hid the column".
Thanks again, and have a great continuation!
-
-
Hello everyone,
I am reaching out to you because I have made good progress on the score table project for the 5000 game, but I'm still struggling with some points:
Here is the file that I have managed to create so far for tracking scores for 4 players: http://www.cjoint.com/c/EGbmkBMSlHa
In it, you will find (from top to bottom of a player sheet): the player's name, their position relative to the others (1st, 2nd...), their total score in the colored box, with the points on the right and left of the score as ribbons (if a player exceeds 2500 points), and in the four corners, there are "X" marks for the players who have managed to get a "sec" (meaning they scored no points from the first throw of their turn). These X marks serve as a "protection": if a player affects you, for example -600 points, you are protected for 1 turn. To activate the protection, type "0" after the noted points, to remove it, type another "0"). Below the score: Secs are counted, as well as the number of negative and positive points that have been scored, or the number of throws needed to win. Some boxes are hidden; you need to display them to see all the formulas applied.
I'm still stuck on some points and maybe you could help me...?
- If we play with 4 and 2 players or more are tied (see the position of the player above their score), they have the same position (e.g., 2 (so 2nd)), the 1st is noted as "1" and the last as "4", whereas I would like the last to be "3". I'm currently struggling with this...
- Affecting negative points to all other players is tedious; if I roll a combination of dice that applies -300 points to all other players, I have to write "-300" for each player. Wouldn't there be a quicker solution?
- The ribbons (these arrows: <---- and ----> to the right and left of the player's score) appear when a player exceeds 2500 points (i.e., half of the path). I would like these ribbons to only appear for the player who crosses 2500 points first, and not for the others. Any ideas?
- 2nd "problem" following the previous one: affecting a bonus to the player's total score if that player has the ribbons (and furthermore removing it if they lose the ribbons, that is, if they drop below the 2500 points mark).
Thank you in advance if any solutions are proposed!
Best regards, -
Hello
- If we play with 4 people and 2 players or more are tied (see the position of the player above their score), they have the same position (e.g., 2 (thus 2nd)), the 1st is rated "1" and the last "4", while I would like the last to be "3". I'm stuck on this at the moment...
maybe like this
http://www.cjoint.com/c/EGbpVGVxiqg
For the rest???
Best regards-
Hello ccm81,
And thank you very much again! :-D !! For your responsiveness and what you proposed! The position system is working great! I understand the formula, I must admit I wouldn't have thought of it... However, it doesn't "work" when one of the 4 players is "at the start", meaning he hasn't scored any points yet (I have 1 2 3 3 in positions instead of 1 2 3 4, where 4 would be for the player at the start = 4th).
Have a great day! -
-
-
- assigning negative points to all other players is tedious; if I roll a combination of dice that applies -300 points to all other players, I have to write “-300” to each player. Isn't there a faster solution?
Yes, but a macro seems essential
http://www.cjoint.com/c/EGbtjIJFAPg
Good evening -
1. However, it does not "work" when one of the 4 players is "at the start", meaning it does not yet display any points (I have 1 2 3 3 in position, instead of 1 2 3 4, with 4 being for the player at the start = 4th).
Maybe by modifying the formulas in A15:A18
A15=IF(E5="Start!";0;E5)
and also correcting below
2. When you write "For the rest ???", should I understand that you do not know how to do it, or that I am not clear in my request ^^?
Both ;-)
Best regards-
Thank you for this information ccm81, I will take back the positions with this correction this evening.
For "the rest", I will revisit my request ^^:
You will notice when a player goes from a score of <2500 to a score >=2500 points, that these symbols: <---- and ----> appear to the left and right of the player's score. These are the "gallons", a kind of "distinction/reward" and "marker" when one has completed half the journey (5000/2=2500). Currently, all players receive the gallons when they exceed 2500 points.
---> In the future, I would like only the player who is the first to exceed (or equal) 2500 points to receive the gallons. If other players (in 2nd, 3rd, or 4th position) subsequently exceed 2500 points, then they do not receive the gallons (since another player has already received them).
---> Therefore, this player must also keep their gallons even if one (or more) player(s) deducts points from them and their score falls below 2500 points (as they remain the first to have surpassed it).
---> The "must" would be for the player who receives the gallons to automatically earn a bonus of X points (let's say 500 points), during the game itself (at the moment they exceed or equal 2500 points).
I hope I have been clearer.
Thank you in advance,
Best regards,
And have a great end of the day!
-
-
An attempt to manage the ranks (macro), at least with what I understood. It is not quite ready for the cell that receives the bonus (I only planned for the one below the input)
I had to name a few ranges/cells and modify the formulas in column A
http://www.cjoint.com/c/EGdinUbDZTg
One question. rank (in the military sense) or gallon (3.78 liters)?
Best regards-
Hello ccm81!
First of all, I apologize for my spelling mistake, it is indeed "galloon" in the military sense.
From what I see of the macro for the galloons, I feel like everything is working: the galloons are only awarded to the first player who reaches 2500 points, the bonus is automatically assigned to them, others cannot have it until the end of the game, and if the "gallooned" player drops below 2500 points, they keep it. Perfect! So, I don't understand when you say that "it's not quite right for the cell that receives the bonus (I've only planned for the one below the input)."
I have modified the formulas in A15 to A18 again: I simply replaced the 0 with -10000 in the formula =IF(E5="Departure!";-10000;IF(E5="Won!";5000;E5)) so that the one who is at departure is always last (even if a player starts their game in the "negatives"). And all of that works perfectly!
Here are the results: http://www.cjoint.com/c/EGdl7sA6F3a
Great macro to erase everything and start a new game, that's really handy!
For the macro that assigns negative points, there is a little "glitch"... but I don’t want to take up too much of your time, skills, or patience... so I'll understand if you don’t follow up...
--> The observation: when a player (J1 for example) makes a "set" (a combination of dice that is worth no points and is quite rare), I note 0 in the player's score. At that moment, 4 crosses (like this: X) appear in the four corners of the player's score. These crosses represent a protection. Then, when another player (J4 for example) assigns negative points to the other players (J1, J2, and J3):
- if the player is not protected: I apply the negative points to the player;
- if the player is protected: I add a 2nd zero in the scores of the protected player(s), which cancels their protection and makes them vulnerable again.
--> The problem: the macro should not be applied to the protected players (= who have the crosses).
--> What would be ideal (but I am completely overwhelmed...) is to make the macro apply negative points to all other players (DONE ^^) only if they do not have protection. If they do, then cancel the protection by adding a "zero" in the points of the targeted player.
--> Additional question: is it possible to enter a positive number (for example, "100" points entered in the macro window) and have it automatically attributed negatively ("-100" points) in the players' scores?
Thank you very much! For everything... Best regards
-
-
1. I've fixed the issue with the positioning of the gallon bonus (if we're in M39, the bonus needs to be placed in N12, it may not seem like much, but ...)
2. I've dynamically named the ranges of your chart to limit it to the ranges containing values, this prevents it from dropping to 0 after the last point, and if you want to track the evolution in real time, it's more readable.
(I took the liberty of changing the style to make it clearer)
3. --> Side question: is it possible to enter a positive number (e.g., "100" points entered in the macro window) and have it automatically assigned negatively ("-100" points) in the players' scores?
OK and cheaply: whether you enter a positive or negative number,
pena = -abs(pena)
will return it negative
4. Great macro to erase everything and start a new game, it's really handy!
A click on New Game will be enough to launch the macro
5. I've modified the formulas in A15 to A18 again: I simply replaced the 0 with -10000 in the formula =IF(E5="Départ!",-10000,IF(E5="Gagné!",5000,E5)) so that whoever is at the start is always last (even if a player starts their game in the "negatives"). And all of this works perfectly!
OK
5. For the macro that assigns negative points, there's a little "glitch" .................... by adding a "zero" to the targeted player's points.
pfffffffffffffffffff!
http://www.cjoint.com/c/EGdm2KvghBg
Best regards-
1. Ok for the story about the badges, I encountered this constraint (3 columns of points) for the total scores (hidden in column H).
2. Great initiative for the graph!! I don't know how you did it, I'll look for it at home ^^. The drop to zero complicated the reading, now it’s perfect.
3. Perfect!
4. Great, THE simplicity ;-)
5. Yes I know... lol Don't worry... for now I'll reassign the negative points that shouldn't have been to the protected player, and I'll manually add a zero, that'll do the trick, there’s no rush ^^.
--> I will work on my ideas (in the yellow box to the right of player 4's game grid) this weekend and see what I can do ^^.
Thanks again for everything!
Best regards and have an excellent end of the week!
-
-
One question.
Are negative points only assigned by clicking on the name of the "attributor" (via the dialog box)?-
I'm not sure I understand the question...
Do you mean: are there any negative points assigned to the player(s) outside of those given through the macro dialog box? The answer is no.
So if that's what you meant to understand and to answer your question: Yes, negative points are only assigned by clicking on the name of the "attributor."
-
-
-