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?
 
Sort by date Sort by votes
Thanks for helping but i am still getting a value error message.

=CHOOSE(DQ14,IF(DK14:DN14>=3,"Advance","1"),IF(DK14:DN14>=3,"Established","2"),IF(DK14:DN14>=2,"Emerging","3"),IF(DK14:DN14=1,"Latent","4"))

I need cells H to respond Advance, Establish, Emerging, or Latent.
 
Last edited:
Upvote 0

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
question, based on your data shouldn't the first 3 be Advance then Established and then Emerging?
<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; }</style> [TABLE="width: 520"]
<colgroup><col style="width:65pt" span="8" width="65"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 65"]EXAMq28a[/TD]
[TD="class: xl63, width: 65"]EXAMq28b[/TD]
[TD="class: xl63, width: 65"]EXAMq28c[/TD]
[TD="class: xl63, width: 65"]EXAMq28d[/TD]
[TD="class: xl63, width: 65"]EXAMq28e[/TD]
[TD="class: xl63, width: 65"]EXAMq28f[/TD]
[TD="class: xl63, width: 65"]EXAMq29[/TD]
[TD="width: 65"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 65"]1[/TD]
[TD="class: xl64, width: 65"]1[/TD]
[TD="class: xl64, width: 65"]1[/TD]
[TD="class: xl64, width: 65"]1[/TD]
[TD="class: xl63, width: 65"][/TD]
[TD="class: xl63, width: 65"][/TD]
[TD="class: xl64, width: 65"]1[/TD]
[TD]Advance[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 65"]1[/TD]
[TD="class: xl64, width: 65"]1[/TD]
[TD="class: xl64, width: 65"]1[/TD]
[TD="class: xl63, width: 65"][/TD]
[TD="class: xl63, width: 65"][/TD]
[TD="class: xl63, width: 65"][/TD]
[TD="class: xl64, width: 65"]2[/TD]
[TD]Established[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 65"]1[/TD]
[TD="class: xl64, width: 65"]1[/TD]
[TD="class: xl63, width: 65"][/TD]
[TD="class: xl63, width: 65"][/TD]
[TD="class: xl63, width: 65"][/TD]
[TD="class: xl63, width: 65"][/TD]
[TD="class: xl64, width: 65"]3[/TD]
[TD]Emerging[/TD]
[/TR]
</tbody>[/TABLE]

the first one being >=3 and 1
the second being >=3 and 2
the third being >=2 and 3
yet in your sample they are noted as Advance, Advance and Advance...

used: =IFERROR(INDEX($R$29:$R$32,MATCH(1,IF(($P$29:$P$32<=SUM($A2:$D2))*($Q$29:$Q$32=G2),1)),0),"") Ctrl + Shift + Enter not just enter on a PC or Command + Return on a MAC.
 
Upvote 0
The function that i was using in that data is =IF(AND(DQ3=1)+SUM(DK3:DN3)>=3,"Advance",IF(AND(DQ3=2)+SUM(DK3:DN3)>=3,"Established",IF(AND(DQ3=3)+SUM(DK3:DN3)>=2,"Emerging",IF(AND(DO3=1),"Latent","Latent"))))

it somewhat works but treats the + as an OR. This was the issue of my original post.


 
Upvote 0
DCAbound said:
The function that i was using in that data is =IF(AND(DQ3=1)+SUM(DK3:DN3)>=3,"Advance",IF(AND(DQ3=2)+SUM(DK3:DN3)>=3,"Established",IF(AND(DQ3=3)+SUM(DK3:DN3)>=2,"Emerging",IF(AND(DO3=1),"Latent","Latent"))))

it somewhat works but treats the + as an OR. This was the issue of my original post.
Click to expand...

You need to specify the outcome cells, which are now empty in the exhibit below...


[TABLE="width: 240"]
<COLGROUP><COL style="WIDTH: 48pt" span=5 width=64><TBODY>[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="class: xl77, width: 256, bgcolor: transparent, colspan: 4"]Q29 à[/TD]
[/TR]
[TR]
[TD="class: xl76, width: 64, bgcolor: white"]Q28 ↓[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl71, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl72, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl71, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl72, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl71, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl72, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl73, bgcolor: transparent"] [/TD]
[TD="class: xl74, bgcolor: transparent"] [/TD]
[TD="class: xl74, bgcolor: transparent"] [/TD]
[TD="class: xl75, bgcolor: transparent"] [/TD]
[/TR]
</TBODY>[/TABLE]
 
Upvote 0
I am afraid I cannot do that because the responses for these questions are by Columns. Individuals Questionnaire results data are shown in column from question 1 - 50. Some questions are check all boxes (1, Blank) and some are radio boxes (represented by 1-4, depending on how many responses there are), where I have to choose 1 radio box and 2 or more check boxes for example to form a decision rule... Advance, Established, Emerging Latent. EACH ROW in its entirety signifies a responses from 1 individual questionnaire results.

that's why I need to put across the board instead of in colums.

Thanks,
-earl
 
Upvote 0
Aladin Akyurek said:
You need to specify the outcome cells, which are now empty in the exhibit below...


[TABLE="width: 240"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="class: xl77, width: 256, bgcolor: transparent, colspan: 4"]Q29 à[/TD]
[/TR]
[TR]
[TD="class: xl76, width: 64, bgcolor: white"]Q28 ↓[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl72, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl72, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl72, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl73, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

DCAbound said:
I am afraid I cannot do that because the responses for these questions are by Columns. Individuals Questionnaire results data are shown in column from question 1 - 50. Some questions are check all boxes (1, Blank) and some are radio boxes (represented by 1-4, depending on how many responses there are), where I have to choose 1 radio box and 2 or more check boxes for example to form a decision rule... Advance, Established, Emerging Latent. EACH ROW in its entirety signifies a responses from 1 individual questionnaire results.

that's why I need to put across the board instead of in colums.

Thanks,
-earl
Click to expand...

You already provided parts of the above matrix. Q28 has to do with the numbers which obtain by summing and Q29 represents the radio button choices.
 
Upvote 0
DCAbound said:
The function that i was using in that data is =IF(AND(DQ3=1)+SUM(DK3:DN3)>=3,"Advance",IF(AND(DQ3=2)+SUM(DK3:DN3)>=3,"Established",IF(AND(DQ3=3)+SUM(DK3:DN3)>=2,"Emerging",IF(AND(DO3=1),"Latent","Latent"))))

it somewhat works but treats the + as an OR. This was the issue of my original post.
Click to expand...

Maybe this:

Code: 
=IF((DQ3=1)+(SUM(DK3:DN3)>=3),"Advance",IF((DQ3=2)+(SUM(DK3:DN3)>=3),"Established",IF((DQ3=3)+(SUM(DK3:DN3)>=2),"Emerging","Latent")))

Markmzz
 
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,164
Messages
6,170,444
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