Velocity Reviews > Excel Spreadsheet Formula

Guest
Posts: n/a

 11-14-2005
BIGEYE wrote:

> "Blinky the Shark" <(E-Mail Removed)> wrote in message
> news:Xns970E8BFF9E0D5bnooz@130.133.1.4...
>> BIGEYE wrote:
>>
>>> "Blinky the Shark" <(E-Mail Removed)> 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.

--

BIGEYE
Guest
Posts: n/a

 11-14-2005
"Blinky the Shark" <(E-Mail Removed)> wrote in message
news:Xns970E93EBC9B77bnooz@130.133.1.4...
> BIGEYE wrote:
>
>> "Blinky the Shark" <(E-Mail Removed)> wrote in message
>> news:Xns970E8BFF9E0D5bnooz@130.133.1.4...
>>> BIGEYE wrote:
>>>
>>>> "Blinky the Shark" <(E-Mail Removed)> 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.

Guest
Posts: n/a

 11-14-2005
BIGEYE wrote:

> "Blinky the Shark" <(E-Mail Removed)> wrote in message
> news:Xns970E93EBC9B77bnooz@130.133.1.4...
>> BIGEYE wrote:
>>
>>> "Blinky the Shark" <(E-Mail Removed)> wrote in message
>>> news:Xns970E8BFF9E0D5bnooz@130.133.1.4...
>>>> BIGEYE wrote:
>>>>
>>>>> "Blinky the Shark" <(E-Mail Removed)> 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.

--

why?
Guest
Posts: n/a

 11-14-2005

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

Guest
Posts: n/a

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

--