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?
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

cyrilbrd

Well-known Member
Joined
Feb 2, 2012
Messages
4,071
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,959
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,071
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,071
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,089,474
Messages
5,408,454
Members
403,207
Latest member
JFoley182

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top