Newbie struggling with nested-if formula

escobar

New Member
Joined
May 18, 2013
Messages
20
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
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
You could simply add another IF argument to your formulas to test for that parameter:


Excel 2010
BEIJBQBRBSBT
1LatencyAmountBetBalanceFreespinsLDW (<3x amount bet)Small win (3-8x amount betBig win (>8x amount betLoss
210,9051004,7000    
39,204104,72009,204
44,665104,74004,665
52,818104,71002,818
69,111104,70009,111
71,96154,71001,961
81,263204,65001,263
91,762504,50001,762
101,560104,47001,560
111,01414,46701,014
128581018570858
13121710190701,217
1480008,000
1514193
1616284
17176721018770
189211018970921
19135710191701,357
208001018870800
Sheet1
Cell Formulas
RangeFormula
BQ2=IF(SUM(I1)>0,IF($J1=0,IF($I2>$I1,IF($I2-$I1<($E2*3),$B2,""),""),""),"")
BR2=IF(SUM(I1)>0,IF($J1=0,IF($I2>$I1,IF($I2-$I1<($E2*8),IF($I2-$I1>($E2*2.9),$B2,""),""),""),""),"")
BS2=IF(SUM(I1)>0,IF($J1=0,IF($I2>$I1,IF($I2-$I1>($E2*8),$B2,""),""),""),"")
BT2=IF(SUM(I1)>0,IF($J1=0,IF($I1>$I2,$B2,""),""),"")
 
Upvote 0
If you want to simplify your nested IF statements a little, you can use the AND function to combine them.


Excel 2010
BEIJBQBRBSBT
1LatencyAmountBetBalanceFreespinsLDW (<3x amount bet)Small win (3-8x amount betBig win (>8x amount betLoss
210,9051004,7000    
39,204104,72009,204
44,665104,74004,665
52,818104,71002,818
69,111104,70009,111
71,96154,71001,961
81,263204,65001,263
91,762504,50001,762
101,560104,47001,560
111,01414,46701,014
128581018570858
13121710190701,217
1480008,000
1514193
1616284
17176721018770
189211018970921
19135710191701,357
208001018870800
Sheet1
Cell Formulas
RangeFormula
BQ2=IF(AND(I2>SUM(I1),I2-SUM(I1)I1)>0),B2,"")
BR2=IF(AND(I2>SUM(I1),I2-SUM(I1)>=E2*3,I2-SUM(I1)I1)>0),B2,"")
BS2=IF(AND(I2>SUM(I1),I2-SUM(I1)>=E2*8,J1=0,SUM(I1)>0),B2,"")
BT2=IF(AND(I2I1),J1=0,SUM(I1)>0),B2,"")
 
Upvote 0

Forum statistics

Threads
1,226,287
Messages
6,190,065
Members
453,592
Latest member
bcexcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top