Need help with stats In Bankroll sheet to calculate results from tournament sheet.

For cash, satellites Sit n GO, Spin & Gos and high rollers: I would like the Dropdown list to give the results for each poker site. AC:AE rows 3:42 down, Largest Cash Wins, and tourney Yearly Stats.
and lastly. Help merge rows 3:5 from workbook Example, into Tournament & Cash in Tournament sheet.

Please feel free to ask any question.

Put rows 1:5 from the Example file above tbl_Tournaments and fixed the circular reference errors that were produced.
Have filled in the formulas for those column headers in row 1 that match with column headers in row 6.
Have filled cells in yellow where there was no match.
You will need to tell us which columns in tbl_Tournaments the 'Cashes', 'Day Results' and 'Mawney' headers should match.

Assuming that "Mawney would be the Balance" means the last amount in the Balance column.

As for post #6 the formulas from AB12:AB18 have been copied and pasted into the satellites, sit n Go and spin & gos sections.
As for post #7 I encourage you to try that for yourself and let us know if you have any problems.

Largest cash wins: =IFERROR(LARGE(tbl_CashGames835[Profit],ROWS(AG$3:AG3)),"")
Yearly stats:
Profit: =SUMPRODUCT((tbl_Tournaments[Profit])*(YEAR(tbl_Tournaments[Start])=AI$12)*(MONTH(tbl_Tournaments[Start])=MONTH(AI13)))
Played: =SUMPRODUCT((YEAR(tbl_Tournaments[Start])=AI$12)*(MONTH(tbl_Tournaments[Start])=MONTH(AI13)))
Rebuys: =SUMPRODUCT((tbl_Tournaments[Rebuy])*(YEAR(tbl_Tournaments[Start])=AI$12)*(MONTH(tbl_Tournaments[Start])=MONTH(AI13)))

Mate, I finaly found away to help me with the entries, is still going to take me few days to get all 19k
something that would help me alot is the
1. Start time format is this Jul 28, 2023 11:00
2. Can a formula be used to detect the catagory? it would be almost 20k entries to have to do it manualy. I am inputing the entries from the day i Started staking.

I set the format of the start time column to [$-en-US]mmm d, yyyy hh:mm which should produce start times like Jul 28, 2023 11:00 when you type in 7/28/23 11:00 (remember to leave a space between the date and the time)
If there are several rows that all share the same date time then you could fill each consecutive row by pressing the Ctrl + d keys
On the Info sheet column AT is a list of 228 distinct tournaments from column G on the Tournament sheet.
Fill in column AU with the category for each tournament. Note that I have filled AU27:AU89 although I may be wrong about some of those.
The formula for the Category column on the Tournament sheet is: =INDEX(Info!AU$2:AU$229,MATCH([@Tournaments],Info!AT$2:AT$229,0))

If the file becomes too large to upload as an .xlsb try to zip the file and upload that way.

Thank you, mate
Very nice, I will do the catagory once, I finished with the rest of the inputs. cheers
about to hit the 10k mark,

Sort A to Z will sort the start dates oldest to newest.

That is something with which another contributor may be able to help because I can't think of a way to speed up the process myself.
I suggest that you show us a few examples of the workbooks that you downloading.
I'll try to take a look in the morning and see if I have any ideas then.

Mate, just finished the inputs. I don't think is sorting the way it used to before the start date was modify.
I would like to have it to sort from the 1st date i started to stake to the last day 8/5/23 like that, I can check all the prizes, deposits 1 by 1 and delete some of the canceled stakes, not sure if, I am really down that much i though maybe few thousand but not over 10k. will continued to work on it all day tomorrow.
Mate, it won't upload even if zip, here is the link
https://drive.google.com/file/d/18e9...usp=drive_link

It shows as an .rar type file, with which I am not familiar and cannot open.
My guess is that the values in the start column are text entries.
I suggest removing the left alignment from the start column and widening it enough to see if the values are naturally aligned left or right.
If the values are naturally aligned to the left they are text which will need to be changed.
Here is a tutorial that shows how the Text to Columns feature on the Data tab can do the job relatively fast: https://www.excelcampus.com/tips/tex...%20is%20...%20
Question: What is the size of the .xlsb file and what is the size of the .zip file?
The forum says you are allowed to upload either of those file types up to 9.77 Mb

Thank you mate,
I was able to make it sort as before. I will try to upload, again file is about 3.92 mb


3444.png
keep getting this msg size 3.13 mb

Mate, not sure why is not letting me upload.
https://drive.google.com/file/d/1S71...ew?usp=sharing

Mate, try see if you can download from here

I was able to download the file.
It shows as being 4.07 Mb and will compress to a .zip file with a size of 3.39 Mb.
I cannot get the .zip file to upload either.

On the Tournament sheet:
For the profit column change the formula to read: =[@Prize]-[@[Buy-in]]
Resize tbl_Tournaments to $A$1:$AB$17238 (Note that this may cause the computer to buffer for a minute or two)
Delete the Prize 2 column
On the bankroll sheet change the following formulas to read:
Cell AB21: =SUMPRODUCT((tbl_Tournaments[Prize])*(ISNUMBER(SEARCH("¥",tbl_Tournaments[Tournaments]))))
Cell AB22: =SUMPRODUCT((tbl_Tournaments[Buy-in])*(ISNUMBER(SEARCH("¥",tbl_Tournaments[Tournaments]))))
Cell AB23: =SUMPRODUCT((ISNUMBER(SEARCH("¥",tbl_Tournaments[Tournaments])))*(tbl_Tournaments[Profit])*(INT(tbl_Tournaments[Start])=TODAY()))
Cell AB25: =SUMIFS(tbl_Tournaments[Prize],tbl_Tournaments[Prize],"<>",tbl_Tournaments[Category],Z19)/AB20
There may be some other formulas that need yuanconv to be removed. If you find one let us know.

Thank you mate, Okay I will stop and do the changes now.
brb
Amt. is dropping -$9,135.40 it will continued


mate, how do i Resize table? where do i go?

With any cell in tbl_Tournaments selected you should see a Table Design tab at the top of the sheet.
Select the Table Design tab and look at the far left end of the ribbon to find Resize Table.
Select Resize Table and change $A$1:$AC$17238 to $A$1:$AB$17238 and then press OK.

Thank you, Mate done
Mate, I was thinking, can we have a column in tournament where it shows profit/loss by player to see rank of wins or losses where I can easy see whos at profit or what amount of losses like that ill stop and think, before staking him or maybe a bit %

like than when it shows on pivot table i can see it

Here is a small sample of the Tournament sheet.
Please show us how the column of profit/loss by player should look.

thank you mate,
let me download and see
Something like this where i can sort by highest to lowest
unless you can think of a better way

I am guessing that "something like this" refers to the values in H3:H22, however I feel that we need an explanation of how those values should be calculated.
For example how are the four values for player Fors[EX] calculated?

hi, mate hope your doing well as for me im doing well very happy
i finihed the inputs to the best i could would like you too see if everything is working as it should total bankroll running balance H5 is 7335.94 not the same and largest stake wins not showing results todays busying and todays cashes showing #N/A
in tounament sheet i see #n/a in category even i have a name on the info i try to upload got error here is the link sheet. https://drive.google.com/file/d/1WZB...usp=drive_link

In attempting to follow the link I get the following message:
"Google Drive
You need access
Request access, or switch to an account with access."

When GG is placed in the Network column in tbl_Tournaments and the amounts in cells H5 and H1 are the same.
Formulas for the Bankroll sheet:
For cells AD3 and down: =IFERROR(LARGE(tbl_Tournaments[Prize],ROWS(AD$3:AD3)),"")
For cells AE3 and down: =IF(AD3="","",INDEX(tbl_Tournaments[Player],AGGREGATE(15,6,(ROW(tbl_Tournaments[Player])-ROW(tbl_Tournaments[#Headers]))/(tbl_Tournaments[Prize]=AD3),COUNTIFS(AD$3:AD3,AD3))))
For cell P4: =SUMPRODUCT((tbl_Tournaments[Buy-in])*(INT(tbl_Tournaments[Start])=TODAY()))
For cell P5: =SUMPRODUCT((tbl_Tournaments[Prize])*(INT(tbl_Tournaments[Start])=TODAY()))
#N/A in cells F4:F5 because there is no match for Sunday Bounty King Jr $32.10 in cells AT2:AT229 on the Info sheet.
#N/A in cell F6 because G6 is blank and there is no match for a blank in cells AT2:AT229 on the info sheet.

I may be too late in mentioning this but to put GG in all cells of the Network column:
1. Select cell B2
2. Simultaneously press the Ctrl, Shift and down arrow keys
3. Type GG
4. Simultaneously press the Ctrl and Enter keys

In attempting to follow the link in post #50 I get the following message:
"Google Drive
You need access
Request access, or switch to an account with access."

Change the formula in the Category column on the Tournament sheet to read: =INDEX(Info!AU$2:AU$230,MATCH([@Tournaments],Info!AT$2:AT$230,0))
This will produce the following in cells F4:F5 "Bounty Hunters"
It would be better to change the data in columns AT:AU on the info sheet into an Excel table (select cell AT2, press the Ctrl and t keys and then select OK)
The formula for the Category column could then be modified to read: =INDEX(Table2[Category],MATCH([@Tournaments],Table2[Tournaments],0))
Any time a new row is added to Table2 then the formula in the Category column will automatically include that row in its calculations.

The formula in the Category column isn't consistent all the way down.
1. Remove the filter from the Category column
2. Paste the following into cell F2: =INDEX(Table2[Category],MATCH([@Tournaments],Table2[Tournaments],0))
3. Double click the fill handle so that the formula will copy down.
There are 344 blanks in the Tournaments column so those aren't going to have a category, unless you want to leave a blank in column AT on the info sheet and then put a category corresponding to the blank in column AU.
I am attaching a file that has Table2 with all previously listed tournaments and the tournaments that correspond to #N/A's in the Category column (with the exception of the blanks).
Note that from row 231 and down all categories are marked Bounty Hunters. I imagine that a number of them will need to be changed.
You can copy and paste the table into columns AT:AU on the Info sheet.

thank you mate, let me try to do the above changes. see how it goes. thank you

1. Remove the filter from the Category column on the Tournaments sheet
2. Paste the following into cell F2: =INDEX(Table26[Category],MATCH([@Tournaments],Table26[Tournaments],0))
3. Double click the fill handle so that the formula will copy down.

Yep, the reason is that all of the deposits are gone.
We need to start back with the file that is attached to post #52 which is named Staking 8-31-2023 (west123)
This time we need to leave the blanks in the Tournaments column on the Tournament sheet, because those correspond to the deposits/withdrawals.
As there are 337 deposits and only 7 withdrawals, I am going to suggest that Table26 be modified such that a blank cell is left in column AT and the word deposit is put in column AU.
You will need to manually make 7 changes for the withdrawals in the Category column on the Tournament sheet.
Let me know if you agree to this suggestion.

yes, mate that would be the esiest way to do it thank you. i will be back after im done doing my excersies eat and shower. ty mate.
mate just download the file in post 52 and that file don't show any deposit, i am manually inputing back all dposits. will upload once im done here is the link
running balance and bankroll total is different too mate. please let me know how to fix the #N/A

https://drive.google.com/file/d/1w1X...ew?usp=sharing

tbl_Tournaments shows 688 deposits and withdrawals while tbl_AllTransacations shows 344.

This is how I got rid of the #N/A's
1. Paste the attached Table2 onto the Info sheet
2. Change the formula for the Category column on the Tournament sheet to read: =INDEX(Table2[Category],MATCH([@Tournaments],Table2[Tournaments],0))
3. Manually type Withdrawal in cells F3271, F3683, F4958, F5918, F8812, F9058, F9645, F9945, F10019, F10336, F12492, F12840, F13491 and F13824

thank you a bunch mate, i think it working now. ty plese tell me how i will be entering new data new stakes, i don't want to mess formulas again.
mate, the running balance $6,335.31 and total bankroll -$1,160.63
updated file different https://drive.google.com/file/d/1w1X...ew?usp=sharing

Assuming that the running balance is the correct total bankroll, put the following into cell Y3 on the Info sheet: =INDEX(tbl_Tournaments[Balance],ROWS(tbl_Tournaments[Balance]))

mate, I was working on the changes. i input the withdrawal but for this numbers F5918 F5918 F9058 F9945 F10336 F12840 F13824F5918, F9058 F9945
it only hightlights the row but won't set the withdrawal amount. i will upload also the balances are different. will upload shortly the #N/A seem to be resolved
https://drive.google.com/file/d/1hNS...ew?usp=sharing updated7777.png
not sure why i get tha message when i open
not sure whats going on my changes got deleted it shows #n/a on the uploded file must be because the error msg

I get the same error message.
I don't see where Table2 was pasted into the Info sheet.
When I go back to the 8-31-2023 file and type withdrawal in cell F2733 then cell V2733 displays -$415.00
I notice that all the date times for both deposits and withdrawals in the start column entered twice.

I would try downloading the file from post #52 again. I just did and it looks like the deposits/withdrawals are correct.

okay, what we need to do to have it working without any error. ty mate, let me know since i been trying to find the answer to fixed the issue i when back to a file i had dated 8-4-2023 and re input the withdrawals and deposit but the withdrawals won't show. but the category all seems to be correct with out the #n/a i also paste the table #2 let me know if you would like to see it so i can upload maybe is easier to get it fixed not sure the only think is that i fon't have all the inputs from 2022 and the one in post#52 has all the tournaments.

If you can download the file from post #52 then I feel that we can fix the #N/A's for that file.
If you would rather use the file dated 8-4-2023 then please provide the link so that we can see if there is a way to correct the errors.

mate, i really don't know wthat going on i swear id my excel is not working well or i understood wrong. post#52 file after downloading it i don't see anythin in deposit column its black the whole column not sue whats really happening 4444.png

When I downloaded the file from post #52 I selected the filter/sort button in cell V1 on the Tournament sheet.
From there I scrolled all the way down and deselected Blanks.
There were 344 rows displayed.

whats the next step to have everything working mate, this is been really complicated seems we been stock on this for a few days now
after doing the steps above i can see the deposits

do you want me to do the steps again in post # 63 ? see if it works now

Paste Table2 from the file in post #63 into the Info sheet.
Let us know what the name of the table is once it is pasted into the Info sheet, by looking at the Table Design tab.

If the table name given in the Table Design is Table2 then change the formula in the Category column (Tournament sheet) to read: =INDEX(Table2[Category],MATCH([@Tournaments],Table2[Tournaments],0))

"mate, just want to let you know that withdrawals don't show only deposits"
Correct, as I stated previously you will need to manually type Withdrawal into some of the cells in the Category column.
To find out what cells do the following
1. Filter the Category column by selecting only deposit
2. Filter the Deposit column by selecting only Blanks
At this point only the seven rows that should be marked Withdrawal should be displayed.

its a been confusing mate
okay i select blanks where in column V? i don't see blanks in deposit column these mate sss.png

Select the filter/sort button in cell V1
Deselect the Select All check box
Scroll all the way to the bottom and select Blanks and then select OK

If you put a link to the file in your next post I'll look in the morning.

mate, please after completing the all the step i get this msg when i open file
ggg.png then anothe one come out after this one hhh.png
here is the file https://drive.google.com/file/d/1hNS...ew?usp=sharing
then when it completetly opens i get all the #N/A IN Category thank you mat, yes is okay ill talk to you in the morning. i will get some rest good have a blessed night

I am not sure why that error occurs, perhaps the following will help.
1. Select cell AT1 on the Info sheet
2. Press the Ctrl and t keys
3. Select the Table Design tab
4. Type tbl_Category into the Table Name window and press the Enter key
5. Paste the following into cell F2 on the Tournament sheet: =INDEX(tbl_Category[Category],MATCH([@Tournaments],tbl_Category[Tournaments],0))
6. Copy the formula down
7. Use the steps given in post #81 to identify the rows that should be Withdrawals (Note that there are now 9)

thank you mate, let me try all them steps mate, where is the design tab when i do control T this is what i get fff.png

When you get the Create Table dialog box select OK.

Okay now i can see the table styles IM in step 4 not sure whe to enter the table name like this matefff.png

Yes, in the Category column.
Once you replace Deposit with Withdrawal in those rows then the amount of the withdrawal should appear in column V.

mate, just finished but not all appear row 9945 and row 9058 they stay blank will upload file https://drive.google.com/file/d/1hNS...ew?usp=sharing update

I believe they are blank because looking at tbl_AllTransacations there are no deposits or withdrawals for either Oct 16, 2022 11:30 or Sep 18, 2022 06:30.

okay so do i delete them? i will double check all the deposit how about the totals mate
the totals is off by 20 dollars please help me find the error

There are only 344 deposits/withdrawals in tbl_AllTransacations and there are 346 deposits/withdrawals in the Category column, so I imagine those 2 records need to be deleted.

mate i think something its wrong with the buyin and prize column when i put buyins amt. instead of subtracting it is addin 100 to the running balance and doind the opposite with the prize money won when i put amt won in prize column it minus the amt. in column u

I think that the buy-in and prize columns are doing what they should. All of the amounts in column U are negative (deficit) so a buy-in increases the amount of the deficit and a prize reduces the size of the deficit.

thank you mate, your the master ty ty after a while my mind wear out now. how about the 20 dollars off the total bankroll and running balance not matching.?? mate we almost done with this workbook few things more before puttint it on a long pause. mate here is the updated file
https://drive.google.com/file/d/1hNS...ew?usp=sharing i added tourney yearly stats all the way to 2028 i copy formulas from 2022 and i think they are working lastly mate can we get the charts, quesion I want to ask if on the pivot table start column we can search days by only satuday or sundays ?

Change the formula in the Deposit column (cell V2 and down) so that it reads:

Formula:

Please Login or Register  to view this content.

thank you so much mate, if fixed the balance ty ty mate, question is all the inputs going to be the same as before when entering in tournament sheet, give me a bit of how to do it when there is a new category i don't want to mess up any fomulas

Here is another version of the formula from post #107 that uses structured references as arguments for the COUNTIFS function:

Formula:

Please Login or Register  to view this content.

Paste it into cell V2 and then copy down.

It will hopefully keep the formula working when records are added/deleted in tbl_Tournaments.
By the way as long as the record in row 2 stays the same, if any of the column formulas quit working, double click the fill handle in the cell in row 2 of that column. Be sure to check and see that the formula is again working correctly. If not undo the step (Ctrl + z).

I assume that you are talking about the pivot table on the Bankroll sheet, columns AL:BE.
We can try but without being able to put the file in a post it may be hard to explain what I am doing.
I'll go one step per post until I have to go.
Put weekday in cell AC1 (Tournament sheet) and then put the following in cells AC2 and down: =TEXT([Start],"ddd")
Let me know when this is completed down to cell AC16182.

yes mate that pivot table. thats fine with me lets do it done mate it seems it froze8.png done mate7.png

Yes, my computer froze too, but eventually it worked.
Now select a cell in the pivot table and from the PivotTable Analyze tab select Refresh All.
Select Field List
Weekday should now be the last item in the list, drag it into the Filters area
You should now have a drop down in cell AM2 from which you can select the days of the week.

thank you mate, is now working cheers only thing left is the charts if you want we can leave it for tomorrow mate no rush

I won't be around my computer tomorrow.
Perhaps Sunday evening or Monday.

My feeling is that the question asked in post #1 has been answered.

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

mate, how can i make the columns to continued the formula in columns J K and L after adding rows i am showing value! please have a look

when adding rows can it show 0 zero instead of #value!

i can't find the issue, i must made about 10 to 15 changes and nothing correct b7 an b8 on banroll rules
mtt shows amt. 10 and 13 in cells b7 b8s.png