Array formula with IF

[Guest]

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

 

[Johz]

Try this:
IF(MAX(B3:E3)-A3>=3,1,0)
this selects the maximum value in the range and then tests that.

 

[Guest]

.. 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)

 

[Mike Woodhouse]

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

 

[Guest]

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]

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-A33)<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