# Newbie struggling with nested-if formula

#### escobar

##### New Member
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,""),"")

 B E I J BQ BR BS BT Latency AmountBet Balance Freespins LDW (<3x amount bet) Small win (3-8x amount bet Big win (>8x amount bet Loss 10905 100 4700 0 9204 10 4720 0 9204 4665 10 4740 0 4665 2818 10 4710 0 2818 9111 10 4700 0 9111 1961 5 4710 0 1961 1263 20 4650 0 1263 1762 50 4500 0 1762 1560 10 4470 0 1560 1014 1 4467 0 1014

<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 number B E I J BQ BR BS BT Latency Amountbet Balance Freespins LDW (<3x amount bet) Small win (3-8x amount bet Big win (>8x amount bet Loss 401 858 10 1857 0 858 402 1217 10 1907 0 1217 403 8000 404 14193 405 16284 406 17672 10 1877 0 17672 407 921 10 1897 0 921 408 1357 10 1917 0 1357 409 800 10 1887 0 800

<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

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### bschwartz

##### Well-known Member
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,""),""),"")

#### bschwartz

##### Well-known Member
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,"")

#### escobar

##### New Member
Thanks heaps man, i really appreciate it! Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

### Forum statistics

1,164,288
Messages
5,836,430
Members
430,428
Latest member
Deepak Tanwar ### 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.

### Which adblocker are you using?    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

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