What is wrong with this formula?

[Darren]

=IF(B3:K3<=3,"Please enter the reason for 3's and less","Thank You") The
cells B3 through K3 are valid cells.

[Norman Jones]

Hi Darren,

The formula works for me.

The formula is an array formula and should be confirmed with :

control - shift - enter

---
Regards,
Norman


"Darren" <Darren@discussions.microsoft.com> wrote in message
news:56351AB3-8108-40C8-AF82-424EEB250B2A@microsoft.com...
> =IF(B3:K3<=3,"Please enter the reason for 3's and less","Thank You") The
> cells B3 through K3 are valid cells.

[Gary Keramidas]

only works if b3 >3 for me.

--


Gary


"Norman Jones" <normanjones@whereforartthou.com> wrote in message
news:uoKk9ae2FHA.4008@tk2msftngp13.phx.gbl...
> Hi Darren,
>
> The formula works for me.
>
> The formula is an array formula and should be confirmed with :
>
> control - shift - enter
>
> ---
> Regards,
> Norman
>
>
> "Darren" <Darren@discussions.microsoft.com> wrote in message
> news:56351AB3-8108-40C8-AF82-424EEB250B2A@microsoft.com...
>> =IF(B3:K3<=3,"Please enter the reason for 3's and less","Thank You") The
>> cells B3 through K3 are valid cells.
>
>

[Gary Keramidas]

this would evaluate to false if any entry in b3:k3 <=3

=IF(OR(B3<=3,C3<=3,D3<=3,E3<=3,F3<=3,G3<=3,H3<=3,I3<=3,J3<=3,K3<=3),"Please
enter the reason for 3's and less","Thank You")

--


Gary


"Darren" <Darren@discussions.microsoft.com> wrote in message
news:56351AB3-8108-40C8-AF82-424EEB250B2A@microsoft.com...
> =IF(B3:K3<=3,"Please enter the reason for 3's and less","Thank You") The
> cells B3 through K3 are valid cells.

[Darren]

Perfect - that works with a small exception. I though I had it formulated to
check B3 through K3 and if any of those numbers fall at 3 or less, then it
would ask for a reason. The way it is working is only checking B3. Any help
on the getting it to work correctly?

"Norman Jones" wrote:

> Hi Darren,
>
> The formula works for me.
>
> The formula is an array formula and should be confirmed with :
>
> control - shift - enter
>
> ---
> Regards,
> Norman
>
>
> "Darren" <Darren@discussions.microsoft.com> wrote in message
> news:56351AB3-8108-40C8-AF82-424EEB250B2A@microsoft.com...
> > =IF(B3:K3<=3,"Please enter the reason for 3's and less","Thank You") The
> > cells B3 through K3 are valid cells.
>
>
>

[Norman Jones]

Hi Darren,

Instead try:

=IF(MIN(B3:K3)<=3,"Please enter the reason for 3's and less","Thank You")

confirmed with Enter.


---
Regards,
Norman



"Darren" <Darren@discussions.microsoft.com> wrote in message
news:20521EE5-E580-461F-B456-29DF61983956@microsoft.com...
> Perfect - that works with a small exception. I though I had it formulated
> to
> check B3 through K3 and if any of those numbers fall at 3 or less, then it
> would ask for a reason. The way it is working is only checking B3. Any
> help
> on the getting it to work correctly?
>
> "Norman Jones" wrote:
>
>> Hi Darren,
>>
>> The formula works for me.
>>
>> The formula is an array formula and should be confirmed with :
>>
>> control - shift - enter
>>
>> ---
>> Regards,
>> Norman
>>
>>
>> "Darren" <Darren@discussions.microsoft.com> wrote in message
>> news:56351AB3-8108-40C8-AF82-424EEB250B2A@microsoft.com...
>> > =IF(B3:K3<=3,"Please enter the reason for 3's and less","Thank You")
>> > The
>> > cells B3 through K3 are valid cells.
>>
>>
>>

[Darren]

That is it!! Thanks.

"Gary Keramidas" wrote:

> this would evaluate to false if any entry in b3:k3 <=3
>
> =IF(OR(B3<=3,C3<=3,D3<=3,E3<=3,F3<=3,G3<=3,H3<=3,I3<=3,J3<=3,K3<=3),"Please
> enter the reason for 3's and less","Thank You")
>
> --
>
>
> Gary
>
>
> "Darren" <Darren@discussions.microsoft.com> wrote in message
> news:56351AB3-8108-40C8-AF82-424EEB250B2A@microsoft.com...
> > =IF(B3:K3<=3,"Please enter the reason for 3's and less","Thank You") The
> > cells B3 through K3 are valid cells.
>
>
>

[Norman Jones]

Hi Gary,

Quite correct - my error.

Simply:

=IF(MIN(B3:K3)<=3,"Please enter the reason for 3's and less","Thank You")


---
Regards,
Norman



"Gary Keramidas" <GKeramidasATmsn.com> wrote in message
news:e939Qde2FHA.1276@TK2MSFTNGP09.phx.gbl...
> only works if b3 >3 for me.
>
> --
>
>
> Gary
>
>
> "Norman Jones" <normanjones@whereforartthou.com> wrote in message
> news:uoKk9ae2FHA.4008@tk2msftngp13.phx.gbl...
>> Hi Darren,
>>
>> The formula works for me.
>>
>> The formula is an array formula and should be confirmed with :
>>
>> control - shift - enter
>>
>> ---
>> Regards,
>> Norman
>>
>>
>> "Darren" <Darren@discussions.microsoft.com> wrote in message
>> news:56351AB3-8108-40C8-AF82-424EEB250B2A@microsoft.com...
>>> =IF(B3:K3<=3,"Please enter the reason for 3's and less","Thank You")
>>> The
>>> cells B3 through K3 are valid cells.
>>
>>
>
>

[Patrick Molloy]

The replies that you've had all look really intersting.

However, the simple formula
=COUNTIF(B3:K3,"<=3")
returns the number of cells that have a value of 3 or less
you could wrap this in an IF to raise the alert
=IF(COUNTIF(B3:K3,"<=3"),"Explain….","OK")

You could make it more versatile
example 1
=IF(COUNTIF(testrange,"<=3"),"Explain….","OK")
where B3:K3 is range named 'testrange'
if you move or resize the range, the formula will work
example 2
=IF(COUNTIF(INDIRECT(lookup),"<=3"),"Explain….","OK")
where lookup is a named cell containg the value 'B3:K3'

again, this offers adegree of flexibility. edit B3:K3 and your formula still
works so long as its a genuine reference such as B3:D3








"Darren" wrote:

> =IF(B3:K3<=3,"Please enter the reason for 3's and less","Thank You") The
> cells B3 through K3 are valid cells.

[Jim May]

Norman, thanks for the modification. Question however...
Noticed your Original approach indicated a CSE array
formula required,

Your modified one "does not"; Can you explain why
I've been unable (yet) to get my mind around the difference in the above
two.
Tks in Advance.
Jim




"Norman Jones" <normanjones@whereforartthou.com> wrote in message
news:ewchIte2FHA.2268@TK2MSFTNGP15.phx.gbl...
> Hi Darren,
>
> Instead try:
>
> =IF(MIN(B3:K3)<=3,"Please enter the reason for 3's and less","Thank You")
>
> confirmed with Enter.
>
>
> ---
> Regards,
> Norman
>
>
>
> "Darren" <Darren@discussions.microsoft.com> wrote in message
> news:20521EE5-E580-461F-B456-29DF61983956@microsoft.com...
>> Perfect - that works with a small exception. I though I had it formulated
>> to
>> check B3 through K3 and if any of those numbers fall at 3 or less, then
>> it
>> would ask for a reason. The way it is working is only checking B3. Any
>> help
>> on the getting it to work correctly?
>>
>> "Norman Jones" wrote:
>>
>>> Hi Darren,
>>>
>>> The formula works for me.
>>>
>>> The formula is an array formula and should be confirmed with :
>>>
>>> control - shift - enter
>>>
>>> ---
>>> Regards,
>>> Norman
>>>
>>>
>>> "Darren" <Darren@discussions.microsoft.com> wrote in message
>>> news:56351AB3-8108-40C8-AF82-424EEB250B2A@microsoft.com...
>>> > =IF(B3:K3<=3,"Please enter the reason for 3's and less","Thank You")
>>> > The
>>> > cells B3 through K3 are valid cells.
>>>
>>>
>>>
>
>

[Tom Ogilvy]

Min works with a range - you are only interested in a single value contained
in that range

If(rng<=3, ,)

would generate an array of true or false, one for cell in rng based on
whether that cell is less than or equal to 3.

Put some numbers in F1 to F10
select a cell, then put this formula in the formula bar

=IF(F1:F10<=3,1,0)

go to the formula bar and select the F1:F10<=3 portion of the formula and
hit F9

You will see the array. Hit escape.

Now select the whole formula and hit F9

You will see the array of 1 and zeros returned by the IF function. Hit
Escape.

Now put in

=Max(F1:F10)

It returns a single number. So do you want to make a decision on a per
cell basis (array formula) or do you want a decision across a range of
cells.

--
Regards,
Tom Ogilvy


"Jim May" <jmay@cox.net> wrote in message
news:79K7f.21843$OM4.18391@dukeread06...
> Norman, thanks for the modification. Question however...
> Noticed your Original approach indicated a CSE array
> formula required,
>
> Your modified one "does not"; Can you explain why
> I've been unable (yet) to get my mind around the difference in the above
> two.
> Tks in Advance.
> Jim
>
>
>
>
> "Norman Jones" <normanjones@whereforartthou.com> wrote in message
> news:ewchIte2FHA.2268@TK2MSFTNGP15.phx.gbl...
> > Hi Darren,
> >
> > Instead try:
> >
> > =IF(MIN(B3:K3)<=3,"Please enter the reason for 3's and less","Thank
You")
> >
> > confirmed with Enter.
> >
> >
> > ---
> > Regards,
> > Norman
> >
> >
> >
> > "Darren" <Darren@discussions.microsoft.com> wrote in message
> > news:20521EE5-E580-461F-B456-29DF61983956@microsoft.com...
> >> Perfect - that works with a small exception. I though I had it
formulated
> >> to
> >> check B3 through K3 and if any of those numbers fall at 3 or less, then
> >> it
> >> would ask for a reason. The way it is working is only checking B3. Any
> >> help
> >> on the getting it to work correctly?
> >>
> >> "Norman Jones" wrote:
> >>
> >>> Hi Darren,
> >>>
> >>> The formula works for me.
> >>>
> >>> The formula is an array formula and should be confirmed with :
> >>>
> >>> control - shift - enter
> >>>
> >>> ---
> >>> Regards,
> >>> Norman
> >>>
> >>>
> >>> "Darren" <Darren@discussions.microsoft.com> wrote in message
> >>> news:56351AB3-8108-40C8-AF82-424EEB250B2A@microsoft.com...
> >>> > =IF(B3:K3<=3,"Please enter the reason for 3's and less","Thank You")
> >>> > The
> >>> > cells B3 through K3 are valid cells.
> >>>
> >>>
> >>>
> >
> >
>
>