If Statement/Excel 2007

knaimi · Jun 23, 2013

I have created three columns (A, B, & C) in Excel 2007 that contain number values. I’m trying to create one “If Statement” in column D that does the following:</SPAN>

If the value of the number in column C is less than both or any of the other two numbers then type 1
If the value of the number in column C equals the value of both numbers Or if it equals one but greater than the other then type 0
If the value of the number in column C is greater than the value of both numbers then type -1

</SPAN>
Example: </SPAN>

A</SPAN>	B</SPAN>	C</SPAN>	D (If Statement)</SPAN>
3</SPAN>	4</SPAN>	3</SPAN>	1</SPAN>
4</SPAN>	2</SPAN>	4</SPAN>	0</SPAN>
6</SPAN>	7</SPAN>	8</SPAN>	-1</SPAN>

Thank you,</SPAN>

etaf · Jun 23, 2013

<a2, c2<b2)[="" code]<a1,c2<b1)="" --="" is="" test="" for="" a="" 1="" if="" true
<a1,c2<b1), 1,=""
<a2, c2<b2),="" 1,="" [="" code]
<a1,c2<b1), 1,="" if(or(c2="(A1+B1)," and(c2="A1,C2"><a1,c2<b1), 1,="" if(or(c2="(A1+B1)," and(c2="A1,C2">If the value of the number in column C is less than both or any of the other two numbers then type 1

=OR(C2<(A1+B1),C2<A2, C2<B2)

so

=IF( OR(C2<(A1+B1),C2<A2, C2<B2), 1,

If the value of the number in column C equals the value of both numbers Or if it equals one but greater than the other then type 0

=OR(C2=(A1+B1), AND(C2=A1,C2>B1), AND(C2=B1,C2>A1) )

so adding to previous IF

=IF( OR(C2<(A1+B1),C2<A2, C2<B2), 1, IF(OR(C2=(A1+B1), AND(C2=A1,C2>B1), AND(C2=B1,C2>A1) ), 0

If the value of the number in column C is greater than the value of both numbers then type -1

C2>(A1+B1)

so adding to IF

=IF( OR(C2<(A1+B1),C2<A2, C2<B2), 1, IF(OR(C2=(A1+B1), AND(C2=A1,C2>B1), AND(C2=B1,C2>A1) ), 0, IF (C2>(A1+B1), -1, "does not meet conditions")))

should do it - just need to test

EDIT
for some reason all my code got changed- so I have put between code tags - not sure if that should be just for VBA code - but that IF did not workout correctly when posted here

and that did not work either - so trying quote now

</a1,c2<b1),></a1,c2<b1),></a2,></a1,c2<b1),></a2,>

etaf · Jun 23, 2013

missed the time limit to edit

so try this now

Sheet1

*	A
1	If the value of the number in column C is less than both or any of the other two numbers then type 1
2	*
3	Code: =OR(C2<(A2+B2),C2<a2, c2<b2)[="" code]<="" td=""></a2,>[/TD] [/TR] [TR] [TD="bgcolor: #cacaca, align: center"]4[/TD] [TD]so [/TD] [/TR] [TR] [TD="bgcolor: #cacaca, align: center"]5[/TD] [TD][code]=IF( OR(C2<(A2+B2),C2<a2, c2<b2),="" 1,="" [="" code]<="" td=""></a2,>[/TD] [/TR] [TR] [TD="bgcolor: #cacaca, align: center"]6[/TD] [TD][/TD] [/TR] [TR] [TD="bgcolor: #cacaca, align: center"]7[/TD] [TD]If the value of the number in column C equals the value of both numbers Or if it equals one but greater than the other then type 0[/TD] [/TR] [TR] [TD="bgcolor: #cacaca, align: center"]8[/TD] [TD][/TD] [/TR] [TR] [TD="bgcolor: #cacaca, align: center"]9[/TD] [TD][code]=OR(C2=(A2+B2), AND(C2=A2,C2>B2), AND(C2=B2,C2>A2) )
10	*
11	so adding to previous IF
12	*
13	Code: `=IF( OR(C2<(A2+B2),C2<a2, c2<b2),="" 1,="" if(or(c2="(A1+B1)," and(c2="A1,C2">B2), AND(C2=B2,C2>A2) ), 0` </a2,>
14	*
15	If the value of the number in column C is greater than the value of both numbers then type -1
16	*
17	C2>(A2+B2)
18	*
19	so adding to IF
20	*
21	Code: `=IF( OR(C2<(A2+B2),C2<a2, c2<b2),="" 1,="" if(or(c2="(A1+B1)," and(c2="A1,C2">B2), AND(C2=B2,C2>A2) ), 0, IF (C2>(A2+B2), -1, "does not meet conditions")))` </a2,>
22	*
23	should do it - just need to test

Excel tables to the web >> Excel Jeanie HTML 4

etaf · Jun 23, 2013

and try again

Excel Workbook

C

4

0

Sheet2

knaimi · Jun 25, 2013

Hi,

Thanks for the help. Unfortunately, the results didn’t match. please check the table below:
I have placed your formula in column D and column E has the right answer. Look at the numbers in the first row. The value of the number in column C is greater that the value of the number in column A and B so the answer should be -1, but the answer I got after using your formula is 0. So your formula added the two values and then compared it to the value of column C.
Row 6, shows that the value of the number in column C is the same as column A and B, so answer should be 0, but the formula gave 1.

I hope you can rewrite the formula and resend it to me. I really appreciate your help

A B C D E
1 1 2 0 -1
2 0 0 1 1
2 3 2 1 1
3 3 2 1 1
2 3 2 1 1
6 6 6 1 0
0 1 0 1 1
2 2 0 1 1
1 2 2 1 0

etaf · Jun 25, 2013

lets make sure we get all the rules absolutly correct

In E you have -1 for 1st row - so rule 3
3. If the value of the number in column C is greater than the value of both numbers then type -1
but A and B are not greater than C - added together they are equal

and the value applies to rule 1

If the value of the number in column C equals the value of both numbers Or if it equals one but greater than the other then type 0

as A+B =2 value of both number - is equal to 2

so I think the rules that should apply would help - and do you mean added together when you say the value of both numbers

i clearly have misunderstood all the rules

knaimi · Jun 26, 2013

Actually, it was my mistake for not clearing the rules. </SPAN>

I DO NOT want to add the numbers in columns A and B in any of the three rules. I just want to compare the value of the numbers in column C with the numbers in column A and then again with column B separately. </SPAN>

I hope that will clear the confusion and thanks again for your help. </SPAN>

etaf · Jun 26, 2013

Thanks for that , sorry for confusion

If the value of the number in column C is less than both or any of the other two numbers then type 1

OR(AND(C6 < A6, C6 <b6 ),="" c6<a6,="" c6<b6)

If the value of the number in column C equals the value of both numbers Or if it equals one but greater than the other then type 0

OR(AND(C6 = A6, C6 =B6 ), AND(C6=A6, C6>B6), AND(C6>A6, C6=B6))

If the value of the number in column C is greater than the value of both numbers then type -1

AND(C6 > A6, C6 > B6 )

Should be the conditions now
so in an IF

=IF(OR(AND(C6 < A6, C6 <b6 ),="" c6<a6,="" c6<b6),1,if(or(and(c6="A6," c6="B6" and(c6="A6,">B6), AND(C6>A6, C6=B6)),0,IF(AND(C6 > A6, C6 > B6 ),-1,"does not meet conditions")))

Excel Workbook

A

B

C

D

E

F

G

H

4

*

Formula

correct

OLD

New

*

5

A

B

C

D

E

*

6

1

2

0

-1

0

-1

TRUE

7

2

0

1

TRUE

8

2

3

2

1

TRUE

9

3

2

1

TRUE

10

2

3

2

1

TRUE

11

6

1

0

1

0

TRUE

12

0

1

0

1

TRUE

13

2

0

1

TRUE

14

1

2

1

0

1

0

TRUE

Sheet1

</b6></b6>

jjones312 · Jun 26, 2013

Try the following:

=IF(OR(AND($C1<$A1,$C1<$B1),OR($C1<$A1,$C1<$B1)),1,IF(OR(AND($C1=$A1,$C1=$B1),AND($C1=$A1,$C1>$B1),AND($C1=$B1,$C1>$A1)),0,IF(OR(AND($C1>$A1,$C1>$B1)),-1,"Didn't many any criteria")))

This is a little narly and may be shortened but I'm learning excel as well.. Also, I've added the fixed cell references..

knaimi · Jun 26, 2013

I copied the formula and paste it into D6. When I hit enter, it gave me an error message.
I have Excel 2007

thanks,

If Statement/Excel 2007

knaimi

New Member

Excel Facts

etaf

Well-known Member

etaf

Well-known Member

etaf

Well-known Member

knaimi

New Member

etaf

Well-known Member

knaimi

New Member

etaf

Well-known Member

jjones312

New Member

knaimi

New Member

Similar threads

Forum statistics

Share this page

If Statement/Excel 2007

New Member

Excel Facts

Well-known Member

Well-known Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock