Help with total bankroll balances to match tournament and cash, along with other stats

Hi, I would like my total bankroll balances to match base on games played in either Tournaments or Cash games.
in the cash Bankroll sheets there are stats, I would like help with, starting with w8 and w9

please feel free to ask anyquestions

As to the tournament formulas, I feel that they basically just need the references to your C: drive removed and the tbl references left.
As to the Cash by Stake:
1. Profit: =SUMIFS(tbl_CashGames835[Profit],tbl_CashGames835[Stake],B18)
2. Hours: =SUMIFS(tbl_CashGames835[Length],tbl_CashGames835[Stake],B18)
I feel that we need an explanation in words as to how the 1.94 in cell W8 is being calculated.

Thank you mate
Chips In Play. After winning a big pot, or a lot of small ones, enter the amount of chips you have in front of you at the table. The Have to Leave Table field will let you know if it's time for you to leave the table or if you can keep playing. Next to that field is a summary that tells you your current bankroll including the chips in play and what percent of your bankroll is in play.

33.png

Mate, which Tournament formulas? not sure if we are looking at a different workbook, or which sheet? let me know how i correct it? I been looking in the cash section I do see this for total deposits & balances
=SUMIFS('C:\Users\Administrator\Desktop\Tournaments in Pivot table 6-1-23 (west123) (1).xlsb'!tbl_AllTransacations[Amount],'C:\Users\Administrator\Desktop\Tournaments in Pivot table 6-1-23 (west123) (1).xlsb'!tbl_AllTransacations[Amount],">0",'C:\Users\Administrator\Desktop\Tournaments in Pivot table 6-1-23 (west123) (1).xlsb'!tbl_AllTransacations[Casino],B4)

In the file attached to post #2 I have already changed the formulas in the total deposits & balances section, cells C4:E13.
If your first post referred to a different group of cells then please specify which cells.
From the screenshot attached to post #3 I take it that cell W8 is manually entered. Is that correct?
Since the bankroll is 30,000 then cell W9 should change to yes when cell W8 is >= 3,000. Is that correct?

The formulas for the cash profit by stake section on the cash bankroll sheet in cells C18:E43 are in post #2.
If you want formulas for another section of the worksheet, please tell us specifically which cells.
Here is a file with the formula for W9: =IF(W8>=D1*'Bankroll Rules'!B5,"Yes","No")

Thank you mate,
here is the updated workbook. I paste the formulas from post #2 to the cash section
can we get the Total bankroll as we had before in the old version. same for tournaments and cash. like that we can get the amounts in Session Stats and Important Info in cash sheet column s and w, to calculate correctly base on the bankroll, not sure is doing that.

Total bankroll: =Info!Z3
# of sessions: =SUM(COUNTA(tbl_Tournaments[Tournaments]),COUNTA(tbl_CashGames835[Site]))
Average profit: =SUM(SUM(tbl_Tournaments[Profit]),SUM(tbl_CashGames835[Profit]))/S3
Average length: =SUM(SUM(tbl_Tournaments[Duration]),SUM(tbl_CashGames835[Length]))/S3
Average $/hr: =S4/(S6*24)
I feel we'll need more information about average roi and average # hands.

Thank you, Mate
average of # of hands played by session average roi over all sessions. will look in site see if i find anything else on roi and # of hands played
okay, Big Blinds
The amount of the big blind for the tables you played. if you're playing $0.2/$0.05 stakes enter $0.05
# Hands
The number of hands you played in that session. On PokerStars there's a tab marked Stats where the chat box and note box is. Click the Reset button before you start your session then at the end of the session, just see how many hands you played.


33.png

It seems to me that number of hands played comes from column H on the cash sheet, so it needs to be manually filled in that column.
I don't see anything about hands on the tournament sheet.
As to ROI: =SUM(SUM(tbl_Tournaments[Profit]),SUM(tbl_CashGames835[Profit]))/SUM(SUM(tbl_Tournaments[Buy-in]),SUM(tbl_CashGames835[Buy-in]))
As to big blinds: =INDEX(Worksheet!B5:B18,MATCH(1,FREQUENCY(D1+0.0001,Worksheet!G5:G17),0))
Note that the big blinds formula is windknife's formula for current stakes redirected to column B on the worksheet sheet.

We can just pull data from tbl_CashGames835 if that is what you want, will make things easier actually, but we need to know specifically which sheet to reference
This is also one reason that we ask for manually calculated results as they give a better indication of what the formulas should reference.
I wanted to make sure that windknife got credit for the formula, as I didn't come up with it.
I think that I have changed the formulas in the cells that need changing (highlighted yellow).

For total bankroll use: =Info!Z3

Cell Z3 on the Info sheet: =SUM(SUM(tbl_Tournaments[Profit]),SUM(tbl_AllTransacations[Amount]))
Cell D1 on the cash bankroll sheet: =Info!Z3

Thank you mate,
will input now cheers
nice ty ty mate, can we continued with these stats in the picture

ref.png

the ones with #REF~

Are they based on the data in just the cash sheet or based on data in both the cash and tournament sheets?

Here is the file with the formulas applied.

Happy morning mate, thank you
will download now, Nice mate looking good. can we continued with the graph cash yearly stats & cash profit by game
after that I will test everything to see how it works and see where it may need a bit of change. ty mate

Mate, I am doing all my inputs in the stakeing workbook. just have a question about the total bankroll and GG balance it don't match. where can I check see if I find the issue. I have spend lots of time trying to find the glitch but I can't even check entries in tournaments have GG in network but still not matching. see picture please.

There might be an entry for 4.32 where GG has an extra space.
Select the filter button in the Network cell and see if GG shows more than once which would indicate that it is entered differently somewhere.
Other than that, I need to see the workbook.

Thank you mate, I was able to find it is correct now.

Mate, can we continued with, formulas for Cash Game by Number of Tables, Cash Yearly Stats and Cash Profit by Game

Cell S7: =AVERAGE(tbl_CashGames835['#Hands])
Cell S8: =AVERAGE(tbl_CashGames835[BB/100])
Both display #DIV/0! because the #Hands and Rebuys columns on the Cash sheet do not have any values in them.
I feel that before starting on the sections mentioned in post #27 we need to see the updated file as implied from post #24.

Thank you mate,
okay mate will, input formulas and will upload. ty

made this is the Stakeing workbook mention in post# 24

Done.
In the case of Cash Yearly Stats and Cash Profit by Game, done again, because there were formulas there but the references were broken when the layout was changed.

When I put numbers in cells H3:H4 on the cash sheet then the P3:P4 show a value.
I also see a value in S7:S8 on the cash bankroll sheet.
Please refer to the attached file or upload a new file so we can see what is happening.

Mate, can we delete the values or formulas in it and make it work like the workbook in post#31 the original google workbook?
tell me please do i delete and reupload?

The number format may be what you mean.
Perhaps in google put $0.04 in the big blind, 10 in the hands and $15.43 in the profit and see if the result in BB/100 agrees with the one in cell P3 this file.

Thank you mate,
let me input that. will download new file

Mate, not the same here is a picture

xx.png

The profit in J12 is showing $0.85 not $15.43.
When I type .85 into cell L3 of the file attached to post #37 then cell P3 displays 106.25 just like in cell N12 in the screenshot in post #38.

Cell D1 on the cash bankroll sheet: =INDEX(tbl_CashGames835[Bankroll],1)

Happy morning mate, Thank you
will input formula
mate, that changes my total, from bankroll. I ment Bankroll column Q3 in cash to show the balance from D1

Mate, can we get the graph in cash section to agree with cash table.
and in Bankroll, Can we get Cash Games stats, Largest Cash wins, Tourney Yearly stats, and Satellites, Sit n Go and spin & Gos formulas I also have dropdown list to choose which site they correstpond to.
here is the updated workbook. I update the Tournament sheet only think that work is having a black background seems to work when adding rows.
would like some formatting. in Tournament columns T, U, and V red for minus and green for profit Balance red minus, Rank column M green for anything under 10 place.
Purple for every new day across the row

Should cell Q3 on the cash sheet and cell D1 on the cash bankroll sheet both show the total of the profit from the tournament sheet, the cash sheet and the amount in the transaction table?

Mate, I input the balances starting with the Bankroll on Q4 for $10.05, adding Q5 profit and Q6 profit which then it would need to reflect in Bankroll Total and Tournament Running Balance. Now Running balance is showing -$90.02 which may be the correct number to be in Total bankroll and Q4 in cash adding the profits to Running balance and total bankroll.

The best that I understand is that the total bankroll in cell D1 on the cash bankroll sheet is -90.02 which comes from cell V52 on the tournament sheet.
If that is the case then paste the following formula into cell D1:

Formula:

Please Login or Register  to view this content.

Mate, would that be the same for the Tournament bankroll?
mate I added a row to workbook in post# 45 not sure if you saw that in cash sheet where I would aslo want the total to be the same

It seems to me that the cash sheet balance is backwards.
The balance for the earliest date (4/23/2023) is 11.44.
The next date (5/15/2023) shows a profit of .85, however the balance drops to 10.90
In one of the earlier versions the balance formula was set total the profits from the bottom to the top.
I am confused as to why that needed to be changed.
As to the total bankrolls they can be set to show the same amount if that is what you want.

Happy morning mate, Thank you
okay I reverse the days 4/23/2023 on top. This is how it works on my poker client I have 1 total which that can be used for tournaments or cash or anything else. I would want to have a total balance for all poker sites, that when I play tournament or cash the Total balance from all sites is acurate along with the cash. So please help me how is this suppose to be set to show. My running balance now in this workbook is. -$90.02 on tournament sheet, and in bankroll is $10.05 and in the cash section I added a row Q4 to display i would think the same balance maybe from Running Bal. and Total bankroll ? not sure of what is the best way to set this to be all in one.may

maybe we need to add a column to cash to give break down from cash sessions and losses but the total bank roll it adjust from when playing tournaments as well when playing cash. Give me your opion on how would be best have all with main bankroll from deposits from all sites.

Two observations:
1. The starting roll was not included in the balance (tournament sheet) because the wrong date (7/17/2023) was in cell D2
2. The most recent bankroll (cash sheet) is 11.44
3. If I9 is supposed to be the same as D1 (cash bankroll sheet)then past =D1 into cell I9
I'll be away from my computer for the next few days.

Cell V52 on the Tournament sheet shows -90.02, which is not correct as the Starting Roll was not included (see post #51)
Cell Q4 on the Cash sheet shows 10.05 which again I don't think is correct because it doesn't include the profit form rows 5:6
That said if you want to combine the above two values in cell D1 on the Cash Bankroll sheet use the following formula:

Formula:

Please Login or Register  to view this content.

The total displayed is -79.97. If that isn't what it should show then tell us the correct total and from which cells the total should be calculated.

Thank you mate,
okay I just input the starting roll. seems to be all matching. Only can we have the column Q4 to calculate base on the inputs in the cash sheet?
After checking the cash bankroll is now $20.10 will try to check why is showing that amt.

"When I input a buying in cash lets say $1000 ($1,000) minus it won't reflect in the total bankroll balances"
Please attach a file to show us what this means and then tell us what the expected value is for cell D1 on the Cash Bankroll sheet.

Happy morning mate,
Here is the workbook, I manualy input the Balance in Q4 that is currently in Tournament and Bankroll sheets. lets say I play 2 session of cash
1st session I lost $1000
2nd session I lost $10
Then my Balance would be Zero in Tournament Sheet, Bankroll and Cash Bankroll. showing 0 zero as well in GG Deposits and Balances column D5
can formula be adjusted for all other poker sites

Thank You for giving us a balance result with which to work, makes things easier.
I feel that the following would be a workable strategy:
1. Populate the Bankroll column in tbl_CashGames835 using: =ROUND(SUM(INDEX(tbl_CashGames835[[#All],[Bankroll]],ROW()-3),[@Profit]),2)
2. Populate cell D1 on the Cash Bankroll sheet using:

Formula:

Please Login or Register  to view this content.

3. Cells I9 (Cash Bankroll sheet) and H1 (Bankroll sheet) reference cell D1 on the Cash Bankroll sheet.

Thank you, and your welcome mate, Nice
will donwload and see results ty ty.
Mate, can the Running balance in Tournament be Zero too and column H20 show the balance in 0 in Bankroll and for Cash Bankroll would be D5
Not sure why still shows Sugg Buy in $10.10 in Bankroll sheet if Balance is 0

You could add a cash category in tbl_Tournaments and then modified the formula in the deposit column to read:

Formula:

Please Login or Register  to view this content.

Mate, I created the new cash category and changed the formula in deposit. what is that suppose to do? i don't see any changes
here is the workbook

not sure I did it right shows TRUE in deposit after inputing the formula

Please look at rows 2:3 on the Tournament sheet of the file attached to post #62.
In order to work the exact Date Time from tbl_CashGames835 will need to be entered in the Start column of tbl_Tournaments.
The amount from the Profit column in tbl_CashGames835 will then be displayed in the Deposit column in tbl_Tournaments.

To avoid confusion, put the profit for cash games in another column (X) on the Tournament sheet.
Changed formula in Balance column (Tournament sheet): =ROUND(SUM(INDEX(tbl_Tournaments[[#All],[Balance]],ROW()-1),[@Profit],[@Deposit],[@Cash]),2)
H20: =ROUND(SUM(SUMIFS(tbl_AllTransacations[Amount],tbl_AllTransacations[Site],F20),SUMIFS(tbl_Tournaments[Profit],tbl_Tournaments[Network],F20),SUMIFS(tbl_CashGames835[Profit],tbl_CashGames835[Site],F20)),2)
I don't feel that H20 should show zero as deposits to GG are 135.07, tournament profits to GG are 931.47 and cash losses to GG are 1,010.00 which would be 56.54.

Graphs are now per tournament and cash.
There is already a profit column in tbl_CashGames835 or are you asking about somewhere else?

Column X on the Tournament sheet displays the profit from cash games.

U54 shows the profit for that individual tournament, unless tbl_Tournaments has been changed since post #72.
Column X feeds into the overall balance in column V, so showing a running total of the cash games in column X will make column V display an incorrect overall running total.
If you want just a column to show a running to of the cash games, like column Q on the Cash sheet, then it will need to be added.

thank you mate, yes please lets add it. what do i do, tell me how to di it. want to see how that works
mate, how can I remove row 2 in the cash sheet without formulas changing. when i delete graph line goes up

Adding a running total column:
1. Select a cell in column Y > Press the Ctrl and space bar keys > Press the Ctrl, Shift and + keys
2. Put a column header like Cash Total in cell Y1
3. Put a formula like the following in cell Y2: =ROUND(SUM(INDEX(tbl_Tournaments[[#All],[Cash Total]],ROW()-1),[@Cash]),2)
4. Double click the fill handle to copy the formula down to cell Y54
As for removing row 2, please upload a file after completing the steps above.

Yes, you have correctly set up a running total column.
As to removing row 2 from the tbl_CashGames835, you also need to remove the row that included that cash game from tbl_Tournaments.
In this case row 2 in tbl_Tournaments had the data from the now deleted row 2 in tbl_CashGames835 so I deleted that also. (I hope that makes sense).
Since there is now only one data point it will be hard to find in the graph on the Cash Bankroll sheet.

conditional formatting of rank under ten is: Cell Value <= 10
Please define "new days".

Conditional formatting rule: =$C2=MAX($C$2:$C$53)

Mate, I have a workbook of a section I would like to apply to the Tournament sheet. rows 2:5
this is the workbook. please let me know if thats possible

Conditional formatting rule: =$C2<>$C1

I feel that placing rows 2:5 at the top of tbl_Tournaments will have a negative effect on the column formulas in the table.
Formulas in a table will copy automatically to new rows as long they are included in all existing rows. So if you add four rows with different formulas at the top of the table then the formulas in rows below would have to be manually copied down each time a new row of data is added.
I would put the information in rows 2:5 elsewhere, which in my mind is not part of the original question.

Please start a new thread for the new query with a suitable title. This thread should be marked as solved.

Please choose Thread Tools from the menu link above and mark this thread as SOLVED.