Hey everyone,
I've run into an issue (the issue being my lack of skill/knowledge in excel) when making a formula. I'll give you some background info and an example.
Background: I run slot machine simulations, and analyse the data in excel. At the moment, im trying to understand how the size of participants' win on any individual spin relates to how quickly they spin again. Sounds easy, right?
Here is a small snapshot of the relevant columns and the formula i have made. In this case, the formula is working fine. The 4 right-most columns are different win/loss sizes. The formulae i use for BQ-> BT are as follows:
BQ: =IF($J2=0,IF($I3>$I2,IF($I3-$I2<($E3*3),$B3,""),""),"") BR: =IF($J2=0,IF($I3>$I2,IF($I3-$I2<($E3*8),IF($I3-$I2>($E3*2.9),$B3,""),""),""),"")
BS: =IF($J2=0,IF($I3>$I2,IF($I3-$I2>($E3*8),$B3,""),""),"") BT: =IF($J2=0,IF($I2>$I3,$B3,""),"")
[TABLE="width: 500"]
<tbody>[TR]
[TD]B[/TD]
[TD]E[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]BQ[/TD]
[TD]BR[/TD]
[TD]BS[/TD]
[TD]BT[/TD]
[/TR]
[TR]
[TD]Latency[/TD]
[TD]AmountBet[/TD]
[TD]Balance[/TD]
[TD]Freespins[/TD]
[TD]LDW (<3x amount bet)[/TD]
[TD]Small win (3-8x amount bet[/TD]
[TD]Big win (>8x amount bet[/TD]
[TD]Loss[/TD]
[/TR]
[TR]
[TD]10905[/TD]
[TD]100[/TD]
[TD]4700[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9204[/TD]
[TD]10[/TD]
[TD]4720[/TD]
[TD]0[/TD]
[TD]9204[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4665[/TD]
[TD]10[/TD]
[TD]4740[/TD]
[TD]0[/TD]
[TD]4665[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2818[/TD]
[TD]10[/TD]
[TD]4710[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2818[/TD]
[/TR]
[TR]
[TD]9111[/TD]
[TD]10[/TD]
[TD]4700[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]9111[/TD]
[/TR]
[TR]
[TD]1961[/TD]
[TD]5[/TD]
[TD]4710[/TD]
[TD]0[/TD]
[TD]1961[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1263[/TD]
[TD]20[/TD]
[TD]4650[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1263[/TD]
[/TR]
[TR]
[TD]1762[/TD]
[TD]50[/TD]
[TD]4500[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1762[/TD]
[/TR]
[TR]
[TD]1560[/TD]
[TD]10[/TD]
[TD]4470[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1560[/TD]
[/TR]
[TR]
[TD]1014[/TD]
[TD]1[/TD]
[TD]4467[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1014[/TD]
[/TR]
</tbody>[/TABLE]
As you can see, each row has relevant data and so my caveman nested-if method works. The problem arises when participants switch to a different slo machine in the simulation, which outputs a blank row into the datafile. They can do this as much as they want, so sometimes there are 5 rows blank. An example of this, and the error in my formula is shown below in BS406.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Row number[/TD]
[TD]B[/TD]
[TD]E[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]BQ[/TD]
[TD]BR[/TD]
[TD]BS[/TD]
[TD]BT[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Latency[/TD]
[TD]Amountbet[/TD]
[TD]Balance[/TD]
[TD]Freespins[/TD]
[TD]LDW (<3x amount bet)[/TD]
[TD]Small win (3-8x amount bet[/TD]
[TD]Big win (>8x amount bet[/TD]
[TD]Loss[/TD]
[/TR]
[TR]
[TD]401[/TD]
[TD]858[/TD]
[TD]10[/TD]
[TD]1857[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]858[/TD]
[/TR]
[TR]
[TD]402[/TD]
[TD]1217[/TD]
[TD]10[/TD]
[TD]1907[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]1217[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]403[/TD]
[TD]8000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]404[/TD]
[TD]14193[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]405[/TD]
[TD]16284[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]406[/TD]
[TD]17672[/TD]
[TD]10[/TD]
[TD]1877[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD]17672[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]407[/TD]
[TD]921[/TD]
[TD]10[/TD]
[TD]1897[/TD]
[TD]0[/TD]
[TD]921[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]408[/TD]
[TD]1357[/TD]
[TD]10[/TD]
[TD]1917[/TD]
[TD]0[/TD]
[TD]1357[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]409[/TD]
[TD]800[/TD]
[TD]10[/TD]
[TD]1887[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]800[/TD]
[/TR]
</tbody>[/TABLE]
Because the participant has just switched machine, i dont want to count this latency at all (ideally BS406 will be left blank). This happens reasonably frequently across a number of participants, so manually cleaning the data will take a long time.
If anyone can help me with this, i would very much appreciate it.
P.s. my apologies for the horrible nested-if functions, i realise there are probably a lot better ways to do this. Cheers
I've run into an issue (the issue being my lack of skill/knowledge in excel) when making a formula. I'll give you some background info and an example.
Background: I run slot machine simulations, and analyse the data in excel. At the moment, im trying to understand how the size of participants' win on any individual spin relates to how quickly they spin again. Sounds easy, right?
Here is a small snapshot of the relevant columns and the formula i have made. In this case, the formula is working fine. The 4 right-most columns are different win/loss sizes. The formulae i use for BQ-> BT are as follows:
BQ: =IF($J2=0,IF($I3>$I2,IF($I3-$I2<($E3*3),$B3,""),""),"") BR: =IF($J2=0,IF($I3>$I2,IF($I3-$I2<($E3*8),IF($I3-$I2>($E3*2.9),$B3,""),""),""),"")
BS: =IF($J2=0,IF($I3>$I2,IF($I3-$I2>($E3*8),$B3,""),""),"") BT: =IF($J2=0,IF($I2>$I3,$B3,""),"")
[TABLE="width: 500"]
<tbody>[TR]
[TD]B[/TD]
[TD]E[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]BQ[/TD]
[TD]BR[/TD]
[TD]BS[/TD]
[TD]BT[/TD]
[/TR]
[TR]
[TD]Latency[/TD]
[TD]AmountBet[/TD]
[TD]Balance[/TD]
[TD]Freespins[/TD]
[TD]LDW (<3x amount bet)[/TD]
[TD]Small win (3-8x amount bet[/TD]
[TD]Big win (>8x amount bet[/TD]
[TD]Loss[/TD]
[/TR]
[TR]
[TD]10905[/TD]
[TD]100[/TD]
[TD]4700[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9204[/TD]
[TD]10[/TD]
[TD]4720[/TD]
[TD]0[/TD]
[TD]9204[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4665[/TD]
[TD]10[/TD]
[TD]4740[/TD]
[TD]0[/TD]
[TD]4665[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2818[/TD]
[TD]10[/TD]
[TD]4710[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2818[/TD]
[/TR]
[TR]
[TD]9111[/TD]
[TD]10[/TD]
[TD]4700[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]9111[/TD]
[/TR]
[TR]
[TD]1961[/TD]
[TD]5[/TD]
[TD]4710[/TD]
[TD]0[/TD]
[TD]1961[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1263[/TD]
[TD]20[/TD]
[TD]4650[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1263[/TD]
[/TR]
[TR]
[TD]1762[/TD]
[TD]50[/TD]
[TD]4500[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1762[/TD]
[/TR]
[TR]
[TD]1560[/TD]
[TD]10[/TD]
[TD]4470[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1560[/TD]
[/TR]
[TR]
[TD]1014[/TD]
[TD]1[/TD]
[TD]4467[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1014[/TD]
[/TR]
</tbody>[/TABLE]
As you can see, each row has relevant data and so my caveman nested-if method works. The problem arises when participants switch to a different slo machine in the simulation, which outputs a blank row into the datafile. They can do this as much as they want, so sometimes there are 5 rows blank. An example of this, and the error in my formula is shown below in BS406.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Row number[/TD]
[TD]B[/TD]
[TD]E[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]BQ[/TD]
[TD]BR[/TD]
[TD]BS[/TD]
[TD]BT[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Latency[/TD]
[TD]Amountbet[/TD]
[TD]Balance[/TD]
[TD]Freespins[/TD]
[TD]LDW (<3x amount bet)[/TD]
[TD]Small win (3-8x amount bet[/TD]
[TD]Big win (>8x amount bet[/TD]
[TD]Loss[/TD]
[/TR]
[TR]
[TD]401[/TD]
[TD]858[/TD]
[TD]10[/TD]
[TD]1857[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]858[/TD]
[/TR]
[TR]
[TD]402[/TD]
[TD]1217[/TD]
[TD]10[/TD]
[TD]1907[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]1217[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]403[/TD]
[TD]8000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]404[/TD]
[TD]14193[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]405[/TD]
[TD]16284[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]406[/TD]
[TD]17672[/TD]
[TD]10[/TD]
[TD]1877[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD]17672[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]407[/TD]
[TD]921[/TD]
[TD]10[/TD]
[TD]1897[/TD]
[TD]0[/TD]
[TD]921[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]408[/TD]
[TD]1357[/TD]
[TD]10[/TD]
[TD]1917[/TD]
[TD]0[/TD]
[TD]1357[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]409[/TD]
[TD]800[/TD]
[TD]10[/TD]
[TD]1887[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]800[/TD]
[/TR]
</tbody>[/TABLE]
Because the participant has just switched machine, i dont want to count this latency at all (ideally BS406 will be left blank). This happens reasonably frequently across a number of participants, so manually cleaning the data will take a long time.
If anyone can help me with this, i would very much appreciate it.
P.s. my apologies for the horrible nested-if functions, i realise there are probably a lot better ways to do this. Cheers