Excel IF Question

[say4nything]

Hello

I'm having trouble with a bit of a tricky IF function on microsoft excel.

I'll attempt to put this into words..

Basically, I have Column A set to multiples of 1.6. So, starting at 0, 1.6, 3.2, 4.8, etc. all the way to several hundred
(years)

In Column B I have multiples of 0.308. So, 0, 0.308, 0.616, etc...

In Column C I have the same multiples of Column B + 8.5446*10^-4, so...0.30885446, 0.61685446, etc... all up to a few hundred (years)

So, what I want to find if any of the values in Column A falls between the Column C-D numbers

Any ideas?

 

[JoeMo]

Hello

I'm having trouble with a bit of a tricky IF function on microsoft excel.

I'll attempt to put this into words..

Basically, I have Column A set to multiples of 1.6. So, starting at 0, 1.6, 3.2, 4.8, etc. all the way to several hundred
(years)

In Column B I have multiples of 0.308. So, 0, 0.308, 0.616, etc...

In Column C I have the same multiples of Column B + 8.5446*10^-4, so...0.30885446, 0.61685446, etc... all up to a few hundred (years)

So, what I want to find if any of the values in Column A falls between the Column C-D numbers

Any ideas?

Care to share what's in column D?

 

[bschwartz]

Maybe something like this?

=AND(A2>B2,A2
<c2)
<c2)
<c2)<c2)
<c2)

assuming you meant between B-C, not C-D)

Edit: Formula is not going through for some reason. End of formula is less than symbol followed by C2)<c2)< c2)
</c2)<></c2)
</c2)<c2)
</c2)
</c2)

 

[bschwartz]

Actually, that will always be false. Did you want to know whether each A value falls between any B and the same C?

 

[say4nything]

sorry, typo. I meant column b-c

 

[say4nything]

" Did you want to know whether each A value falls between any B and the same C?"

Yeah, sorry

I want to know if any of the column A values fall between any of the B and corresponding C values...if that makes sense

 

[bschwartz]

I'm not sure if there is a faster way, but I believe this accomplishes what you are looking for using a helper column. I'm not getting any TRUEs however.

Excel 2010
	A	B	C	D
1	1.6	0.308	.308+8.5446*10^4-4	A2>$B$2:$B$301,<$C$2:$C$301
2	0	0	0.00085446	FALSE
3	1.6	0.308	0.30885446	FALSE
4	3.2	0.616	0.61685446	FALSE
5	4.8	0.924	0.92485446	FALSE
6	6.4	1.232	1.23285446	FALSE
7	8	1.54	1.54085446	FALSE
8	9.6	1.848	1.84885446	FALSE
9	11.2	2.156	2.15685446	FALSE
10	12.8	2.464	2.46485446	FALSE
11	14.4	2.772	2.77285446	FALSE
12	16	3.08	3.08085446	FALSE
13	17.6	3.388	3.38885446	FALSE
14	19.2	3.696	3.69685446	FALSE
Sheet106

Cell Formulas
Range	Formula
C2	=B2+8.5446*10^-4
C3	=B3+8.5446*10^-4
C4	=B4+8.5446*10^-4
C5	=B5+8.5446*10^-4
C6	=B6+8.5446*10^-4
C7	=B7+8.5446*10^-4
C8	=B8+8.5446*10^-4
C9	=B9+8.5446*10^-4
C10	=B10+8.5446*10^-4
C11	=B11+8.5446*10^-4
C12	=B12+8.5446*10^-4
C13	=B13+8.5446*10^-4
C14	=B14+8.5446*10^-4
D2	{=AND(A2>$B$2:$B$301,A2<$C$2:$C$301)}
D3	{=AND(A3>$B$2:$B$301,A3<$C$2:$C$301)}
D4	{=AND(A4>$B$2:$B$301,A4<$C$2:$C$301)}
D5	{=AND(A5>$B$2:$B$301,A5<$C$2:$C$301)}
D6	{=AND(A6>$B$2:$B$301,A6<$C$2:$C$301)}
D7	{=AND(A7>$B$2:$B$301,A7<$C$2:$C$301)}
D8	{=AND(A8>$B$2:$B$301,A8<$C$2:$C$301)}
D9	{=AND(A9>$B$2:$B$301,A9<$C$2:$C$301)}
D10	{=AND(A10>$B$2:$B$301,A10<$C$2:$C$301)}
D11	{=AND(A11>$B$2:$B$301,A11<$C$2:$C$301)}
D12	{=AND(A12>$B$2:$B$301,A12<$C$2:$C$301)}
D13	{=AND(A13>$B$2:$B$301,A13<$C$2:$C$301)}
D14	{=AND(A14>$B$2:$B$301,A14<$C$2:$C$301)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[bschwartz]

Correction. Try this formula in D2 and fill down:

=--(A2>$B$2:$B$301)*--(A2<$C$2:$C$301)

 

[say4nything]

@Bschwartz - possibly... It shouldn't occur very often... maybe every hundred years give or take. thanks

 

[bschwartz]

You can test it by manually changing some values to include column A values in their range.