Automatically fill in a value if the column number is between several stated number ranges.

[Sharon1234]

I am trying to make a large spread sheet that will automatically fill in the 1's under the number if it appears between the start-stop columns; as shown in the example.

It will have many start stop columns filed in, for hundreds of numbers.

I can get it to work for the number if it is between the start-stop figures from one section; but I cant get multiple selections of start-stop values to work. The equation I have used for this is =AND(I$2>=$B4,I$2<=$C4) This gives me 'TRUE' or 'FALSE' which I can then convert to 1 or 0, if anyone knows how to make it a 1 in the first instance that would also be greatly appreciated.

Thank you in advance to anyone able to help



[TABLE="width: 603"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]........[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD]total[/TD]
[/TR]
[TR]
[TD]Start[/TD]
[TD]Stop[/TD]
[TD][/TD]
[TD]Start[/TD]
[TD]Stop[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Pup Denab]

Hi Sharon1234
Welcome to the forum, try something like this,
=IF(AND(I$2>=$B4,I$2<=$C4),1,0)
at the end you can decide what you would like to use, the 1, is the 1 you request the 0 is generally what most people use, but this can be any number or text you requiry, possibly you want "", which will show blank
Regards
Pup

 

[Sharon1234]

Hi Pup Denab

Ahh...Thank you so much, this works well to get the 1's directly, also great to know how to get a blank rather than a zero it will look much better now.

I also would like to know how to get the equation to show a 1 if the value is between the first number range OR the second number range. I have tried several variations of the following:

=IF(AND(J2>=B4,J2<=C4) (AND(J2>=E4,J2<=F4)),1,"")

I can't find a way to connect the two statements to that I get a 1 if either of the number ranges contain the column number.

Any ideas on how to connect these statements?

Thank you so much for your help

 

[Pup Denab]

HI Sharon1234
It the same formula as before except you need to use an OR instead of AND
OR if any calculations is true true false this would return true
AND if all calculations are true true false this would return false
=IF(OR(I$2>=B4,I$2<=C4),1,0)
Hope this helps
Regards
Pup

 

[Sharon1234]

Hi Pup Denab</SPAN>

Thank you for your help, I am still learning all the functions and what they mean.</SPAN>

However this equation does not get the desired result.</SPAN>

The column value has to be more than the start value and less than the stop value to fit the criteria; so it has to be an AND.

The problem is that there are several start and stop values I would like it to check; if the start stop values were 2-3 and 7-9 the equation should give a 1 for columns 2,3, 7, 8, and 9 only.
</SPAN>
I think that the equation should be something like this, but I can’t get it to work
</SPAN>
=IF(AND(J2>=B4,J2<=C4) OR (AND(J2>=E4,J2<=F4)),1,"")</SPAN>

=IF(OR(AND(J2>=B4,J2<=C4),(AND(J2>=E4,J2<=F4)),1,""))</SPAN>

Any Ideas where to stick the OR in this case?

Thank you so much for all your help, I am quite stuck on this one.

Sharon1234

 

[Sharon1234]

Hi All

I finally got the brackets in the right place and it is working:

=IF(OR(AND(C$24>$C2,C$24<=$D2),(AND(C$24>$F2,C$24<=$G2))),1,"")

Any idea how I add a third fourth and fifth number range into the OR section, I have tried the following but again I am stuck:

=IF(OR(AND(C$24>$C3,C$24<=$D3),(AND(C$24>$F3,C$24<=$G3),(AND(C$24>$I3,C$24<=$J3))),1,"")

 

[Sharon1234]

I fixed it, all is well, more brackets were required. thank you so much for your help

=IF(OR((AND(C$24>$C3,C$24<=$D3)),(AND(C$24>$F3,C$24<=$G3)),(AND(C$24>$I3,C$24<=$J3)))),1,"")

 

[Pup Denab]

HI Sharon1234
Excellent, nice to see you well on your way, please feel free to give me a shout if you have any further issue, or just post on the forum, the guys & girls here are all excellent
Regards
Pup


Thanks for letting us know all is well