if condition for range of values

[excelism]

Hi,

I need help in writing a formula with if condition. I have a table as below

-------A--------B--------C------D-------E (Column names)

1------A--------B--------x1-----x2-----x3
2------x1-------x2------1--------1-------0
3------x5-------x6------0--------0-------0

I tried with the formula: IF ($A2>=C$1,1,0) (Though incomplete)

I would like to set a value of 0,1 if the column values (x1,x2,x3) lies within the range of values present in column A and B. Ex: x1 lies with x1 and x2 so set to 1 in the C2.

I tried with different combinations but its not working. Can any one help me out on how to handle this condition. Thanks in advance.

[martindwilson]

is that literally x1 or is it a number?
if its number values
=--AND(C$1>=$A2,C$1<=$B2)

[daddylonglegs]

Try using AND, i.e.

=IF(AND($A2<=C$1,$B2>=C$1),1,0)

[excelism]

...i could resolve this by myself . I tried similarly with one in last post. Thanks for the quick responses. I appreciate your help.

[excelism]

I would like to extend this question..

1------A--------B----C---------x1------x2-----x3
2------x1-------x2---obj1------1--------1-------0
3------x4-------x6---obj1------0--------0-------0
4------x5-------x6---obj2------0--------0-------0

I'm using the above columns x1, x2, x3 in a pivot table...

I kept x1, x2, x3 in Values section. (which will appear in pivot table as Count of x1, Count of x2)
and column C in Row Labels Section.

Count of x1 is displaying a value of 2 for obj1...I'm expecting that count of x1 will display the values which are set to 1 (i.e Count of x1 should be 1). Is it possible to do this in pivot table?. Please let me know.

[excelism]

I could change this by selecting Sum of X1 instead of Count of X1...

[arthurbr]

Hi,
please start a new thread with your new question, it will be easier for others to take advantage of the solutions,. Don't forget to change the title accordingly.
Cheers

[excelism]

Sorry, I will update it from the next time.