Multiple Function same formula

DCAbound

New Member
Joined
Dec 3, 2013
Messages
20
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

Well-known Member
Joined
Feb 2, 2012
Messages
4,030
Office Version
365
Platform
Windows, Mobile
Hi, do you want TRUE if A1+B1+B2+B3+B4 superior or equal to 3?
 

DCAbound

New Member
Joined
Dec 3, 2013
Messages
20
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.

ABcdefg
EXAMq28aEXAMq28bEXAMq28cEXAMq28dEXAMq28eEXAMq28fEXAMq29
Rep111111Advance
Rep21112Advance
Rep3113Advance
Rep414Latent
Rep51Latent
Rep611111Advance
Rep712Latent
Rep813Advance
Rep914Latent
Rep10Latent
Rep11Latent
Rep121Latent
Rep131111Advance
Rep14
Rep15
Q28Q29
A:D>=31Advance
A:D>=32Established
A:D>=23Emerging
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

New Member
Joined
Dec 3, 2013
Messages
20
These are the text that I need to show once the value is true

Q28Q29
A:D>=31Advance
A:D>=32Established
A:D>=23Emerging
E=1 and ELSE 4Latent

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

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,895
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.
 
Last edited:

DCAbound

New Member
Joined
Dec 3, 2013
Messages
20
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"))


ABcdefg
EXAMq28aEXAMq28bEXAMq28cEXAMq28dEXAMq28eEXAMq28fEXAMq29
Rep111111#VALUE!
Rep21112#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

Well-known Member
Joined
Feb 2, 2012
Messages
4,030
Office Version
365
Platform
Windows, Mobile
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"))


ABcdefg
EXAMq28aEXAMq28bEXAMq28cEXAMq28dEXAMq28eEXAMq28fEXAMq29
Rep111111#VALUE!
Rep21112#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>
ABcdefg
EXAMq28aEXAMq28bEXAMq28cEXAMq28dEXAMq28eEXAMq28fEXAMq29
Rep1111111
Rep211122

<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

New Member
Joined
Dec 3, 2013
Messages
20
Why would it be H3? Shouldn't it be G3? Formula still doesn't work showing a value error

 

cyrilbrd

Well-known Member
Joined
Feb 2, 2012
Messages
4,030
Office Version
365
Platform
Windows, Mobile
sorry, yes ...
my columns are off by 1 value... B3:E3 should be A3:D3...
 

Forum statistics

Threads
1,086,033
Messages
5,387,396
Members
402,059
Latest member
wdyl121

Some videos you may like

This Week's Hot Topics

Top