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 |
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(D20,D3=0),"Real Estate & Personal Property", IF(AND(D2=0,D30)," Real Estate and Business Interests", IF(AND(D20,D30), "Real Estate, Personal Property, and Business interests")))) --ron |
Cell text based on 4 condition test
Hi!
Try this: =IF(AND(COUNT(D1:D3)=3,D2=0,D3=0),"Real Estate",IF(AND(COUNT(D1:D3)=3,D20,D3=0),"Real Estate and Personal Property",IF(AND(COUNT(D1:D3)=3,D2=0,D30),"Real Estate and Business Interests",IF(COUNTIF(D1:D3,"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 |
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(D1:D3)=3,D2=0,D3=0),"Real Estate",IF(AND(COUNT(D1:D3)=3,D20,D3=0),"Real Estate and Personal Property",IF(AND(COUNT(D1:D3)=3,D2=0,D30),"Real Estate and Business Interests",IF(COUNTIF(D1:D3,"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 |
All times are GMT +1. The time now is 02:49 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com