Multiple Function same formula

D

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?
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sort by date Sort by votes
Hi, do you want TRUE if A1+B1+B2+B3+B4 superior or equal to 3?
 
Upvote 0
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.

[TABLE="width: 621"]
<!--StartFragment--> <colgroup><col width="69" style="width:69pt"> <col width="69" span="7" style="width:69pt"> <col width="69" style="width:69pt"> </colgroup><tbody>[TR]
[TD="width: 69"][/TD]
[TD="width: 69"]A[/TD]
[TD="width: 69"]B[/TD]
[TD="width: 69"]c[/TD]
[TD="width: 69"]d[/TD]
[TD="width: 69"]e[/TD]
[TD="width: 69"]f[/TD]
[TD="width: 69"]g[/TD]
[TD="class: xl71, width: 69"] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl74"]EXAMq28a[/TD]
[TD="class: xl74"]EXAMq28b[/TD]
[TD="class: xl74"]EXAMq28c[/TD]
[TD="class: xl74"]EXAMq28d[/TD]
[TD="class: xl73"]EXAMq28e[/TD]
[TD="class: xl74"]EXAMq28f[/TD]
[TD="class: xl75"]EXAMq29[/TD]
[TD="class: xl72"] [/TD]
[/TR]
[TR]
[TD]Rep1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="class: xl72"]Advance[/TD]
[/TR]
[TR]
[TD]Rep2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="class: xl72"]Advance[/TD]
[/TR]
[TR]
[TD]Rep3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD="class: xl72"]Advance[/TD]
[/TR]
[TR]
[TD]Rep4[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD="class: xl72"]Latent[/TD]
[/TR]
[TR]
[TD]Rep5[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl72"]Latent[/TD]
[/TR]
[TR]
[TD]Rep6[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="class: xl72"]Advance[/TD]
[/TR]
[TR]
[TD]Rep7[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="class: xl72"]Latent[/TD]
[/TR]
[TR]
[TD]Rep8[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD="class: xl72"]Advance[/TD]
[/TR]
[TR]
[TD]Rep9[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD="class: xl72"]Latent[/TD]
[/TR]
[TR]
[TD]Rep10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl72"]Latent[/TD]
[/TR]
[TR]
[TD]Rep11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl72"]Latent[/TD]
[/TR]
[TR]
[TD]Rep12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="class: xl72"]Latent[/TD]
[/TR]
[TR]
[TD]Rep13[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="class: xl76"]Advance[/TD]
[/TR]
[TR]
[TD]Rep14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl72"] [/TD]
[/TR]
[TR]
[TD]Rep15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl71"] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl78"]Q28[/TD]
[TD="class: xl77"]Q29[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A:D>=3[/TD]
[TD="align: right"]1[/TD]
[TD="class: xl72"]Advance[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A:D>=3[/TD]
[TD="align: right"]2[/TD]
[TD="class: xl72"]Established[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A:D>=2[/TD]
[TD="align: right"]3[/TD]
[TD="class: xl72"]Emerging[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]28F=1 and ELSE [/TD]
[TD="class: xl72"]Latent[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
<!--EndFragment--></tbody>[/TABLE]
 
Upvote 0
These are the text that I need to show once the value is true

[TABLE="width: 207"]
<!--StartFragment--> <colgroup><col width="69" span="3" style="width:69pt"> </colgroup><tbody>[TR]
[TD="class: xl74, width: 69"]Q28[/TD]
[TD="class: xl73, width: 69"]Q29[/TD]
[TD="width: 69"][/TD]
[/TR]
[TR]
[TD]A:D>=3[/TD]
[TD="class: xl72"]1[/TD]
[TD="class: xl71"]Advance[/TD]
[/TR]
[TR]
[TD]A:D>=3[/TD]
[TD="class: xl72"]2[/TD]
[TD="class: xl71"]Established[/TD]
[/TR]
[TR]
[TD]A:D>=2[/TD]
[TD="class: xl72"]3[/TD]
[TD="class: xl71"]Emerging[/TD]
[/TR]
[TR]
[TD]E=1 and ELSE [/TD]
[TD="class: xl72"]4[/TD]
[TD="class: xl71"]Latent[/TD]
[/TR]
<!--EndFragment--></tbody>[/TABLE]
 
Upvote 0
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:
Upvote 0
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"))


[TABLE="width: 621"]
<!--StartFragment--> <colgroup><col width="69" style="width:69pt"> <col width="69" span="7" style="width:69pt"> <col width="69" style="width:69pt"> </colgroup><tbody>[TR]
[TD="width: 69"][/TD]
[TD="width: 69"]A[/TD]
[TD="width: 69"]B[/TD]
[TD="width: 69"]c[/TD]
[TD="width: 69"]d[/TD]
[TD="width: 69"]e[/TD]
[TD="width: 69"]f[/TD]
[TD="width: 69"]g[/TD]
[TD="class: xl71, width: 69"] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl74"]EXAMq28a[/TD]
[TD="class: xl74"]EXAMq28b[/TD]
[TD="class: xl74"]EXAMq28c[/TD]
[TD="class: xl74"]EXAMq28d[/TD]
[TD="class: xl73"]EXAMq28e[/TD]
[TD="class: xl74"]EXAMq28f[/TD]
[TD="class: xl75"]EXAMq29[/TD]
[TD="class: xl72"] [/TD]
[/TR]
[TR]
[TD]Rep1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="class: xl76, align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]Rep2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="class: xl76, align: center"]#VALUE![/TD]
[/TR]
<!--EndFragment--></tbody>[/TABLE]
 
Upvote 0
DCAbound said:
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"))


[TABLE="width: 621"]
<tbody>[TR]
[TD="width: 69"][/TD]
[TD="width: 69"]A[/TD]
[TD="width: 69"]B[/TD]
[TD="width: 69"]c[/TD]
[TD="width: 69"]d[/TD]
[TD="width: 69"]e[/TD]
[TD="width: 69"]f[/TD]
[TD="width: 69"]g[/TD]
[TD="class: xl71, width: 69"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl74"]EXAMq28a[/TD]
[TD="class: xl74"]EXAMq28b[/TD]
[TD="class: xl74"]EXAMq28c[/TD]
[TD="class: xl74"]EXAMq28d[/TD]
[TD="class: xl73"]EXAMq28e[/TD]
[TD="class: xl74"]EXAMq28f[/TD]
[TD="class: xl75"]EXAMq29[/TD]
[TD="class: xl72"][/TD]
[/TR]
[TR]
[TD]Rep1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="class: xl76, align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]Rep2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="class: xl76, align: center"]#VALUE![/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

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> [TABLE="class: grid, width: 585"]
<tbody>[TR]
[TD="width: 65"][/TD]
[TD="width: 65"]A[/TD]
[TD="width: 65"]B[/TD]
[TD="width: 65"]c[/TD]
[TD="width: 65"]d[/TD]
[TD="width: 65"]e[/TD]
[TD="width: 65"]f[/TD]
[TD="width: 65"]g[/TD]
[TD="width: 65"][/TD]
[/TR]
[TR]
[TD="width: 65"][/TD]
[TD="width: 65"]EXAMq28a[/TD]
[TD="width: 65"]EXAMq28b[/TD]
[TD="width: 65"]EXAMq28c[/TD]
[TD="width: 65"]EXAMq28d[/TD]
[TD="width: 65"]EXAMq28e[/TD]
[TD="width: 65"]EXAMq28f[/TD]
[TD="width: 65"]EXAMq29[/TD]
[TD="width: 65"][/TD]
[/TR]
[TR]
[TD="width: 65"]Rep1[/TD]
[TD="width: 65"]1[/TD]
[TD="width: 65"]1[/TD]
[TD="width: 65"]1[/TD]
[TD="width: 65"]1[/TD]
[TD="width: 65"][/TD]
[TD="width: 65"][/TD]
[TD="width: 65"]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD="width: 65"]Rep2[/TD]
[TD="width: 65"]1[/TD]
[TD="width: 65"]1[/TD]
[TD="width: 65"]1[/TD]
[TD="width: 65"][/TD]
[TD="width: 65"][/TD]
[TD="width: 65"][/TD]
[TD="width: 65"]2[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]
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"))
 
Upvote 0
sorry, yes ...
my columns are off by 1 value... B3:E3 should be A3:D3...
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
assigning one of three values to cell to get a score of the row
Replies
1
Views
133
dreid1011
dreid1011
G
  • Solved
Formula to make combination of elements given possible variables?
Replies
5
Views
247
felixstraube
F
J
  • Solved
Nesting/combining functions into one formula
Replies
3
Views
392
Fluff
Fluff
M
  • Solved
How to get multiple outputs from XLOOKUP?
Replies
2
Views
652
mwl_y
M
PuntingJawa
  • Solved
Nested formula with multiple criteria
Replies
7
Views
443
PuntingJawa
PuntingJawa

Forum statistics

Threads
1,223,162
Messages
6,170,432
Members
452,326
Latest member
johnshaji

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top