I cannot work out what is wrong with my formula: =IF(B3:E3-A3=3,1,0)
I would like if any of the values in the range are greater than 3 more than
the value of A3 a "1" to be returned as I eventually will use the formula for
conditional formatting. When I enter this formula it only seems to take one
cell (usually the 1st one) in the range into account. I have tried entering
this as an array {=IF(B3:E3-A3=3,1,0)} with the same result. My hunch is my
"-A3" part is wrong. Help would be much appreciated. Thank you

.. as I eventually will use the formula for conditional formatting.

This should do it for your underlying CF intent
Select B3:F3 (B3 active), then apply CF using Formula is:
=AND($A3<"",B3-3=$A3)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"rosysnozzy" wrote:
I cannot work out what is wrong with my formula: =IF(B3:E3-A3=3,1,0)
I would like if any of the values in the range are greater than 3 more than
the value of A3 a "1" to be returned as I eventually will use the formula for
conditional formatting. When I enter this formula it only seems to take one
cell (usually the 1st one) in the range into account. I have tried entering
this as an array {=IF(B3:E3-A3=3,1,0)} with the same result. My hunch is my
"-A3" part is wrong. Help would be much appreciated. Thank you

On May 10, 8:04 am, rosysnozzy
wrote:
I cannot work out what is wrong with my formula: =IF(B3:E3-A3=3,1,0)
I would like if any of the values in the range are greater than 3 more than
the value of A3 a "1" to be returned as I eventually will use the formula for
conditional formatting. When I enter this formula it only seems to take one
cell (usually the 1st one) in the range into account. I have tried entering
this as an array {=IF(B3:E3-A3=3,1,0)} with the same result. My hunch is my
"-A3" part is wrong. Help would be much appreciated. Thank you

Are you entering the formula into an array? So, assuming you want the
results in row 4, beneath the B3:E3 values, you select cells B4 to E4,
type in your formula and control-shift-enter. I got this:


A B D C E
3: 1 2 3 4 5
4: 0 0 1 1


- Mike

Mike and Max,

Thanks for your help, but I still have some problems. Basically what I am
trying to achieve is a row of numbers which all must be at least 3 minutes
apart from each other, and if one is not, it will turn a different colour to
indicate it is incorrect. The problem I think with your answer Max is that
the formula only takes A3 into account, which is fine, but I could not then
allow the range B3:E3 to cross reference with the rest of the range too. With
Mike's answer, I am only interested in a single result, not multiples, that
being whether the whole range is 3 minutes apart, therefore correct. I hope
you can both continue to help, thank you :-)

"Mike Woodhouse" wrote:

On May 10, 8:04 am, rosysnozzy
wrote:
I cannot work out what is wrong with my formula: =IF(B3:E3-A3=3,1,0)
I would like if any of the values in the range are greater than 3 more than
the value of A3 a "1" to be returned as I eventually will use the formula for
conditional formatting. When I enter this formula it only seems to take one
cell (usually the 1st one) in the range into account. I have tried entering
this as an array {=IF(B3:E3-A3=3,1,0)} with the same result. My hunch is my
"-A3" part is wrong. Help would be much appreciated. Thank you

Are you entering the formula into an array? So, assuming you want the
results in row 4, beneath the B3:E3 values, you select cells B4 to E4,
type in your formula and control-shift-enter. I got this:


A B D C E
3: 1 2 3 4 5
4: 0 0 1 1


- Mike

On May 10, 10:35 am, rosysnozzy
wrote:
Mike and Max,

Thanks for your help, but I still have some problems. Basically what I am
trying to achieve is a row of numbers which all must be at least 3 minutes
apart from each other, and if one is not, it will turn a different colour to
indicate it is incorrect. The problem I think with your answer Max is that
the formula only takes A3 into account, which is fine, but I could not then
allow the range B3:E3 to cross reference with the rest of the range too. With
Mike's answer, I am only interested in a single result, not multiples, that
being whether the whole range is 3 minutes apart, therefore correct. I hope
you can both continue to help, thank you :-)


Sorry - I've been missing for a few days (life, work, stuff like
that).

I think I get it now. How about this, if you haven't already found it
yourself:

{=IF(MIN(B3:E3-A3:D3)<3,"Bad","Good")}

So it calculates an array of intervals, B-A, C-B and so on, then
checks for the smallest, returning "Bad" if there is an interval less
than 3.

Mike