Newbie struggling with nested-if formula

[escobar]

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

 

[bschwartz]

You could simply add another IF argument to your formulas to test for that parameter:

Excel 2010
	B	E	I	J	BQ	BR	BS	BT
1	Latency	AmountBet	Balance	Freespins	LDW (<3x amount bet)	Small win (3-8x amount bet	Big win (>8x amount bet	Loss
2	10,905	100	4,700	0
3	9,204	10	4,720	0	9,204
4	4,665	10	4,740	0	4,665
5	2,818	10	4,710	0				2,818
6	9,111	10	4,700	0				9,111
7	1,961	5	4,710	0	1,961
8	1,263	20	4,650	0				1,263
9	1,762	50	4,500	0				1,762
10	1,560	10	4,470	0				1,560
11	1,014	1	4,467	0				1,014
12	858	10	1857	0				858
13	1217	10	1907	0		1,217
14	8000							8,000
15	14193
16	16284
17	17672	10	1877	0
18	921	10	1897	0	921
19	1357	10	1917	0	1,357
20	800	10	1887	0				800
Sheet1

Cell Formulas
Range	Formula
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]

If you want to simplify your nested IF statements a little, you can use the AND function to combine them.

Excel 2010
	B	E	I	J	BQ	BR	BS	BT
1	Latency	AmountBet	Balance	Freespins	LDW (<3x amount bet)	Small win (3-8x amount bet	Big win (>8x amount bet	Loss
2	10,905	100	4,700	0
3	9,204	10	4,720	0	9,204
4	4,665	10	4,740	0	4,665
5	2,818	10	4,710	0				2,818
6	9,111	10	4,700	0				9,111
7	1,961	5	4,710	0	1,961
8	1,263	20	4,650	0				1,263
9	1,762	50	4,500	0				1,762
10	1,560	10	4,470	0				1,560
11	1,014	1	4,467	0				1,014
12	858	10	1857	0				858
13	1217	10	1907	0		1,217
14	8000							8,000
15	14193
16	16284
17	17672	10	1877	0
18	921	10	1897	0	921
19	1357	10	1917	0	1,357
20	800	10	1887	0				800
Sheet1

Cell Formulas
Range	Formula
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]

Thanks heaps man, i really appreciate it!