IF Statement.

[Sysrq111]

Hello.


I just wondered if somebody could help me with IF statements.

I'll explain my situation first - I'm testing out a little system for the roulette wheel and want to see my expected profit after 1,000 spins. I have randomly generated 1,000 numbers and these are in a spreadsheet.

What I want to do is create an IF statement that will go something like this -

IF A1 is between 1 and 12 then dsiplay "-5", IF A1 is 12, 16, 18, etc display "45"

I just don't know how to correctly write the formula.

Thanks.

[starguy]

Hello.


I just wondered if somebody could help me with IF statements.

I'll explain my situation first - I'm testing out a little system for the roulette wheel and want to see my expected profit after 1,000 spins. I have randomly generated 1,000 numbers and these are in a spreadsheet.

What I want to do is create an IF statement that will go something like this -

IF A1 is between 1 and 12 then dsiplay "-5", IF A1 is 12, 16, 18, etc display "45"

I just don't know how to correctly write the formula.

Thanks.

try this

=if(and(A1>1,A1<12),-5,if(or(A1=12,A1=16,A1=18),45))

edited......
thank you mudraker

[mudraker]

starguy

I'm no formula expert but I think there is a mistake in your formula as you have =12 listed twice - The error is caused by Sysrq111 required formula description

=if(and(A1=>1,A1[COLOR=<"Red"]=12[/COLOR]),-5,if(or(A1=12,A1=16,A1=18),45))

[Sysrq111]

Thanks for the help guys. I'll try that.

I know I listed 12 twice... I was just giving an example.

[starguy]

Thanks for the help guys. I'll try that.

I know I listed 12 twice... I was just giving an example.

you're welcome here.
I have corrected the formula for your purpose.

[Sysrq111]

Sorry, I still can't seem to getit working properly.

I don't actually need any "betweens" in the formula.

I need IF A1 = 1, 4, 7, etc, "-5" IF A1 = 0, 34, 35, etc, "-35" IF A1=2, 3, 6, etc, "10"

Something like that. There will be 4 different outcomes "10", "-5", "-20" and "-35"

The only thing I don't understand is the and's and ors

[mudraker]

To help you understand and in the formula

in this formula A1 has to be greater than 1 and less then 12 for the result to equal -5
If both conditions are not meet then the formula calculates the Else part of the formula


=if(and(A1>1,A1<12),-5,if(or(A1=12,A1=16,A1=18),45))

To help you understand Or in the formula
If A1 = 12, 16, 18 then the result = 45 and is calculated only if the 1st part of the formula = false


Formula using If statement based on your last post

=IF(OR(A1=1,A1=4,A1=7),-5,IF(OR(A1=0,A1=34,A1=35),-35,IF(OR(A1=2,A1=3,A1=6),10)))

if A1 = 1 or 4 or 7 then result = -5
elseif A1 = 0 or 34 or 35 result = -35
eslseif A1 = 2 or 3 or 6 result = 10
esle result = false