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.

> .. 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 <rosysno...@discussions.microsoft.com>
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 <rosysno...@discussions.microsoft.com>
> 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 <rosysno...@discussions.microsoft.com>
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-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