help with rows in tournamen sheet j k and l to reflect on column M base on markup and %

I am confused about the request.
It seems from reading post #1 that $3.38 should be calculated from 1.5, 85 and 85%, however I don't understand how.
Please tell us the value that should be calculated and how it would be done if the calculation was being done manually.

sorry mate, for the not explaining myself accurately okay i am going to be selling a share of my buying lets say i want to sell 85% in row 10 in tournament but i only sell 34% in column m i want the prize i pay after the % i sold im trying to do the math number really tricking my mind bit slow thats where i see the effect of what happen let me see if i can figure it out the math tournament buying is 15 dollars they buy 34%x 1.5 x 66% = 7.65 because of 1.5 markup so my prize would be 15 x 66$ = 9.9 i don't pay markup 1.5 since im selling it not sur if the math is correct sorry mat, im horrible @ math

It would seem that your profit in row 10 could be calculated using: =I10*(1-L10)

According to post #3 it seems that this is your portion of the prize, so it seems to me that the formula should go in column M.
Then again I am not sure what is happening. I feel that it would be better to ask the question in post #6 to someone that is familiar with staking.

total markup to sell 100% out of 15 dollars
sold 34% to backer my percentage i bought to play tournament 66% my buying and percentage 15x66%=9.90 my prize base on my 66% prize 100x66% profit= 66

This formula in cell X10 will yield $66.00: =[Prize]*(1-[@[% sold]])
Question: Does the amount of the buy in have anything to do with the profit?

thank you a bunch mate, well it would have to minus the buying so actually the profit then should be 56.40 if im correct
these calculations is all base of the share i put up for action and depending of how much i sell whatever don't sale then i would have to cover the rest of % to enter the tournament,
mate can my actual buy in calculate automatictly base on the % i sell to reflect my buying amt. instead of me having to do the math for column L i mean my 9.90 dollar buying of my 66% base on the 34% i sold

You're saying that $9.90 is 66% of the $15, as in the tournament name is "Bounty Hunters Sunday Special $15"?

yes mate, this is how i calculate that with out markup this is how i do it on the calculator 15 x 0.66% =9.9 i could be wrong
in the tournament name it would always be the actual buying to enter the tournament like this Bounty Hunters Sunday Special " $15 "

Put the following into cell L9 and copy down:

Formula:

Please Login or Register  to view this content.

thank mate, I will do now
mate, have a question is the math in column L is it also base on the markup because i notice in row 10 im paying 7.50 1/2 of the 15 dollars they actually should be paying a bit more i think base on the 1.5 markup i don't pay markup my buying should be no markup

I didn't include markup.

Note that markup is a percentage which isn't shown in the file.
In your file should the result be 13.70 or 1.30 (15.00 - 13.70)?
In which column should the result be displayed?

This seems to work:

Formula:

Please Login or Register  to view this content.

Note that I feel this lends itself to use the new LET function for the parts which repeat. Unfortunately my 2019 version doesn't support that function, but perhaps another contributor's will.

thank you thank you mat, can you explain a bit more and what to we need to do to fixed it? do i paste that in column L ?ccc.png
mate i though column L row 10 amount was going to be 1.30 but it shows 18.75

Change the values in column I (mark up) to percentages as shown in the file attached.

mate, a question on pivot table. lets say i wanted to get all detail of tournaments stake in staking how do i do it when i right click seem that i won't get a full report of the whole tournamentsff.png these but i want all to the last one

mate, Just notice that the profit need adjustment base of %

mate, I update the column profit for rows 10,11, and 12 base on the % i bought
row 10 10% = my profit 100x10%=10
row 11 40% = my profit 100x40%=40
ro2 12 50% = my profit 100x50%=50

This formula in the Profit column will yield the results shown for rows 8:10 in the file: =(1-[@[% sold]])*[@Prize]
Question: Should buy-in be considered when making the profit calculation?

mate, do i copy this formula down =(1-[@[% sold]])*[@Prize] in column x? <-------- I manualy input it to the last row

As to post #32, The date/time of the deposit/withdrawal has to be put into the Start column.
As to post #33, I assume that you mean minus the buy-in as subtracting the prize would result in all negative profits.
As to post #34, the formula now reads: =(1-[@[% sold]])*[@Prize]-[@[Buy-in]]

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 Posted this in the old thread was looking for the ctrl shift down ctrl enter instructions to paste the formula down and didn't change to this tread sorrry mate

found a glitch on the $54 wow row 30 had a space i got so lucky on this one mate, will try to add rows see if it continues the formula

mate, i need help with columns i j and K so it can continued the formula I was able to to L i try the same way but not working here are my changes

Formula modified such that it should copy down:

Formula:

Please Login or Register  to view this content.

Note that cell L30 displays #Value!, but if you remove the extra space after $54 in cell G30 then L30 will show a dollar amount.
Note that cell L34 displays #Value! because there is nothing in cell G34. So the #Value! errors in column L indicate where corrections are needed in column G which is a good way to troubleshoot.
Note that this is in response to the 8:36 post before the "found a glitch..." statement was added. I can not keep up when you keep making new requests before I have a chance to answer the previous one.

The file in post #38 is different.
You had been making requests about the Tournament & Cash file.

sorry mate, i choose the wrong date will upload mate, i need help with columns i j and K so it can continued the formula the percentages
mate, can you make the new date in purple the color in b22 across like the staking for new dates

I am not sure that I understand the request.
I changed the formatting rule for purple to include A7:AI35.
I changed the formula in column C so that deposits and withdrawals are not counted as new days:

Formula:

Please Login or Register  to view this content.

If deposits and withdrawals should be counted as new dates let us know and the formula can be reset as it was before.
I also removed the "pm" which had been typed into cells D23 on the Tournament sheet and A4 on the Bankroll sheet, making that text instead of a date/time.

thank you mate, will applied code now i was testing the cash section not sure the prize or results are calculating with the total bankroll or running balancet.png

in this photo i would like the numbers and percentage to continue when creating new row because the format changes to date like in the picture

"can you make the new date in purple the color in b22 across like the staking for new dates"
You want the Day to change even though there is no tournament played on September 17th?
"can we fixed the #value! in these cells I5, I6, I7 I14 and Q3,Q4 and Q5"
I assume that you mean on the Bankroll sheet
I5:I7 fixed by inclosing formula in IFERROR
I14 fixed by changing formulas in Buy-in and Profit column on Tournament sheet.
Q3:Q5 seem to be calculating correctly.
As to post #45, I typed in 1.5% 44% in the markup and % sold columns with no problem.
When I tryped 90 into the percent column it displayed a date, I changed the formatting (Ctrl + 1) to number and then added another column which displayed correctly.

mate, can we work on deposits & Balances tournament & Cash profit by site i made changes
in cell i30 i have a dropdown list for tourneys & cash would like to get stats for either or is showing now the tourney stat for GG and Partypoker
in H20 its only showing tournaments amt. can we combine tourney & cash amts
will upload the updated workbook
mate regarding post#45 i get 1/1/1900 when typing 1.5 or 90 or 50% this is whta i get dd.png

I really don't understand why the formatting keeps changing.
I changed the formatting in those columns to custom:
Column I: 0.00%
Column J: 0
Column K: 0%
The formatting held when I added row 34.
Formula for H19:H28 is: =ROUND(SUM(SUMIFS(tbl_Tournaments[Profit],tbl_Tournaments[Network],F19),SUMIFS(tbl_CashGames835[Profit],tbl_CashGames835[Site],F19)),2)
I will look into the request about the drop down later. Please do not make any changes or other requests until I/someone has a chance to reply to that.

As to the drop down:
1. Formula for Profit: =IF(I$30="Tourney",SUMIFS(tbl_Tournaments[Profit],tbl_Tournaments[Network],F32),SUMIFS(tbl_CashGames835[Profit],tbl_CashGames835[Site],F32))
2. Formula for Played: =IF(I$30="Tourney",COUNTIFS(tbl_Tournaments[Network],F32),COUNTIFS(tbl_CashGames835[Site],F32))
3. Formula for Rebuys: =IF(I$30="Tourney",SUMIFS(tbl_Tournaments[Rebuy],tbl_Tournaments[Network],F32),SUMIFS(tbl_CashGames835[Rebuys],tbl_CashGames835[Site],F32))

mate, can we fixed the total bankroll and running balance from cash to work along with tournaments like in the staking
i don't see it changing after adding prize/results on cash i12 in bankroll can we add the cash to add up too ty

Cell I12 on the Cash sheet is blank.
Cell H1 on the Tournament sheet displays total bankroll using:

Formula:

Please Login or Register  to view this content.

mate i mean i 12 in bankroll tourneys stake can we add the cash to add up it has 24 now would the running balance in column Y in tounament should that be same as 2875.41 depending on wins or losses from either cash or tournaments
not sure why in bankroll shows 88.29 on h20 and on cash bankroll sheet shows 1088.29

Paste the following in cell I12:

Formula:

Please Login or Register  to view this content.

Paste the following into cell H19 and drag down:

Formula:

Please Login or Register  to view this content.

thank you mate, mate can the bankroll total be adjusted with the othe ones its showing 2875.41 cash bankroll sheet & tournament sheet running balance show 1275.41
mate, can dropdown list in bankroll cell AE also displayed individual stats for cash

mate, i also added a row row 31 not sure why is showing #value! on column L after checking notice is because the amt is between the text can it be work around

The running total on the Tournament sheet doesn't include the profit/loss from the Cash sheet.
You could start putting the cash game records into tbl_Tournaments the way that deposits/withdrawals are done.
If you want cell D1 on the Cash Bankroll sheet to show the same amount as cell H1 on the Bankroll sheet then use: =Bankroll!H1
As the formula in the Buy-in column is already complicated I suggest manually editing the tournament name in row 31 to read: WSOP Online: "GGMaasters Asia kick-Off $25"

thank you mate, let me understand so we going put cash into tournament sheet ? don't want to mess things up
cash games has different tab names okay let me try that, okay mate done,
but the balance is still off H1 4536.78 cash bankroll sheet D1 4,536.78 running balance is $3,020.50
is there no other way around it not to have to input cash records in two sheets? what you sugest is best to do think about it if you were going to be the one keeping track and playing at the same time think the big picture mate you depositing to all these sites lets say 1k on each platform then you playing tournaments & cash games keeping record of everything going on from deposits and withdrawals

Personally, I don't understand why having different amounts is a problem.
The running balance on the tournament sheet just shows results of tournaments.
Similarly, the running balance on the cash sheet just shows results of cash games.
The table showing deposits/withdrawals is just meant to do that.
The total bankroll gets amounts from the three tables.
So, there is a way to see the big picture (total bankroll) and also a way to see how each element is working.
But, in my opinion, if you want the running total to show the same as the total bankroll then all of the information would have to be in tbl_Tournaments.

The way that it should work is the way that makes it meaningful to you. I am just saying that in my opinion if you need the running total on the Tournament sheet to be the same as the total bankroll then all cash games and transactions, deposits/withdrawals, would have to be recorded in tbl_Tournaments.
You may want to see if someone else has a different idea about how to synchronize the running total and total bankroll. If so then I suggest that you close this thread and open a new one in the Excel General forum so that contributors who are knowledgeable about VBA will also participate.

thank you mate, thats fine lets keep working on the changes that need adjustment i would see how things work the way you have it and input more tourneys see everything,
can we adjust the dropdown list mate on AE in bankrolll sheet and cash bankroll column P:3 TO P9
mate you ever played poker?
mate, is the amt. in g33 in bankroll correct for tournaments amt. show 433.37 and on buyings 139.47 its also showing a partypoker tounament which there is none in tournament sheet mate please see these can total of both be in y 4 cash and tournament amt

How do you want to adjust the dropdown in cell AE2?
P3:P9 are populated by formula, however I don't know what the difference between ROI and Buy-in ROI is.
433.37 seems to be correct for GG profit, however the buy-ins for GG total is 126.63

Selecting the link in post #65 results in:
"Google Sheets
You need access
Request access, or switch to an account with access.
As to post #66 please look at row 31 on the Tournaments sheet.

thank you mate, here is the workbook to the link in post#65

mate in this workbook is also the 2022 results can you believe this guy made 537k un real hopefully i run that good when is my time he knows what his doing you can see all the stats and even the wins on column I his a treamer too

disregard mate was looking on the wrong side

From the file attached to post #70 seems the buy-in ROI in cell P7 could be: =P3/I7
ROI in cell P6 could be: =P3/'Bankroll Rules'!B3

thank you mate, mate, can we add the rebuys in cash sheet to add up in cell J7 for buyins and rebuys to add up in column x in tournament i have 1 rebuy in M15 the rebuy amt would be the 15 dollars total entry amt. no share i paid the whole amt. to reflect in x15 same for bottom rows be back later mate, have a dr. appointment

I feel that you are describing a different file from Tournament & Cash file attached to post #71 as column M on the Tournament sheet is for Prize and column M on the Cash sheet is for length.
Guessing that cell J7 is on the Cash Bankroll sheet, if so the formula could be: =SUMPRODUCT((tbl_CashGames835[Buy-in])*(tbl_CashGames835[Rebuys]+1))

thank you mate, i am describing this file
rebuy M15 to add whole amt. to column x 1 rebuy = 15 dollars no markup or % tournamnts sheet
cash bankroll sheet to add rebuy amt. to buyins in J7 is showing 600 now not adding the rebuy for 500

Tournament cell X15:

Formula:

Please Login or Register  to view this content.

Cash Bankroll cell J7: =SUMPRODUCT((tbl_CashGames835[Buy-in])*(tbl_CashGames835[Rebuys]+1))

thank you mate, its working now the way it should but why on column x only work for 1 rebuy not for more than 1 no rebuy amt. is 1.30 1 rebuy amt 16.30 but when i add 2 or 3 rebuys it stays the same.
mate how come the baance is not the same on cell H20 in bankroll is 887.98 and on ending br P9 it shows 4887.96 its off by 2 cents ? if you add 4887.96+ 112.02 =4999.98
int Tournament column f not sure when did the names of category disapear can we have it like in the staking workbook speeed racer bounty hunters or turbo hypers gets me confused alot with the other column B at times

I am away from my computer for a few days.
Will look into this when I can.

As to rebuys, the formula for column X could be:

Formula:

Please Login or Register  to view this content.

mate, can we make column B in tournament count only games in cell I12 in Tourneys Stake:/bankroll in the balance why is it still off by .02
4687.96 + 112.02 = 4799.98 an not 5k
mate, would like column F in tournaments to give out the catagory for the games would the table in the staking work if i copy exactly as it is? let me know i can get started on that updated workbook

mate, I was able to clone the staking workbook i think i got the category fixed will reupload

"can we make column B in tournament count only games in cell I12 in Tourneys Stake"
Does this mean that you only want cell I12 to count the rows that have GG in column F?
Is the expected value 18?
As to the balance:
1. Change the formula for the Profit column (Tournament sheet):

Formula:

Please Login or Register  to view this content.

2. Change the formula for Total Bankroll (Bankroll sheet): =SUM(INDEX(tbl_Tournaments[Balance],AGGREGATE(14,6,(ROW(tbl_Tournaments[Balance])-ROW(tbl_Tournaments[#Headers])),1)))
Will have to look at the rest later. Please do not add to the requests until I have a chance to respond.

Formula for cell I12: =SUMPRODUCT((tbl_Tournaments[Network]<>"")*(tbl_Tournaments[Network]<>"deposit")*(tbl_Tournaments[Network]<>"withdrawal"))

thank you mate, is that including the cash i have one in cashi will add the cash name to the Network will upload now


wow mate, thank you a bunch disregard i think is already doing it after adding the cash and inputing cash for network it keeps the count going. please see changes

Re post #87: off the top of my head, I can't think of a way to do that using formulas.
I feel that this thread's initial question has been answered. So, if you would like to get opinions from VBA experts, I suggest closing this thread.

That is because the column header for column B on the Tournaments sheet got changed from Category to Network.

mate, just notice that file in post#90 only shows the number of games played but not the other stats below see picture

mate, seem to be working fine not ty