https://docs.google.com/spreadsheet/...HN1dVVFVERpREE
I am trying to make a database of the most common stack sizes in 6-max hypers with 3 remaining when the BB is 100. Right now I managed to have a functional semi-automatic spreadsheet but I still have a lot of manual work to do.

On the page "Hand Histories" I paste 4 lines from the hand history that tell the 3 stack sizes and who is the button. On the page "Stack Sizes" I extract the data but with my formula the data is correctly taken one line out of 4 and I have to manually delete the other 3.

Here is a solution that would fix my problem:
when I drag down =if('Hand Histories'!C186="BU",'Hand Histories'!B186,if('Hand Histories'!C187="BU",'Hand Histories'!B187,if('Hand Histories'!C188="BU",'Hand Histories'!B188)))
it changes to =if('Hand Histories'!C187="BU",'Hand Histories'!B187,if('Hand Histories'!C188="BU",'Hand Histories'!B188,if('Hand Histories'!C189="BU",'Hand Histories'!B189)))
then I have to drag it down by 3 more. I would want the numbers to go up by 4 every time. Sorry if this is really confusing.

First, do you have access to Excel? I think I may know a way to get what you want, but I don't know if it translates into Google Docs.

The second thing is I think I know what you want, but I don't understand why. If you are trying to get the most common stack sizes, why do you need to know that stack size's position? The small, middle, and large stacks are all going to be the button, SB, and BB equally. Wouldn't it be better, cleaner just to extract the chip count from column A in Hand histories (basically what you already have in column B, but without the seat numbers)?

I've never used google docs and it doesn't seem like I can edit yours so I can't check. But in excel what you can do is:

delete all rows from 3 down so that you just have row 2 left with your formulas
highlight a2 - c5. This will get your formulas in a2-c2 and three blank lines below them
grab the lower right corner that does the "fill down" command and drag it down really far
You'll have all your results with three empty lines between them like:

1095 605 1300



535 1390 1075



1320 955 725

Then just sort on any column. This will put the numbers at the top and the blanks at the bottom so they won't be in the way.

Here's one way, skipping the StackSizes formulas:

On the HandHistories sheet, put the following:
D1: SB
E1: BB
F1: BU

D2: =if($C1=ʺʺ,index($B2:$B4,match(D$1,$C2:$C4,0),1),ʺ ʺ)

Copy that formula across to E and F, then down as far as you need it. Then copy those three columns to another sheet, and sort to get all the spaces at the bottom.

I want to memorize the perfect nash charts for this game but since there are so many possible situations, I want to memorize the most common ones. The position of each stack makes a very big difference to the strategy in this format.

This gives me an error for some reason

Will try jmark's suggestion on Excel.

Well I still don't understand your reasons, I found a solution. My solution involves using Vlookup, but this function can only give you information that is to the right of whatever you are looking up. For this to work you have to switch the B and C columns in the Hand Histories sheet. I did verify this works with Google Docs.

Maybe you know this, but I'll go over real quick how to switch those columns and keep the formula's intact. Go to the Hand Histories sheets, then choose the whole column B by clicking on the letter B. Right click and choose "insert 1 left". The highlight your entire positions column (which is now column D), then right click and choose Cut. Now highlight column B again and right click and choose Paste. They are now switch with the formulas intact.

Now choose the Stack Sizes sheet. Change the following cells as intructed-
A2: =VLOOKUP($A$1,INDIRECT("'Hand Histories'!"&ADDRESS(ROW()*4-6,2)&":"&ADDRESS(ROW()*4-4,3)),2,FALSE)
B2: =VLOOKUP($B$1,INDIRECT("'Hand Histories'!"&ADDRESS(ROW()*4-6,2)&":"&ADDRESS(ROW()*4-4,3)),2,FALSE)
C2: =VLOOKUP($C$1,INDIRECT("'Hand Histories'!"&ADDRESS(ROW()*4-6,2)&":"&ADDRESS(ROW()*4-4,3)),2,FALSE)

You can now fill down and everything fill in correctly.

Thanks again for the help. I just made a new one where I tried to list all the possible stack sizes for the second hand. My assumptions are: someone either gets the blinds or stacks someone else and there are no 3-way all ins. I made the document publicly editable:
https://docs.google.com/spreadsheet/...zRvODNacm55dHc
I thought I did everything right but the stack sizes I ended up getting do not always add up to 3,000 chips and I don't know where I made a mistake.

First in Sheet1 A2 thru D2 are 10 because I'm assuming thats what the antes are, and E2=35 because the SB=25 + 10 ante. Shouldn't F2=60 (10+50)? Instead of 65?

So the rest of Sheet1 I see you got the results if any two (and only two) positions go all in on the first hand what the resulting stacks would be, and also if any one position goes all in and nobody calls, so they win the blinds and ante. I can see that's what you're trying to accomplish, and in fact all the numbers are there and they all balance out to 3000. It seems to me what you were looking for is in Sheet1 already.

What I don't understand is Sheet2. What do the numbers mean (1,2 & Busto), why the formulas in columns G thru L don't have an IF statement that reference column B, or just really what you're trying to accomplish there. I don't see any need to manipulate Sheet1 at all.

I'm trying to get the stack sizes for the very next hand. e.g. If UTG wins against MP then the very next hand he will become BB with 1,120 chips (pre blinds/antes). I basically did a bunch of complicated stuff to try to move everybody by one position. There is no MP when 5 handed but there still is if nobody busts the first hand so it was a bit challenging.

Edit: Figured something out; thanks for the help with the other sheet!