Football Playoff Summary

[Richard2542]

Please review the below Instructional Notes to grasp an understanding as to how the Confidence Points are used.

Open the attached file: At the bottom of the sheet you will see a section that is titled Point Values Not Used; in addition, above this section you will see a section titled Points Used. The Points Used section uses the Players selection for winning the game along with the Confidence Points associated with that game. If the player's team wins, the Confidence points are added to the Player's total points; since the confidence point selection can only be used once, I need to develope a non VBA solution for showing the exact point(s) remaining. I tried using the INDEX and Match function as shown in cell E66; obviously, I dont have the formula correct, etc.

I would appreciate a solution that I could be used to show each Player's remaining Points; e.g. Player 1 has the following remaining points:
1, 12, 13

Instructional Notes:

Each player is required to select a Confidence Level (1 - 13) points per game. The Confidence Level number may only be used 1 time throughout the playoffs and

Super Bowl. The Confidence Level points are only awarded for a win (max points = 91). The key to winning the pool is the total amount of Confidence Level Points,

not the number of Wins. Total points scored in the Super Bowl will be used to resolve ties, e.g., Player 1 & 2 are tied, Player 1 goes over and Player 2 is under,

therefore Player 1 is in 2nd place and Player 2 is in 1st place. If both players are over, the closest Player to the Super Bowel score is 1st.

 

[Drrellik]

Football MrExcel - Google Drive

drive.google.com

Let’s try this Richard. Here is a link that anyone on this board can get to, so it is shared. Try to upload your file here in google drive then anyone here can look at it.

After that I don’t know how to help any further sorry Don

 

[Richard2542]

awoohaw...I've copied the file to my Microsoft OneDrive, but don't understand how to Post the Link, or share it to Dropbox, etc..

 

[Drrellik]

Usually outside of the folder that you’ve placed it in or if it’s in the route directory of the dropbox, there should be three dots out beside the file or the folder that would allow you to copy a link or share it at least that’s the way Google Drive works

 

[Richard2542]

Drrellik...I'm stuck here because I don't have a Name, group or email to share the Link to.

 

[Fluff]

Just click on the Copy button at the bottom & then paste that link to the site.

 

[Richard2542]

Here's the link:

Formula to find the used Confidence Points.xlsx

1drv.ms

 

[Richard2542]

Drrellik, Awoohaw and Fluff. I've inserted the link for Confidence Points worksheet. Hopefully this is what you've requested. I'm still struggling with completing the workbook and would appreciate your assistance...thanks

 

[Drrellik]

Richard,

Sorry it has taken me so long to get back but I was out sick for 2 days.

I downloaded you file and started looking at what you asked and noticed several #REF errors in the sheet,

1. if you look at cell AP17 & AQ17 they have errors, I fixed them but copying up from AP18,AQ18
2. Then move to cell range V37-46 they are hard coded to work only if cell B4 is = 4 so I changed B4 from 2 to 4 then the V range 37-46 went to all 0's no error
3. moving on to V37-V46 you will find that they are looking for data in S52-S61 for confidence points. the closest data in in O or Q 52-61
(changing this to O52... causes a circular reference error) not sure where this data is supposed to come from.

it looks like you have moved or deleted some data along the way and have several broken links / formulas

your question about Index Match is looking for the value in Row 52 looking to see if a number 1-13 has been used
I tried this quickly =IF(MATCH(E65,$B$52:$N$52,0),"",E65) and it returned an N/A in columns 1, 12, 13 where those numbers were not picked.
I will look for a better way to fix that so instead of N/A it returns the actual number

Don

 

[Richard2542]

To all...I am extremely disappointed with the Forum; I've been a member since 2017 and have found this Forum to be absolutely excellent in responding and solving Excel problems/issues; however, the current request has now gone unanswered/unsolved. I have followed all the requests in a timely manner and supplied the Forum with the requested information...to date, I have not received a response to my current request - this is inexcusable.

 

[Richard2542]

Richard,

Sorry it has taken me so long to get back but I was out sick for 2 days.

I downloaded you file and started looking at what you asked and noticed several #REF errors in the sheet,

1. if you look at cell AP17 & AQ17 they have errors, I fixed them but copying up from AP18,AQ18
2. Then move to cell range V37-46 they are hard coded to work only if cell B4 is = 4 so I changed B4 from 2 to 4 then the V range 37-46 went to all 0's no error
3. moving on to V37-V46 you will find that they are looking for data in S52-S61 for confidence points. the closest data in in O or Q 52-61
(changing this to O52... causes a circular reference error) not sure where this data is supposed to come from.

it looks like you have moved or deleted some data along the way and have several broken links / formulas

your question about Index Match is looking for the value in Row 52 looking to see if a number 1-13 has been used
I tried this quickly =IF(MATCH(E65,$B$52:$N$52,0),"",E65) and it returned an N/A in columns 1, 12, 13 where those numbers were not picked.
I will look for a better way to fix that so instead of N/A it returns the actual number

Don

Don - Thanks so much...I just sent a message to the Forum expressing my disappointment with the response on my question - I'm sorry that you were ill and hope that you are feeling much better. I do appreciate your help (this is more like it with the MrExcel forum)