Multiple Function same formula

[DCAbound]

I wonder if anyone can help with this. I now know that + is actually equates to OR. I am trying to write an argument that states...

Cell A1 AND B1:B4>=3,"Advance",....

=IF(AND(DP3=1)+SUM(DJ3:DM3)>=3,"Advance",IF(AND(DP3=2)+SUM(DJ3:DM3)>=3,"Established",IF(AND(DP3=3)+SUM(DJ3:DM3)>=2,"Advance",IF(AND(DP3=4)+SUM(DJ3:DM3,DO3)>0,"Latent","Latent"))))

How would I change this?

 

[cyrilbrd]

Hi, do you want TRUE if A1+B1+B2+B3+B4 superior or equal to 3?

 

[mikerickson]

can you describe in words what results you want from what inputs

 

[DCAbound]

I am trying to create decision text for a questionnaire. Responses for columns A-F are 1 or blank since they are answers to a Check all that applies. Column g responses are 1-4 as it is a radio button. Below is the highlighted description of what i need to accomplish.

	A	B	c	d	e	f	g
	EXAMq28a	EXAMq28b	EXAMq28c	EXAMq28d	EXAMq28e	EXAMq28f	EXAMq29
Rep1	1	1	1	1			1	Advance
Rep2	1	1	1				2	Advance
Rep3	1	1					3	Advance
Rep4		1					4	Latent
Rep5		1						Latent
Rep6	1	1	1	1			1	Advance
Rep7		1					2	Latent
Rep8		1					3	Advance
Rep9		1					4	Latent
Rep10								Latent
Rep11								Latent
Rep12							1	Latent
Rep13		1		1	1		1	Advance
Rep14
Rep15
				Q28	Q29
				A:D>=3	1	Advance
				A:D>=3	2	Established
				A:D>=2	3	Emerging
				28F=1 and ELSE	Latent

<!--StartFragment--> <colgroup><col width="69" style="width:69pt"> <col width="69" span="7" style="width:69pt"> <col width="69" style="width:69pt"> </colgroup><tbody>
<!--EndFragment--></tbody>

 

[DCAbound]

These are the text that I need to show once the value is true

Q28	Q29
A:D>=3	1	Advance
A:D>=3	2	Established
A:D>=2	3	Emerging
E=1 and ELSE	4	Latent

<!--StartFragment--> <colgroup><col width="69" span="3" style="width:69pt"> </colgroup><tbody>
<!--EndFragment--></tbody>

 

[mikerickson]

Perhaps something like
=CHOOSE(Q29, IF(A:D>=3, "Advance", "not specified") , IF(A:D>=3,"Established", "not specified") , IF(A:D>=2, "Emerging", "not specified") , IF(E1=1, "Latent", "not specified"))

You'd have to substitue the correct syntax for A:D>=3,2 etc. and also resolve the "not specified" conditions. (Your chart doesn't specify what you want if Q29=1 and A:D < 3)

The main notion is to use CHOOSE(Q29,....) to split out those possibilities. Each of the other arguments of CHOOSE should be a formula appropriate for that value of Q29.

 

[DCAbound]

It shows me a value error.

Here is the formula i used.

=CHOOSE(DQ3,IF(DK3:DN3>=3,"Advance","1"),IF(DK3:DN3>=3,"Established","2"),IF(DK3:DN3>=2,"Emerging","3"),IF(DK3:DN3=1,"Latent","4"))

	A	B	c	d	e	f	g
	EXAMq28a	EXAMq28b	EXAMq28c	EXAMq28d	EXAMq28e	EXAMq28f	EXAMq29
Rep1	1	1	1	1			1	#VALUE!
Rep2	1	1	1				2	#VALUE!

<!--StartFragment--> <colgroup><col width="69" style="width:69pt"> <col width="69" span="7" style="width:69pt"> <col width="69" style="width:69pt"> </colgroup><tbody>
<!--EndFragment--></tbody>

 

[cyrilbrd]

It shows me a value error.

Here is the formula i used.

=CHOOSE(DQ3,IF(DK3:DN3>=3,"Advance","1"),IF(DK3:DN3>=3,"Established","2"),IF(DK3:DN3>=2,"Emerging","3"),IF(DK3:DN3=1,"Latent","4"))

	A	B	c	d	e	f	g
	EXAMq28a	EXAMq28b	EXAMq28c	EXAMq28d	EXAMq28e	EXAMq28f	EXAMq29
Rep1	1	1	1	1			1	#VALUE!
Rep2	1	1	1				2	#VALUE!

<tbody>
</tbody>

1-Entered CSE.
<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { vertical-align: middle; white-space: normal; }.xl64 { text-align: right; vertical-align: middle; white-space: normal; }.xl65 { font-family: "Lucida Grande"; }</style>

	A	B	c	d	e	f	g
	EXAMq28a	EXAMq28b	EXAMq28c	EXAMq28d	EXAMq28e	EXAMq28f	EXAMq29
Rep1	1	1	1	1			1	1
Rep2	1	1	1				2	2

<tbody>
</tbody>

2- range is =CHOOSE(H3,IF(B3:E3>=3,"Advance","1"),IF(B3:E3>=3,"Established","2"),IF(B3:E3>=2,"Emerging","3"),IF(B3:E3=1,"Latent","4"))

 

[DCAbound]

Why would it be H3? Shouldn't it be G3? Formula still doesn't work showing a value error

 

[cyrilbrd]

sorry, yes ...
my columns are off by 1 value... B3:E3 should be A3:D3...