A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Cell text based on 4 condition test



 
 
Thread Tools Display Modes
  #1  
Old November 16th 05, 03:40 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: n/a
Default Cell text based on 4 condition test

Excel 2003

I have 3 cells that I need to test for 4 possible conditions, and want to
have a nearby cell return text based on the test results:

Column C Column D
1 Realty 1 (can be any number 1 to 4)
2 Personal 0 (can be any number 0 to 3)
3 Business 0 (can be any number 0 to 10)

In a nearby cell, I'd like to have text returned based on the values entered
in cells D1, D2, and D3.

(D1 will always be at least one, so the text will always start with "Real
Estate")

Here are the conditions:
If D2 = 0 and D3 = 0, the text should read only "Real Estate"
If D2 > 0 and D3 = 0, the text should read "Real Estate & Personal Property"
if D2 = 0 and D3 >0, the text output should be " Real Estate and Business
Interests"

lastly,
If D2 > 0 and D3 > 0, the text should read "Real Estate, Personal Property,
and Business Interests"

I know there is probably a mega formula involving IFs, ANDs, ORs, or
AREYOUKIDDINGs lurking in here, but I can't get my arms around it. I'd like
to avoid vba coding this one, but I will do that if someone has an elegant
solution to offer :-)

As always, thanks in advance!
BW


Ads
  #2  
Old November 16th 05, 04:14 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: n/a
Default Cell text based on 4 condition test

On Tue, 15 Nov 2005 22:40:09 -0500, "Bob Wall" > wrote:

>Excel 2003
>
>I have 3 cells that I need to test for 4 possible conditions, and want to
>have a nearby cell return text based on the test results:
>
> Column C Column D
>1 Realty 1 (can be any number 1 to 4)
>2 Personal 0 (can be any number 0 to 3)
>3 Business 0 (can be any number 0 to 10)
>
>In a nearby cell, I'd like to have text returned based on the values entered
>in cells D1, D2, and D3.
>
>(D1 will always be at least one, so the text will always start with "Real
>Estate")
>
>Here are the conditions:
>If D2 = 0 and D3 = 0, the text should read only "Real Estate"
>If D2 > 0 and D3 = 0, the text should read "Real Estate & Personal Property"
>if D2 = 0 and D3 >0, the text output should be " Real Estate and Business
>Interests"
>
>lastly,
>If D2 > 0 and D3 > 0, the text should read "Real Estate, Personal Property,
>and Business Interests"
>


Going literally, as you have written it:

=IF(AND(D2=0,D3=0),"Real Estate",
IF(AND(D2>0,D3=0),"Real Estate & Personal Property",
IF(AND(D2=0,D3>0)," Real Estate and Business Interests",
IF(AND(D2>0,D3>0), "Real Estate, Personal Property, and Business interests"))))



--ron
  #3  
Old November 16th 05, 04:42 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: n/a
Default Cell text based on 4 condition test

Hi!

Try this:

=IF(AND(COUNT(D13)=3,D2=0,D3=0),"Real
Estate",IF(AND(COUNT(D13)=3,D2>0,D3=0),"Real Estate and Personal
Property",IF(AND(COUNT(D13)=3,D2=0,D3>0),"Real Estate and Business
Interests",IF(COUNTIF(D13,">0")=3,"Real Estate, Personal Property, and
Business Interests",""))))

Biff

"Bob Wall" > wrote in message
...
> Excel 2003
>
> I have 3 cells that I need to test for 4 possible conditions, and want to
> have a nearby cell return text based on the test results:
>
> Column C Column D
> 1 Realty 1 (can be any number 1 to 4)
> 2 Personal 0 (can be any number 0 to 3)
> 3 Business 0 (can be any number 0 to 10)
>
> In a nearby cell, I'd like to have text returned based on the values
> entered in cells D1, D2, and D3.
>
> (D1 will always be at least one, so the text will always start with "Real
> Estate")
>
> Here are the conditions:
> If D2 = 0 and D3 = 0, the text should read only "Real Estate"
> If D2 > 0 and D3 = 0, the text should read "Real Estate & Personal
> Property"
> if D2 = 0 and D3 >0, the text output should be " Real Estate and Business
> Interests"
>
> lastly,
> If D2 > 0 and D3 > 0, the text should read "Real Estate, Personal
> Property, and Business Interests"
>
> I know there is probably a mega formula involving IFs, ANDs, ORs, or
> AREYOUKIDDINGs lurking in here, but I can't get my arms around it. I'd
> like to avoid vba coding this one, but I will do that if someone has an
> elegant solution to offer :-)
>
> As always, thanks in advance!
> BW
>



  #4  
Old November 16th 05, 07:34 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: n/a
Default Cell text based on 4 condition test

Thanks gentlemen, works perfectly. You guys are awesome!
BW

"Biff" > wrote in message
...
> Hi!
>
> Try this:
>
> =IF(AND(COUNT(D13)=3,D2=0,D3=0),"Real
> Estate",IF(AND(COUNT(D13)=3,D2>0,D3=0),"Real Estate and Personal
> Property",IF(AND(COUNT(D13)=3,D2=0,D3>0),"Real Estate and Business
> Interests",IF(COUNTIF(D13,">0")=3,"Real Estate, Personal Property, and
> Business Interests",""))))
>
> Biff
>
> "Bob Wall" > wrote in message
> ...
>> Excel 2003
>>
>> I have 3 cells that I need to test for 4 possible conditions, and want to
>> have a nearby cell return text based on the test results:
>>
>> Column C Column D
>> 1 Realty 1 (can be any number 1 to 4)
>> 2 Personal 0 (can be any number 0 to 3)
>> 3 Business 0 (can be any number 0 to 10)
>>
>> In a nearby cell, I'd like to have text returned based on the values
>> entered in cells D1, D2, and D3.
>>
>> (D1 will always be at least one, so the text will always start with "Real
>> Estate")
>>
>> Here are the conditions:
>> If D2 = 0 and D3 = 0, the text should read only "Real Estate"
>> If D2 > 0 and D3 = 0, the text should read "Real Estate & Personal
>> Property"
>> if D2 = 0 and D3 >0, the text output should be " Real Estate and Business
>> Interests"
>>
>> lastly,
>> If D2 > 0 and D3 > 0, the text should read "Real Estate, Personal
>> Property, and Business Interests"
>>
>> I know there is probably a mega formula involving IFs, ANDs, ORs, or
>> AREYOUKIDDINGs lurking in here, but I can't get my arms around it. I'd
>> like to avoid vba coding this one, but I will do that if someone has an
>> elegant solution to offer :-)
>>
>> As always, thanks in advance!
>> BW
>>

>
>



 




Thread Tools
Display Modes

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

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Text in formula bar is not displaying in cell Mike Excel Discussion (Misc queries) 0 August 29th 05 09:47 PM
Hit enter in cell & move text down in cell, not go to cell below. raoul_duke Excel Worksheet Functions 3 August 26th 05 06:17 PM
Possible Lookup Table Karen Excel Worksheet Functions 5 June 8th 05 09:43 PM
make a cell empty based on condition mpierre Charts and Charting in Excel 2 December 29th 04 01:01 PM
I am trying to link based on a text value instead of cell position John Links and Linking in Excel 3 December 3rd 04 06:29 PM


All times are GMT +1. The time now is 11:44 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.