Worksheet summary data for catagories to calculate amounts.

Hi,
need help get with entries to calculate values in each of its catagories, along with other stats.
here is the workbook. cheers.

Mate, can we addjust the Rake in column M in the Tournaments to be correct in the profit column
Rake is part of the buyin, lets say the tournament cost is $1+$0.8 the rake would be the $0.8 its what the site charges for the tournament.
Rake, and $/Hr need formulas, I input formula for column U for duration not sure if its correct.
also would like to know how is column AA would work as for deposits? having the Transaction section. Can the amount in column AA reflect when making a deposit in the Transaction section? so we can still have the Running balance.

How does Rake effect profit? Should it be subtracted from the profit or something else?
So for example, should cell Y3 display -$4.40 or something else?

Then the formula for the Profit column could be: =[@Prize]-[@[Buy-in]]*([@Rebuy]+1)-[@Rake]

I input formula for column U for duration not sure if its correct.

I don't see a formula in column U for duration, only drop downs sourced to cells G3:G17 on the info sheet.
Assuming that you want a formula that subtracts Start, column E, from End Time, column K.
It appears that if there is no Start then the duration is the End Time.
Is that correct?

Here is the file with your formula, I just changed K2 and E2 to [@[End Time]] and [@Start] to make it a little easier to understand what the formula is doing.
In cell U2 it shows 21:09 because there is no start time in cell E2.
Is that what you want?
BTW, you said that you don't know Excel functions, but you knew MOD. I think you may be sandbagging.

Immm Sandbagging huh I think i heard that in some kind of card games Spades i think

Mate, what would be the formula for $/Hr column X

The current formula in column X will work provided that the formula in the Duration column is modified to read: =IF([@[End Time]]="","",MOD([@[End Time]],1)+(MOD([@[End Time]],1)<MOD([@Start],1))-MOD([@Start],1))

Thank you mate, will see the change, but I aint sandbangging... cheerss
LOL thats a funny word I will add to my list.

Nice mate thank you. Also have question about column AB is that needed? also can we make the Transaction when making deposit to show in AA to keep the Total balance running.

As to column AB, do you need to the stake amounts in this workbook? If not then you can delete that column.
As to column AA, I feel that it would be more efficient to put the deposits in that column and transfer the information to the Transactions section than the other way around.

If I understand correctly then the formula in cell E9 could be: =SUM(Table1[Deposit])

Why is there a difference between the amounts in column AA and the amounts in column D?

If you reload to deposits from column D to column AA then column AA would display $110 for each row having the date Mar 26.
You may need to add another column to Table1 for deposits.

You said that the amounts in column AA are stakes, so you could add another COLUMN (such as column AC which is not being used) for just deposits.

Then I suggest removing the stake amounts in column AA and then entering the deposit amounts corresponding to the dates in column E.

can it be automatictly when doing from within all transaction column D?
It would be great that it just reloads the balance in Red when the balance is in zero then having to input the amounts in column AA

If you want to keep a "Total balance running" then I assume the deposits in column D would have to be copied into column AA on the date they were made.
My thinking of what would happen is as follows: $110 was deposited on March 26th. That date shows up six times in column E. The $110 would get added to column AA six times.
But perhaps one of the other contributors at this site can figure out how to do what you want.

Not at all sure that I understand, however perhaps the following yields the expected results in cells V2 and down: =IF(OR([@Entrants]<20,[@Rank]>10),"",[@Rank])

Thank you mate, let me apply it.
Its showing the actual value of Rank Column lets say the Rank was 10 or 9, 8, 7, down to 1 would be 1
anything that raks under 10 would be 1 in Final Table but excluding Sit & GOs with games of 1 table which is 10 players

Then try: =IF(OR([@Entrants]<20,[@Rank]>10),"",1)
If that does not work, then please upload your file with some examples of what you want.

Happy morning mate, cheers
I will try it now,
Mate you good thats the formula mate cheers.
Can we please try to complete the B:AI in allResults
starting from Dailey to Single Table SNG AVg.

I'll let you to tell me the formula for cell I4 (Total Time).
If Total Tournament Profit is: =SUM(Table1[Profit])
The Total Time would be: =SUM(Table1[??])

Don't see a "Total Time" column in Table 1, however there is a column that displays the total time, just has a different name.

Duration ? let me try that. Thats it mate but only 22:32 hrs? don't seem correct
if tournament in row 5 its duration its 10hrs and row 39 11:19

dura.png

That's a formatting issue.
The formatting for cell I4 should be [h]:mm as shown in the attached file.
This is the kind of question (i.e. how do I format a cell so that it displays correctly?) that this site is designed to answer.

Sorry mate, I would of never know its the format but i will check now
I get 2038:32

Right, if you highlight U2:U149 on the Tournaments sheet, that's the total shown in the bottom right corner of the worksheet.

So if $/Tournament is equal to I3/I5 then $/Hr should be ??

Number of hours is in I4 and $ is in I3 so $/Hr is =I3/(I4*24)
Note that the *24 converts the time in cell I4 to a whole number with a decimal part so that it will be useful for making the calculation.

We can work on those statistics that you explain in the thread, or that are somewhat obvious from the associated name.
I remember that you said ROI was the Profit/Deposits so cell I6: =I3/SUM(tbl_AllTransacations[Amount])
Based on that, I feel that cell I7 could be: =I3/SUMPRODUCT((Table1[Buy-in])*(Table1[Rebuy]+1))
It seems that the formula in L9 could be: =SUM(Table1[Rebuy])

Okay cool thanks Mate, good memory, if you remember that because i don't. thank you will apply
and download
Nice mate i see is coming along. In Todays profit and month profit is #value can it show Zero or is that an error for E3 and E5

Todays profit: =SUMPRODUCT((Table1[Profit])*(INT(Table1[End Time])=TODAY()))
month profit: =SUMPRODUCT((Table1[Profit])*(INT(Table1[End Time])>=EOMONTH(TODAY(),-1)+1)*(INT(Table1[End Time])<=EOMONTH(TODAY(),0)))

Head-Up Win %: =COUNT(Table1[WINS])/COUNTA(Table1[Tournaments])
Finish Top 10% could involve another column on the Tournaments sheet: =1-[@Rank]/[@Entrants]
And then the formula in O3 on the allResults sheet could be: =COUNTIFS(Table1[Finish %],">=0.9")
Question, should Finish High 20% include the top 10% finishes?

happy morning mate,. Thank you. just saw that the Admin said was okay to continued with other thread. its okay to complied is best this way. thank you mate. will see changes. cheersss

yes mate, please let try to finish as much we can.

Based on your answer to post #49 here is the file with formulas in O4:O7

thank you mate, let me download and see the changes

nice, mate ty do i just format to %? the number in column P?
also can we continue to Buy-ins along with Sugg. Buy-in:

Is this supposed to be a % out of the 148 total tournaments?

Would be nice to have those details before working on formulas.

As to Buy-ins, please explain what is meant by Suggested BI.

Hi, mate
okay, Suggested BI. would be the amount I am suppose to buy-in depending on bankroll.
allowable BI thru Max Bis Avail. all go along with the banrkoll rules and worksheet in post #43
here in this picture are the rules that go along with the buy-ins

555.png

in post #43 workbook it shows how it works. under tabs "Bankroll Rules and Worksheet"

Based on my understanding of the bankroll rules I filled in cells S3, S5:S7 and S9.
As there is no mention of allowable buy-in in either the rules nor the Info sheet I omitted S4 and S8.

thank you mate, ty. I upload the workbooks because maybe you can know how things work about the formulas or how is set up.
let me look mate longest they do the same funcions as the model we will be fine

not sure is correct. suggested Bi should not be as high base on my 300 dollar bankroll
if you want lets skip that for now and move on to winning % and single table Sng Avg or ITM% overall stats

Info!A4:B10 is the closest thing in the file that looks like the screenshot in post #58 and the bankroll given in cell B4 on the Info sheet is $51,940.

Copied the actual bankroll rules from the other workbook into columns X:AD on the Info sheet.
Selected cells in the Buy-ins section reference those rules.

Mate, can the sugg. buyin: show in column 1 column R also is there a way to add other stats Allowable BI Average BI allow Bis Avail.

mate, the Suggested Bi, should work base on the bankroll balance meaning depending of how big the balance is the suggested Bi adjust to either buyin into bigger tournaments or either i buyin to lower tourneys depending of the bankroll if it grows then suggested buyin increase

Suggested buy-in is now in cell R1.
Suggested buy-in is 1% of the bankroll as was done in the screenshot of post #58.
If you want to change the percentage then change the value in cell Y4 on the Info sheet.
Also total bankroll in cell M1 on the allResults sheet refers to cell Y3 on the Info sheet.
I don't know what Allowable BI means. Please explain in the same way as you explained suggested buy-in.

Here is my attempt to translate the explanations into Excel formulas.

let me see, thank you mate cheers
cool mate, average Bi would be the amount i mostly buy in for.
can we do the ITM % 1,2,3 4-10 MTT FT

Put the following into cell S6: =AVERAGE(Table1[Buy-in])
As you did in post #67 explain ITM % 1,2,3 4-10 MTT FT

Based on post #72 it seems that the MTT FT calculation in cell L8 on the allResults sheet is different than that in the FT column on the Tournaments sheet which only considers records when there are more than 20 entrants.

The formulas in cells L4:L7 have been changed to count only the tournaments with 20 or more entrants.
Sit N GO JPT is a tournament not a category (see cell AH12)
As to post #75 please tell us the expected value of each cell in that section based on the data on the Tournaments sheet.
For example, should cell T3 display April 5 23 (Mon)?

As to post #77 for each formula in cells AH13:AH17 change [category] to [tournaments]
As to post #78, changes made.
Note that there is nothing in cell N3, unless the allResults sheet has been reformatted, but balance is displayed in cell M1 which is merged (bad habbit) with cell N1.

thank u mate,
cell n1 will download and see whichone is it. so what we need to do to correct that bad habbit
will see
my bad mate I was reffering to m1,
mate what u mean bad habbit with m1? please explain a bit.
can you write the formula for Day count T5
In todays profit. can it be something that give zero 0 instead of Value!
Mate can we also have the N1 adjust calculate when I make a deposit in deposit section.

The best thing would be to size cell M1 to display the value.
If the value of cell M1 won't display correctly in that cell then it would be preferable to select the M1:N1 cells, press the Ctrl and 1 keys, choose Alignment and then select Center Across Selection.
From Post #75: "the count of days to calculate when i input new date"
Count the number of days to/from what?
Put the following into cell AH15: =IFERROR(SUMPRODUCT((Table1[Profit])*(Table1[Tournaments]=AF11)*(DATEVALUE(LEFT(Table1[End Time],SEARCH(",",Table1[End Time])-1))=TODAY())),0)
Are you asking about adjusting balance based on column AB or column D on the Tournaments sheet?
If the former, then it already should reflect deposits, if the latter, then I don't know how to make it work.

Yes for the other Today's Profit formulas put IFERROR( in front of SUMPRODUCT and ,0) after TODAY()))
I have said on numerous occasions that I don't feel balance can be updated from the transaction section.
To count distinct days:
1. Add a column AD to the Tournaments sheet using: =IF(ROW()=2,1,IF(INT(K2)<>INT(K1),SUM(AD1,1),AD1))
2. Populate cell T5 on the allResults sheet using: =MAX(Table1[Days])

Mate,
Please see updated workbook. I made a change T9 ending BR to this =SUM(Table1[Balance]) but I input lets say a big win
and my Buy-ins rules don't calculate to the new Suggested Bi along with other rules.
here is the workbook.
mate, I notice this formula =SUM(Table1[Balance]) adds up the following amounts of the added rows instead of showing the running balance.
will reupload workbook.

Mate in Bankroll Callenge for Buyings its not giving the calculation for the Rebuys entries instead of -$22.59 it shows $18.50

Days column on Tournaments sheet now references column E (Start).
In the last version, 4-10-23 column K (End Time) was populated this time it is column E.
Continually changing which columns get populated and which don't will keep slowing things down.
Cell Y3 on the info sheet has the formula for Bankroll.
Rebuy formula in cell W6 now displays -22.59

On the allResults sheet cell T6 displays Buyings, which I assume is Buy-ins and cell W6 displays -$22.59

Assuming that the example is in row 2 of the Tournaments sheet, the formula in cell Y2 has been updated so that when a value of .25 is in cell N2 and 6 is in cell S2 the result should be correct.
Also check cell W6 on the allResults sheet for correctness.

Mate, w6 I think is more the amt. than what i have buyin in tournaments.
no i think is correct because of rebuys
4.png here is an exxample of original this is from the actual site

"w6 I think is more the amt. than what i have buyin in tournaments."
Are the amounts in cells N3 and N6:N7 on the Tournaments sheet correct for a single Rake or were they put in based on multiple rebuys?
Note sure what I should be looking for in the screenshot.

Thank u mate. I think w6 is correct along with the rebuys in amt. Yes mate N3 and N6 seem correct why?
can you make the amt. In Rake column N to display the total Rake amt. like the 1st row is .25 instead can it be the 1.25 with the Rebuys included

as for the picture mate, In row 44 under buy-ins where it shows (11/27) i would like number of games itm which is 11 and the 27 would be the games played. that would fall under ALL Games, I want a dropdown under All games f45 where I can picked the Buyin Micro, Low, med. and high, along with the Rebuy, final table and wins. and all finishes places.

"In Rake column N to display the total Rake amt. like the 1st row is .25 instead can it be the 1.25 with the Rebuys included"
Not in column N because a cell cannot contain a manually placed value AND a formula, but another column, i.e. column AE, could be added to show that.

Have a feeling that the first number should actually be 10, coming from the number of tournaments listed in the Tournaments column on the Tournaments sheet.
Also feeling that the second number should actually be 20, coming from the original 10 plus 10 (6,2,1,1) rebuys.
Reminder that we don't get notified when a post is edited, only when a new post is added to the thread.

"In row 44 under buy-ins where it shows (11/27) i would like number of games itm which is 11 and the 27 would be the games played."
I was referring t the above sentence from post #96, which I felt was about cell K45 on the allResults sheet.

mate, in row 44 yes 11 would be the games that made it to the money and the 27 would be the games played.
which is 5 / 10

Please tell us where to find the 11 games that made money and the 27 games that were played.

For cell K45:
If you consider the rows that show a positive profit then the ratio would be 3/10: ="( "&COUNTIFS(Table1[Profit],">0")&" / "&COUNTA(Table1[Tournaments])&" )"
If you consider the rows that show a prize then the ratio is 5/10: ="( "&COUNT(Table1[Prize])&" / "&COUNTA(Table1[Tournaments])&" )"

thank you mate, will apply now. cheers mate.
nice mate you know to much of this to know all this figures.
can we do the Micro (.11-4.40) ITM% and winnings base on the buyins range from .11 - 4.40
and go move along the other buyins.

Here are the buy-in's based on profits that are greater than zero.

Thank you mate, will download and see.
Please see update workbook mate
row 45K not showing the wins in the money. I also added a cash session which is not calculating the hrs played.
also not sure how to have the bankroll balance as you can see i made a profit in the cash for $15.43 Which is not adding to the bankroll.
here is workbook.

It seems that you want row 45 to count rows where there is a prize even if the profit is negative.
The formula in cell Y3 on the Info sheet now adds the Cash profit to the last value in the Bankroll column of Table1.

thank you, happy morning mate, yes master I like been here working with u but I was having computer issues mate and also playing a few games to get me going.cheers sowwwyyy
can we get rebuys, ft and wins. Right on mate the balance is to the perfection with my site balance nice. nice thank thank thank a bunch this will keep me more motivated, even tho gets kind of hard to input manually all the information for now its going to be a bit hard to do

Rebuy: =SUM(Table1[Rebuy])
ft: =SUM(Table1[FT])
wins: =SUM(Table1[WINS])

thank you mate, just finish inputing my schedule for today will upload so you can see. when i input the above results.
mate here is the input with the today tournaments. mate can under finishes show the games i am playing instead of going to tournaments.ga.png

in Total Winnings row 44 can we get positive or negative if either or base on winnings/losses

Guessing that the amounts under (2023) are the winnings (green)/losses (red).

Cell AB45: =SUM(U52:U81)
Don't see a heading for profit.

"...rebuys to the right of 2023"
That would be the five zeros in column U
"Under bankrollsux is the buyin..."
Put the following into cell K45: ="( "&COUNTIFS(O52:O81,">0")&" / "&COUNT(O52:O81)&" )"
If these aren't correct then please tell us the correct amounts based on the data in rows 52:57 and explain how the amounts would be calculated if done manually.

Thank you mate, will do it now cheers
it only shows 2 in the money? i have 3 in the tournament under prize.

"Under bankrollsux is the buyin..."
It seemed that you wanted the count of buy-ins from the all games playing section.
To get buy-ins from the Tournaments sheet use: ="( "&COUNT(Table1[Prize])&" / "&COUNTA(Table1[Tournaments])&" )"

Here is the file with the formula applied in cell K45.

Thank you mate, it gets tough, at times playing 7 or 6 tables at the same time and inputing the information
mate U know what really will help which i am struggling its inputing the time when End. can it be when I press a key or something
so it records the time once i click on the cell, I keep missing and im kind of guessing when i get KO of a tournament because I cant do it sometimes right away. also cant focus having to type and be clicking on my action of the game.

Yes, press the Ctrl, Shift and ; keys, then press the Ctrl and ; keys, and then press the Enter key.
Once the Enter key is pressed the cell should format as those in the rest of the column (test in cell L7 on the Tournaments sheet).

thank you mate, let me give it a shot see if i can do that
Ctrl shift ; then i release and press ctrl ; enter?

Ctrl shift ; then i release and press ctrl ; [release again] and then press enter.
If you aren't getting the desired result, upload so that we can see what you are getting.