Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Computing > Computer Support > Excel Spreadsheet Formula

Reply
Thread Tools

Excel Spreadsheet Formula

 
 
Blinky the Shark
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")
>>>> --
>>>> Blinky
>>>
>>> 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
>> Killing all Google Groups posts.
>> http://blinkynet.net/comp/uip5.html

>
>
> 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
Killing all Google Groups posts.
http://blinkynet.net/comp/uip5.html
 
Reply With Quote
 
 
 
 
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")
>>>>> --
>>>>> Blinky
>>>>
>>>> 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
>>> Killing all Google Groups posts.
>>> http://blinkynet.net/comp/uip5.html

>>
>>
>> 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
> Killing all Google Groups posts.
> http://blinkynet.net/comp/uip5.html



Managed to get it working. Thanks.


 
Reply With Quote
 
 
 
 
Blinky the Shark
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")
>>>>>> --
>>>>>> Blinky
>>>>>
>>>>> 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
Killing all Google Groups posts.
http://blinkynet.net/comp/uip5.html
 
Reply With Quote
 
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"
>> <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.


<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
>using IF statements, and didn't think about AND until he asked
>about OR in a followup.


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
 
Reply With Quote
 
Blinky the Shark
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"
>>> <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.

>
> <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
>>using IF statements, and didn't think about AND until he asked
>>about OR in a followup.

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


--
Blinky
Killing all Google Groups posts.
http://blinkynet.net/comp/uip5.html
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Spreadsheet Formula Help Steve Freides Computer Support 4 02-15-2009 03:07 PM
Excel Formula =?Utf-8?B?QyBBbmRlcnNvbg==?= Microsoft Certification 1 04-12-2006 02:15 AM
formula for my spreadsheet Jeaneie Computer Support 5 11-30-2004 01:53 PM
Spreadsheet::WriteExcel, Excel formula won't calculate Sven Jungnickel Perl Misc 3 07-28-2004 11:18 PM
Excel 2003 Formula Doug Microsoft Certification 0 02-15-2004 12:18 PM



Advertisments