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,""),"")

BEIJBQBRBSBT
LatencyAmountBetBalanceFreespinsLDW (<3x amount bet)Small win (3-8x amount betBig win (>8x amount betLoss
1090510047000
920410472009204
466510474004665
281810471002818
911110470009111
19615471001961
126320465001263
176250450001762
156010447001560
10141446701014

<tbody>
</tbody>


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.

Row numberBEIJBQBRBSBT
LatencyAmountbetBalanceFreespinsLDW (<3x amount bet)Small win (3-8x amount betBig win (>8x amount betLoss
4018581018570858
402121710190701217
4038000
40414193
40516284
40617672101877017672
4079211018970921
408135710191701357
4098001018870800

<tbody>
</tbody>

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

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

bschwartz

Well-known Member
Joined
Jan 5, 2013
Messages
1,367
You could simply add another IF argument to your formulas to test for that parameter:

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>B</th><th>E</th><th>I</th><th>J</th><th>BQ</th><th>BR</th><th>BS</th><th>BT</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Latency</td><td style=";">AmountBet</td><td style=";">Balance</td><td style=";">Freespins</td><td style=";">LDW (<3x amount bet)</td><td style=";">Small win (3-8x amount bet</td><td style=";">Big win (>8x amount bet</td><td style=";">Loss</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">10,905</td><td style="text-align: right;;">100</td><td style="text-align: right;;">4,700</td><td style="text-align: right;;">0</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">9,204</td><td style="text-align: right;;">10</td><td style="text-align: right;;">4,720</td><td style="text-align: right;;">0</td><td style="text-align: right;;">9,204</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">4,665</td><td style="text-align: right;;">10</td><td style="text-align: right;;">4,740</td><td style="text-align: right;;">0</td><td style="text-align: right;;">4,665</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">2,818</td><td style="text-align: right;;">10</td><td style="text-align: right;;">4,710</td><td style="text-align: right;;">0</td><td style=";"></td><td style=";"></td><td style=";"></td><td style="text-align: right;;">2,818</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">9,111</td><td style="text-align: right;;">10</td><td style="text-align: right;;">4,700</td><td style="text-align: right;;">0</td><td style=";"></td><td style=";"></td><td style=";"></td><td style="text-align: right;;">9,111</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">1,961</td><td style="text-align: right;;">5</td><td style="text-align: right;;">4,710</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1,961</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">1,263</td><td style="text-align: right;;">20</td><td style="text-align: right;;">4,650</td><td style="text-align: right;;">0</td><td style=";"></td><td style=";"></td><td style=";"></td><td style="text-align: right;;">1,263</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">1,762</td><td style="text-align: right;;">50</td><td style="text-align: right;;">4,500</td><td style="text-align: right;;">0</td><td style=";"></td><td style=";"></td><td style=";"></td><td style="text-align: right;;">1,762</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">1,560</td><td style="text-align: right;;">10</td><td style="text-align: right;;">4,470</td><td style="text-align: right;;">0</td><td style=";"></td><td style=";"></td><td style=";"></td><td style="text-align: right;;">1,560</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">1,014</td><td style="text-align: right;;">1</td><td style="text-align: right;;">4,467</td><td style="text-align: right;;">0</td><td style=";"></td><td style=";"></td><td style=";"></td><td style="text-align: right;;">1,014</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">858</td><td style="text-align: right;;">10</td><td style="text-align: right;;">1857</td><td style="text-align: right;;">0</td><td style=";"></td><td style=";"></td><td style=";"></td><td style="text-align: right;;">858</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">1217</td><td style="text-align: right;;">10</td><td style="text-align: right;;">1907</td><td style="text-align: right;;">0</td><td style=";"></td><td style="text-align: right;;">1,217</td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">8000</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style="text-align: right;;">8,000</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">14193</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">16284</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">17672</td><td style="text-align: right;;">10</td><td style="text-align: right;;">1877</td><td style="text-align: right;;">0</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;">921</td><td style="text-align: right;;">10</td><td style="text-align: right;;">1897</td><td style="text-align: right;;">0</td><td style="text-align: right;;">921</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;">1357</td><td style="text-align: right;;">10</td><td style="text-align: right;;">1917</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1,357</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;;">800</td><td style="text-align: right;;">10</td><td style="text-align: right;;">1887</td><td style="text-align: right;;">0</td><td style=";"></td><td style=";"></td><td style=";"></td><td style="text-align: right;;">800</td></tr></tbody></table><p style="width:4.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">BQ2</th><td style="text-align:left">=IF(<font color="Blue">SUM(<font color="Red">I1</font>)>0,IF(<font color="Red">$J1=0,IF(<font color="Green">$I2>$I1,IF(<font color="Purple">$I2-$I1<(<font color="Teal">$E2*3</font>),$B2,""</font>),""</font>),""</font>),""</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">BR2</th><td style="text-align:left">=IF(<font color="Blue">SUM(<font color="Red">I1</font>)>0,IF(<font color="Red">$J1=0,IF(<font color="Green">$I2>$I1,IF(<font color="Purple">$I2-$I1<(<font color="Teal">$E2*8</font>),IF(<font color="Teal">$I2-$I1>(<font color="#FF00FF">$E2*2.9</font>),$B2,""</font>),""</font>),""</font>),""</font>),""</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">BS2</th><td style="text-align:left">=IF(<font color="Blue">SUM(<font color="Red">I1</font>)>0,IF(<font color="Red">$J1=0,IF(<font color="Green">$I2>$I1,IF(<font color="Purple">$I2-$I1>(<font color="Teal">$E2*8</font>),$B2,""</font>),""</font>),""</font>),""</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">BT2</th><td style="text-align:left">=IF(<font color="Blue">SUM(<font color="Red">I1</font>)>0,IF(<font color="Red">$J1=0,IF(<font color="Green">$I1>$I2,$B2,""</font>),""</font>),""</font>)</td></tr></tbody></table></td></tr></table><br />
 

bschwartz

Well-known Member
Joined
Jan 5, 2013
Messages
1,367
If you want to simplify your nested IF statements a little, you can use the AND function to combine them.

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>B</th><th>E</th><th>I</th><th>J</th><th>BQ</th><th>BR</th><th>BS</th><th>BT</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Latency</td><td style=";">AmountBet</td><td style=";">Balance</td><td style=";">Freespins</td><td style=";">LDW (<3x amount bet)</td><td style=";">Small win (3-8x amount bet</td><td style=";">Big win (>8x amount bet</td><td style=";">Loss</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">10,905</td><td style="text-align: right;;">100</td><td style="text-align: right;;">4,700</td><td style="text-align: right;;">0</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">9,204</td><td style="text-align: right;;">10</td><td style="text-align: right;;">4,720</td><td style="text-align: right;;">0</td><td style="text-align: right;;">9,204</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">4,665</td><td style="text-align: right;;">10</td><td style="text-align: right;;">4,740</td><td style="text-align: right;;">0</td><td style="text-align: right;;">4,665</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">2,818</td><td style="text-align: right;;">10</td><td style="text-align: right;;">4,710</td><td style="text-align: right;;">0</td><td style=";"></td><td style=";"></td><td style=";"></td><td style="text-align: right;;">2,818</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">9,111</td><td style="text-align: right;;">10</td><td style="text-align: right;;">4,700</td><td style="text-align: right;;">0</td><td style=";"></td><td style=";"></td><td style=";"></td><td style="text-align: right;;">9,111</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">1,961</td><td style="text-align: right;;">5</td><td style="text-align: right;;">4,710</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1,961</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">1,263</td><td style="text-align: right;;">20</td><td style="text-align: right;;">4,650</td><td style="text-align: right;;">0</td><td style=";"></td><td style=";"></td><td style=";"></td><td style="text-align: right;;">1,263</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">1,762</td><td style="text-align: right;;">50</td><td style="text-align: right;;">4,500</td><td style="text-align: right;;">0</td><td style=";"></td><td style=";"></td><td style=";"></td><td style="text-align: right;;">1,762</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">1,560</td><td style="text-align: right;;">10</td><td style="text-align: right;;">4,470</td><td style="text-align: right;;">0</td><td style=";"></td><td style=";"></td><td style=";"></td><td style="text-align: right;;">1,560</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">1,014</td><td style="text-align: right;;">1</td><td style="text-align: right;;">4,467</td><td style="text-align: right;;">0</td><td style=";"></td><td style=";"></td><td style=";"></td><td style="text-align: right;;">1,014</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">858</td><td style="text-align: right;;">10</td><td style="text-align: right;;">1857</td><td style="text-align: right;;">0</td><td style=";"></td><td style=";"></td><td style=";"></td><td style="text-align: right;;">858</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">1217</td><td style="text-align: right;;">10</td><td style="text-align: right;;">1907</td><td style="text-align: right;;">0</td><td style=";"></td><td style="text-align: right;;">1,217</td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">8000</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style="text-align: right;;">8,000</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">14193</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">16284</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">17672</td><td style="text-align: right;;">10</td><td style="text-align: right;;">1877</td><td style="text-align: right;;">0</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;">921</td><td style="text-align: right;;">10</td><td style="text-align: right;;">1897</td><td style="text-align: right;;">0</td><td style="text-align: right;;">921</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;">1357</td><td style="text-align: right;;">10</td><td style="text-align: right;;">1917</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1,357</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;;">800</td><td style="text-align: right;;">10</td><td style="text-align: right;;">1887</td><td style="text-align: right;;">0</td><td style=";"></td><td style=";"></td><td style=";"></td><td style="text-align: right;;">800</td></tr></tbody></table><p style="width:4.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">BQ2</th><td style="text-align:left">=IF(<font color="Blue">AND(<font color="Red">I2>SUM(<font color="Green">I1</font>),I2-SUM(<font color="Green">I1</font>)<E2*3,J1=0,SUM(<font color="Green">I1</font>)>0</font>),B2,""</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">BR2</th><td style="text-align:left">=IF(<font color="Blue">AND(<font color="Red">I2>SUM(<font color="Green">I1</font>),I2-SUM(<font color="Green">I1</font>)>=E2*3,I2-SUM(<font color="Green">I1</font>)<E2*8,J1=0,SUM(<font color="Green">I1</font>)>0</font>),B2,""</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">BS2</th><td style="text-align:left">=IF(<font color="Blue">AND(<font color="Red">I2>SUM(<font color="Green">I1</font>),I2-SUM(<font color="Green">I1</font>)>=E2*8,J1=0,SUM(<font color="Green">I1</font>)>0</font>),B2,""</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">BT2</th><td style="text-align:left">=IF(<font color="Blue">AND(<font color="Red">I2<SUM(<font color="Green">I1</font>),J1=0,SUM(<font color="Green">I1</font>)>0</font>),B2,""</font>)</td></tr></tbody></table></td></tr></table><br />
 

Watch MrExcel Video

Forum statistics

Threads
1,130,118
Messages
5,640,215
Members
417,131
Latest member
Seanr19871

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
Top