Discussion in 'Computer Support' started by BIGEYE, Nov 14, 2005.

1. ### BIGEYEGuest

Just started using Excel 2003, used to use Lotus. Can anyone tell me how to
input a formula for this.
Three cells C1, D1, E1, each cell has a value.
Three other cells K1, L1, M1, each with values.
Another cell to test the condition as follows:
If K1<C1 and L1<D1 and M1<E1, put YES into the last cell. If any of the
conditions are false, put NO into the last cell.
Appreciate any help.
TIA

BIGEYE, Nov 14, 2005

BIGEYE wrote:

> Just started using Excel 2003, used to use Lotus. Can anyone
> tell me how to input a formula for this.
> Three cells C1, D1, E1, each cell has a value.
> Three other cells K1, L1, M1, each with values.
> Another cell to test the condition as follows:
> If K1<C1 and L1<D1 and M1<E1, put YES into the last cell. If any
> of the conditions are false, put NO into the last cell.
> Appreciate any help.
> TIA

=IF(K1<C1,IF(L1<D1,IF(M1<E1,"YES"),"NO"),"NO")

--

Blinky the Shark, Nov 14, 2005

3. ### Lord Gazwad of GranthamGuest

Blinky the Shark, <>, the congealed, sugar-coated fairy,
and dealer in salted cow manure, bellowed:

> BIGEYE wrote:
>
>> Just started using Excel 2003, used to use Lotus. Can anyone
>> tell me how to input a formula for this.
>> Three cells C1, D1, E1, each cell has a value.
>> Three other cells K1, L1, M1, each with values.
>> Another cell to test the condition as follows:
>> If K1<C1 and L1<D1 and M1<E1, put YES into the last cell. If any
>> of the conditions are false, put NO into the last cell.
>> Appreciate any help.
>> TIA

>
> =IF(K1<C1,IF(L1<D1,IF(M1<E1,"YES"),"NO"),"NO")

BWAHAHAHAHAHAHAHA

--
For my own part, I have never had a thought which I could not set down
in words with even more distinctness than that with which I conceived
it. There is, however, a class of fancies of exquisite delicacy which
are not thoughts, and to which as yet I have found it absolutely
impossible to adapt to language. These fancies arise in the soul, alas
how rarely. Only at epochs of most intense tranquillity, when the
bodily and mental health are in perfection. And at those weird points
of time, where the confines of the waking world blend with the world of
dreams. And so I captured this fancy, where all that we see, or seem,
is but a dream within a dream.

Lord Gazwad of Grantham, Nov 14, 2005
4. ### why?Guest

On Mon, 14 Nov 2005 19:45:15 -0000, "BIGEYE" <address@is[invalid].ok>
wrote:

>Just started using Excel 2003, used to use Lotus. Can anyone tell me how to
>input a formula for this.
>Three cells C1, D1, E1, each cell has a value.
>Three other cells K1, L1, M1, each with values.
>Another cell to test the condition as follows:
>If K1<C1 and L1<D1 and M1<E1, put YES into the last cell. If any of the
>conditions are false, put NO into the last cell.

IIRC same as Supercalc and Lotus , although it's been a while.

=IF(test1,result1true,result1false)

expand this out to

=IF(test1,IF(test2,IF(test3,result3true,result3false),result2false),result1false)

You simply nest a further test, where you need it.

It works fine for up to 6 then it's tricky to keep things straight.

Run XL , press F1 , for Help.
IF_worksheet_function

Me

why?, Nov 14, 2005
5. ### BIGEYEGuest

"Blinky the Shark" <> wrote in message
news:Xns970E7C97DBC93bnooz@130.133.1.4...
> BIGEYE wrote:
>
>> Just started using Excel 2003, used to use Lotus. Can anyone
>> tell me how to input a formula for this.
>> Three cells C1, D1, E1, each cell has a value.
>> Three other cells K1, L1, M1, each with values.
>> Another cell to test the condition as follows:
>> If K1<C1 and L1<D1 and M1<E1, put YES into the last cell. If any
>> of the conditions are false, put NO into the last cell.
>> Appreciate any help.
>> TIA

>
> =IF(K1<C1,IF(L1<D1,IF(M1<E1,"YES"),"NO"),"NO")
>
> --
> Killing all Google Groups posts.

Thanks for that, now what if the expression was OR instead of AND.
If K1<C1 or L1<D1 or M1<E1, put YES into the last cell. If any of the
conditions are false, put NO into the last cell.
TIA

BIGEYE, Nov 14, 2005

BIGEYE wrote:

> "Blinky the Shark" <> wrote in message
> news:Xns970E7C97DBC93bnooz@130.133.1.4...
>> BIGEYE wrote:
>>
>>> Just started using Excel 2003, used to use Lotus. Can anyone
>>> tell me how to input a formula for this.
>>> Three cells C1, D1, E1, each cell has a value.
>>> Three other cells K1, L1, M1, each with values.
>>> Another cell to test the condition as follows:
>>> If K1<C1 and L1<D1 and M1<E1, put YES into the last cell. If
>>> any of the conditions are false, put NO into the last cell.
>>> Appreciate any help.
>>> TIA

>>
>> =IF(K1<C1,IF(L1<D1,IF(M1<E1,"YES"),"NO"),"NO")

>
> Thanks for that, now what if the expression was OR instead of
> AND. If K1<C1 or L1<D1 or M1<E1, put YES into the last cell. If
> any of the conditions are false, put NO into the last cell.
> TIA

Sorry, only the first joint is free.

(Just kidding; but what I gave you is all the Excel I feel like
doing today. I'm an Open Office user (Linux) (I'm only here in
Windows on a backup computer while I'm rebuilding my Linux box),
and MS products give me hives. <g>)

--

Blinky the Shark, Nov 14, 2005

BIGEYE wrote:

> "Blinky the Shark" <> wrote in message
> news:Xns970E7C97DBC93bnooz@130.133.1.4...
>> BIGEYE wrote:
>>
>>> Just started using Excel 2003, used to use Lotus. Can anyone
>>> tell me how to input a formula for this.
>>> Three cells C1, D1, E1, each cell has a value.
>>> Three other cells K1, L1, M1, each with values.
>>> Another cell to test the condition as follows:
>>> If K1<C1 and L1<D1 and M1<E1, put YES into the last cell. If
>>> any of the conditions are false, put NO into the last cell.
>>> Appreciate any help.
>>> TIA

>>
>> =IF(K1<C1,IF(L1<D1,IF(M1<E1,"YES"),"NO"),"NO")
>> --

>
> Thanks for that, now what if the expression was OR instead of
> AND. If K1<C1 or L1<D1 or M1<E1, put YES into the last cell. If
> any of the conditions are false, put NO into the last cell.
> TIA

Second response: oh, what the heck...

=OR(K1<C1,L1<D1,M1<E1)

Actually, you could do the same for your first quesiton,
substituting "AND" for "OR". I didn't think about that until you
asked the "OR" question -- I'm so used to using IF statements that I
just automatically went that way for your first question.

Now -- start looking in Excel's function help when you need this
kinda stuff, eh? Thanks.

--

Blinky the Shark, Nov 14, 2005

why? wrote:

>
> On Mon, 14 Nov 2005 19:45:15 -0000, "BIGEYE"
>
>>Just started using Excel 2003, used to use Lotus. Can anyone
>>tell me how to input a formula for this.
>>Three cells C1, D1, E1, each cell has a value.
>>Three other cells K1, L1, M1, each with values.
>>Another cell to test the condition as follows:
>>If K1<C1 and L1<D1 and M1<E1, put YES into the last cell. If any
>>of the conditions are false, put NO into the last cell.

>
> IIRC same as Supercalc and Lotus , although it's been a while.
>
>
>=IF(test1,result1true,result1false)
>
> expand this out to
>
>=IF(test1,IF(test2,IF

(test3,result3true,result3false),result2false
>),result1false)

It's interesting that like me, you didn't suggest the AND
function, either. See my responses to OP -- I'm just used to

--

Blinky the Shark, Nov 14, 2005
9. ### BIGEYEGuest

"Blinky the Shark" <> wrote in message
news:Xns970E8BFF9E0D5bnooz@130.133.1.4...
> BIGEYE wrote:
>
>> "Blinky the Shark" <> wrote in message
>> news:Xns970E7C97DBC93bnooz@130.133.1.4...
>>> BIGEYE wrote:
>>>
>>>> Just started using Excel 2003, used to use Lotus. Can anyone
>>>> tell me how to input a formula for this.
>>>> Three cells C1, D1, E1, each cell has a value.
>>>> Three other cells K1, L1, M1, each with values.
>>>> Another cell to test the condition as follows:
>>>> If K1<C1 and L1<D1 and M1<E1, put YES into the last cell. If
>>>> any of the conditions are false, put NO into the last cell.
>>>> Appreciate any help.
>>>> TIA
>>>
>>> =IF(K1<C1,IF(L1<D1,IF(M1<E1,"YES"),"NO"),"NO")
>>> --

>>
>> Thanks for that, now what if the expression was OR instead of
>> AND. If K1<C1 or L1<D1 or M1<E1, put YES into the last cell. If
>> any of the conditions are false, put NO into the last cell.
>> TIA

>
> Second response: oh, what the heck...
>
> =OR(K1<C1,L1<D1,M1<E1)
>
> Actually, you could do the same for your first quesiton,
> substituting "AND" for "OR". I didn't think about that until you
> asked the "OR" question -- I'm so used to using IF statements that I
> just automatically went that way for your first question.
>
> Now -- start looking in Excel's function help when you need this
> kinda stuff, eh? Thanks.
>
>
> --
> Killing all Google Groups posts.

Going back to the AND, it works OK when all conditions are true, YES gets
put in the formula cell. But if one of them becomes false, then FALSE gets
put in the cell instead of NO.
If I use AND as in your second suggestion, I end up with #value in the
formula cell.

BIGEYE, Nov 14, 2005
10. ### Lord Gazwad of GranthamGuest

BIGEYE, <address@is[invalid].ok>, the oblique, wayward moldwarp, and
employee responsible for building high stone walls from small pebbles using
dog shit for mortar, buzzed:

> "Blinky the Shark" <> wrote in message
> news:Xns970E8BFF9E0D5bnooz@130.133.1.4...
>> BIGEYE wrote:
>>
>>> "Blinky the Shark" <> wrote in message
>>> news:Xns970E7C97DBC93bnooz@130.133.1.4...
>>>> BIGEYE wrote:
>>>>
>>>>> Just started using Excel 2003, used to use Lotus. Can anyone
>>>>> tell me how to input a formula for this.
>>>>> Three cells C1, D1, E1, each cell has a value.
>>>>> Three other cells K1, L1, M1, each with values.
>>>>> Another cell to test the condition as follows:
>>>>> If K1<C1 and L1<D1 and M1<E1, put YES into the last cell. If
>>>>> any of the conditions are false, put NO into the last cell.
>>>>> Appreciate any help.
>>>>> TIA
>>>>
>>>> =IF(K1<C1,IF(L1<D1,IF(M1<E1,"YES"),"NO"),"NO")
>>>> --
>>>
>>> Thanks for that, now what if the expression was OR instead of
>>> AND. If K1<C1 or L1<D1 or M1<E1, put YES into the last cell. If
>>> any of the conditions are false, put NO into the last cell.
>>> TIA

>>
>> Second response: oh, what the heck...
>>
>> =OR(K1<C1,L1<D1,M1<E1)
>>
>> Actually, you could do the same for your first quesiton,
>> substituting "AND" for "OR". I didn't think about that until you
>> asked the "OR" question -- I'm so used to using IF statements that I
>> just automatically went that way for your first question.
>>
>> Now -- start looking in Excel's function help when you need this
>> kinda stuff, eh? Thanks.
>>
>>
>> --
>> Killing all Google Groups posts.

>
>
> Going back to the AND, it works OK when all conditions are true, YES
> gets put in the formula cell. But if one of them becomes false, then
> FALSE gets put in the cell instead of NO.
> If I use AND as in your second suggestion, I end up with #value in the
> formula cell.

What do you expect when you listen to a dumb **** like that, eh?

--
For my own part, I have never had a thought which I could not set down
in words with even more distinctness than that with which I conceived
it. There is, however, a class of fancies of exquisite delicacy which
are not thoughts, and to which as yet I have found it absolutely
impossible to adapt to language. These fancies arise in the soul, alas
how rarely. Only at epochs of most intense tranquillity, when the
bodily and mental health are in perfection. And at those weird points
of time, where the confines of the waking world blend with the world of
dreams. And so I captured this fancy, where all that we see, or seem,
is but a dream within a dream.

Lord Gazwad of Grantham, Nov 14, 2005

BIGEYE wrote:

> "Blinky the Shark" <> wrote in message
> news:Xns970E8BFF9E0D5bnooz@130.133.1.4...
>> BIGEYE wrote:
>>
>>> "Blinky the Shark" <> wrote in message
>>> news:Xns970E7C97DBC93bnooz@130.133.1.4...
>>>> BIGEYE wrote:
>>>>
>>>>> Just started using Excel 2003, used to use Lotus. Can anyone
>>>>> tell me how to input a formula for this.
>>>>> Three cells C1, D1, E1, each cell has a value.
>>>>> Three other cells K1, L1, M1, each with values.
>>>>> Another cell to test the condition as follows:
>>>>> If K1<C1 and L1<D1 and M1<E1, put YES into the last cell. If
>>>>> any of the conditions are false, put NO into the last cell.
>>>>> Appreciate any help.
>>>>> TIA
>>>>
>>>> =IF(K1<C1,IF(L1<D1,IF(M1<E1,"YES"),"NO"),"NO")
>>>> --
>>>
>>> Thanks for that, now what if the expression was OR instead of
>>> AND. If K1<C1 or L1<D1 or M1<E1, put YES into the last cell.
>>> If any of the conditions are false, put NO into the last cell.
>>> TIA

>>
>> Second response: oh, what the heck...
>>
>> =OR(K1<C1,L1<D1,M1<E1)
>>
>> Actually, you could do the same for your first quesiton,
>> substituting "AND" for "OR". I didn't think about that until
>> you asked the "OR" question -- I'm so used to using IF
>> statements that I just automatically went that way for your
>> first question.
>>
>> Now -- start looking in Excel's function help when you need
>> this kinda stuff, eh? Thanks.
>>
>>
>> --
>> Killing all Google Groups posts.

>
>
> Going back to the AND, it works OK when all conditions are true,
> YES gets put in the formula cell. But if one of them becomes
> false, then FALSE gets put in the cell instead of NO.
> If I use AND as in your second suggestion, I end up with #value
> in the formula cell.

Works here.

So use my first solution for your original question - the IF
statements.

--

Blinky the Shark, Nov 14, 2005
12. ### BIGEYEGuest

"Blinky the Shark" <> wrote in message
news:Xns970E93EBC9B77bnooz@130.133.1.4...
> BIGEYE wrote:
>
>> "Blinky the Shark" <> wrote in message
>> news:Xns970E8BFF9E0D5bnooz@130.133.1.4...
>>> BIGEYE wrote:
>>>
>>>> "Blinky the Shark" <> wrote in message
>>>> news:Xns970E7C97DBC93bnooz@130.133.1.4...
>>>>> BIGEYE wrote:
>>>>>
>>>>>> Just started using Excel 2003, used to use Lotus. Can anyone
>>>>>> tell me how to input a formula for this.
>>>>>> Three cells C1, D1, E1, each cell has a value.
>>>>>> Three other cells K1, L1, M1, each with values.
>>>>>> Another cell to test the condition as follows:
>>>>>> If K1<C1 and L1<D1 and M1<E1, put YES into the last cell. If
>>>>>> any of the conditions are false, put NO into the last cell.
>>>>>> Appreciate any help.
>>>>>> TIA
>>>>>
>>>>> =IF(K1<C1,IF(L1<D1,IF(M1<E1,"YES"),"NO"),"NO")
>>>>> --
>>>>
>>>> Thanks for that, now what if the expression was OR instead of
>>>> AND. If K1<C1 or L1<D1 or M1<E1, put YES into the last cell.
>>>> If any of the conditions are false, put NO into the last cell.
>>>> TIA
>>>
>>> Second response: oh, what the heck...
>>>
>>> =OR(K1<C1,L1<D1,M1<E1)
>>>
>>> Actually, you could do the same for your first quesiton,
>>> substituting "AND" for "OR". I didn't think about that until
>>> you asked the "OR" question -- I'm so used to using IF
>>> statements that I just automatically went that way for your
>>> first question.
>>>
>>> Now -- start looking in Excel's function help when you need
>>> this kinda stuff, eh? Thanks.
>>>
>>>
>>> --
>>> Killing all Google Groups posts.

>>
>>
>> Going back to the AND, it works OK when all conditions are true,
>> YES gets put in the formula cell. But if one of them becomes
>> false, then FALSE gets put in the cell instead of NO.
>> If I use AND as in your second suggestion, I end up with #value
>> in the formula cell.

>
> Works here.
>
> So use my first solution for your original question - the IF
> statements.
>
> --
> Killing all Google Groups posts.

Managed to get it working. Thanks.

BIGEYE, Nov 14, 2005

BIGEYE wrote:

> "Blinky the Shark" <> wrote in message
> news:Xns970E93EBC9B77bnooz@130.133.1.4...
>> BIGEYE wrote:
>>
>>> "Blinky the Shark" <> wrote in message
>>> news:Xns970E8BFF9E0D5bnooz@130.133.1.4...
>>>> BIGEYE wrote:
>>>>
>>>>> "Blinky the Shark" <> wrote in message
>>>>> news:Xns970E7C97DBC93bnooz@130.133.1.4...
>>>>>> BIGEYE wrote:
>>>>>>
>>>>>>> Just started using Excel 2003, used to use Lotus. Can
>>>>>>> anyone tell me how to input a formula for this.
>>>>>>> Three cells C1, D1, E1, each cell has a value.
>>>>>>> Three other cells K1, L1, M1, each with values.
>>>>>>> Another cell to test the condition as follows:
>>>>>>> If K1<C1 and L1<D1 and M1<E1, put YES into the last cell.
>>>>>>> If any of the conditions are false, put NO into the last
>>>>>>> cell. Appreciate any help.
>>>>>>> TIA
>>>>>>
>>>>>> =IF(K1<C1,IF(L1<D1,IF(M1<E1,"YES"),"NO"),"NO")
>>>>>> --
>>>>>
>>>>> Thanks for that, now what if the expression was OR instead
>>>>> of AND. If K1<C1 or L1<D1 or M1<E1, put YES into the last
>>>>> cell. If any of the conditions are false, put NO into the
>>>>> last cell. TIA
>>>>
>>>> Second response: oh, what the heck...
>>>>
>>>> =OR(K1<C1,L1<D1,M1<E1)
>>>>
>>>> Actually, you could do the same for your first quesiton,
>>>> substituting "AND" for "OR". I didn't think about that until
>>>> you asked the "OR" question -- I'm so used to using IF
>>>> statements that I just automatically went that way for your
>>>> first question.
>>>>
>>>> Now -- start looking in Excel's function help when you need
>>>> this kinda stuff, eh? Thanks.
>>>
>>> Going back to the AND, it works OK when all conditions are
>>> true, YES gets put in the formula cell. But if one of them
>>> becomes false, then FALSE gets put in the cell instead of NO.
>>> If I use AND as in your second suggestion, I end up with
>>> #value in the formula cell.

>>
>> Works here.
>>
>> So use my first solution for your original question - the IF
>> statements.

>
> Managed to get it working. Thanks.

You're welcome.

--

Blinky the Shark, Nov 14, 2005
14. ### why?Guest

On 14 Nov 2005 21:48:32 GMT, Blinky the Shark wrote:

>why? wrote:
>
>>
>> On Mon, 14 Nov 2005 19:45:15 -0000, "BIGEYE"
>>
>>>Just started using Excel 2003, used to use Lotus. Can anyone
>>>tell me how to input a formula for this.
>>>Three cells C1, D1, E1, each cell has a value.
>>>Three other cells K1, L1, M1, each with values.
>>>Another cell to test the condition as follows:
>>>If K1<C1 and L1<D1 and M1<E1, put YES into the last cell. If any
>>>of the conditions are false, put NO into the last cell.

<snip>

>>=IF(test1,IF(test2,IF

>(test3,result3true,result3false),result2false
>>),result1false)

>
>It's interesting that like me, you didn't suggest the AND
>function, either. See my responses to OP -- I'm just used to

As ugly as nested IF is written out as I did I found more people
understand it that way. When trying *logic* it's an issue

It's not too difficult for 3 tests but that's as about as far as I ever
used it in that form, except once in a tutorial.

If I saw the other posts earlier, was doing a backup (very important) I
might have changed the answer as well.

Me

why?, Nov 14, 2005

why? wrote:

>
> On 14 Nov 2005 21:48:32 GMT, Blinky the Shark wrote:
>
>>why? wrote:
>>
>>>
>>> On Mon, 14 Nov 2005 19:45:15 -0000, "BIGEYE"
>>>
>>>>Just started using Excel 2003, used to use Lotus. Can anyone
>>>>tell me how to input a formula for this.
>>>>Three cells C1, D1, E1, each cell has a value.
>>>>Three other cells K1, L1, M1, each with values.
>>>>Another cell to test the condition as follows:
>>>>If K1<C1 and L1<D1 and M1<E1, put YES into the last cell. If
>>>>any of the conditions are false, put NO into the last cell.

>
> <snip>
>
>>>=IF(test1,IF(test2,IF
>>>(test3,result3true,result3false),result2false ),result1false)

>>
>>It's interesting that like me, you didn't suggest the AND
>>function, either. See my responses to OP -- I'm just used to

>
> As ugly as nested IF is written out as I did I found more people
> understand it that way. When trying *logic* it's an issue

I dunno about that. An AND statment is a lot more intuitive
because of the lack of a need for nesting.

> It's not too difficult for 3 tests but that's as about as far as
> I ever used it in that form, except once in a tutorial.

I think I've gone as far as six levels in Real Life. Flawlessly
logical; and very ugly.

--