Multi-function formula syntax

TWienholz

Board Regular
Joined
Aug 3, 2016
Messages
54
Good morning all. I have been wracking my brain trying to get the proper syntax for a formula that will take the information from 3 different cells providing multiple scenarios and give a specific envelope size result based that criteria. The criteria is as follows (table sample below):

=IF(B13="","",IF(AND(B6="01 CAS: CAHPS",B6="02 CCS: CAHPS",B6="03 CCS CCC: CAHPS",B6="04 MAS: CAHPS",B6="05 MCS: CAHPS",B6="06 MCS CCC: CAHPS",B6="06 MCS CCC: CAHPS Simulation",B6="07 HCAHPS",B6="08 HOS Simulation",B6="09 Call Sat",B6="09 Case Management",B6="09 CG CAHPS",B6="09 Disease Management",B6="09 ECHO - Behavioral Health",B6="09 Member Sat",B6="09 OAS CAHPS",B6="09 Provider Sat",B6="09 CAHPS Simulation",B6="50 PCMH - Adult",B6="60 PCMH - Child",I13="Single Stuff",I13="Split",j6="1 - 8.5 x 11", j6="2 - 8.5 x 14",j6="1 - 11 x 17", j6="2 - 11 x 17"),"#10.5 Window"),

IF(AND(B6="01 CAS: CAHPS",B6="02 CCS: CAHPS",B6="03 CCS CCC: CAHPS",B6="04 MAS: CAHPS",B6="05 MCS: CAHPS",B6="06 MCS CCC: CAHPS",B6="06 MCS CCC: CAHPS Simulation",B6="07 HCAHPS",B6="08 HOS Simulation",B6="09 Call Sat",B6="09 Case Management",B6="09 CG CAHPS",B6="09 Disease Management",B6="09 ECHO - Behavioral Health",B6="09 Member Sat",B6="09 OAS CAHPS",B6="09 Provider Sat",B6="09 CAHPS Simulation",B6="50 PCMH - Adult",B6="60 PCMH - Child",I13="Double Stuff",j6="1 - 11 x 17", j6="2 - 11 x 17"),"6 x 10 Window"),

IF(AND(B6="09 MCAHPS Simulation",B6="09 QHP Simulation",B6="10 MA ONLY: MCAHPS",B6="20 PDP ONLY: MCAHPS",B6="30 MA-PDP: MCAHPS",B6="70 ACO CAHPS",B6="80 QHP",B6="90 MIPS CAHPS",I20="Single Stuff",I13="Split",j6="1 - 11 x 17", j6="2 - 11 x 17",j6="3 - 11 x 17"),"6 x 10 Window"),

IF(AND(B6="70 ACO CAHPS",B6="80 QHP",B6="09 QHP Simulation"B6="90 MIPS CAHPS",I13="Double Stuff",j6="1 - 11 x 17", j6="2 - 11 x 17",j6="3 - 11 x 17"),"6 x 10 Window"),

IF(AND(B6="09 MCAHPS Simulation",B6="10 MA ONLY: MCAHPS",B6="20 PDP ONLY: MCAHPS",B6="30 MA-PDP: MCAHPS",I13="Double Stuff",j6="3 - 11 x 17"),"9 x 12 Window")

ABCDEFGHIJ
1
2PROJECT DESCRIPTION
3MPL #:101020* Account Project Manager:
4* Client Name:ABC Company* Project Coordinator:
5* Parent Company:Mom & Pop Company* Prior Sample #:9128000
6* Project Description:01 CAS: CAHPS* Survey Size:1 – 11 x 17
7* Total Seeds:1
8* # Waves with Project:2
9* Enter language if “Other” is selected:English
10*Initial Scheduled Mail Date:TBD
11
12Sample Number(s)Sample Size
Bleed?
Back Page?Text Format
Need?

Size
*Language Protocol
Size
131 9138000500- SELECT ONE -- SELECT ONE -Single Stuff#10.5 Window
142 91380011000Double Stuff6 x 10 Window

<tbody>
</tbody>

Please excuse my long hand. I know this is ridiculously extra and not the easiest way to accomplish this, but since it is not a range of cells indexing or lookup functions wouldn’t work, I keep getting too many function errors on any If/And functions, and I don’t know VBA to try and make it work that way. I just need to find a way to make sure the correct envelope size is being chosen automatically per project specs so that I can avoid having to request reprints for account people who can’t seem to figure this out. Please help!

Thank you in advance for any assistance rendered!
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
AND(B6="01 CAS: CAHPS",B6="02 CCS: CAHPS")

how can B6 be 2 things at once - did you mean OR(................)
 
Upvote 0
AND(B6="01 CAS: CAHPS",B6="02 CCS: CAHPS")

how can B6 be 2 things at once - did you mean OR(................)

B6 can be anyone one of a list of 24 different things. That's been one of the main challenges to getting this to work.
 
Upvote 0
Hi,

You need OR statements within the AND statements (and you have misplaced brackets), this cleans up your formula a bit, give it a try and see if you're getting the results you want.
The formula can probably be reduced further, I just don't have time right now.


Book1
J
1 
Sheet350
Cell Formulas
RangeFormula
J1=IF(B13="","",IF(AND(OR(B6={"01 CAS: CAHPS","02 CCS: CAHPS","03 CCS CCC: CAHPS","04 MAS: CAHPS","05 MCS: CAHPS","06 MCS CCC: CAHPS","06 MCS CCC: CAHPS Simulation","07 HCAHPS","08 HOS Simulation","09 Call Sat","09 Case Management","09 CG CAHPS","09 Disease Management","09 ECHO - Behavioral Health","09 Member Sat","09 OAS CAHPS","09 Provider Sat","09 CAHPS Simulation","50 PCMH - Adult","60 PCMH - Child"}),OR(I13={"Single Stuff","Split"}),OR(J6={"1 - 8.5 x 11","2 - 8.5 x 14","1 - 11 x 17","2 - 11 x 17"})),"#10.5 Window",IF(AND(OR(B6={"01 CAS: CAHPS","02 CCS: CAHPS","03 CCS CCC: CAHPS","04 MAS: CAHPS","05 MCS: CAHPS","06 MCS CCC: CAHPS","06 MCS CCC: CAHPS Simulation","07 HCAHPS","08 HOS Simulation","09 Call Sat","09 Case Management","09 CG CAHPS","09 Disease Management","09 ECHO - Behavioral Health","09 Member Sat","09 OAS CAHPS","09 Provider Sat","09 CAHPS Simulation","50 PCMH - Adult","60 PCMH - Child"}),I13="Double Stuff",OR(J6={"1 - 11 x 17","2 - 11 x 17"})),"6 x 10 Window",IF(AND(OR(B6={"09 MCAHPS Simulation","09 QHP Simulation","10 MA ONLY: MCAHPS","20 PDP ONLY: MCAHPS","30 MA-PDP: MCAHPS","70 ACO CAHPS","80 QHP","90 MIPS CAHPS"}),I20="Single Stuff",I13="Split",OR(J6={"1 - 11 x 17","2 - 11 x 17","3 - 11 x 17"})),"6 x 10 Window",IF(AND(OR(B6={"70 ACO CAHPS","80 QHP","09 QHP Simulation","90 MIPS CAHPS"}),I13="Double Stuff",OR(J6={"1 - 11 x 17","2 - 11 x 17","3 - 11 x 17"})),"6 x 10 Window",IF(AND(OR(B6={"09 MCAHPS Simulation","10 MA ONLY: MCAHPS","20 PDP ONLY: MCAHPS","30 MA-PDP: MCAHPS"}),I13="Double Stuff",J6="3 - 11 x 17"),"9 x 12 Window",""))))))
 
Last edited:
Upvote 0
=and(a1=2,a1=3) can you not see that this does not make sense

=if(a1=2,"yes",if(a1=3,"no")) if this formula in cell D1 then D1 will be yes or no

2.......yes
3.......no

call this little table mytable and in D1 =vlookup(a1,mytable,2) will return yes or no
 
Upvote 0
Hi,

You need OR statements within the AND statements (and you have misplaced brackets), this cleans up your formula a bit, give it a try and see if you're getting the results you want.
The formula can probably be reduced further, I just don't have time right now.

J
1

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet350

Worksheet Formulas
CellFormula
J1=IF(B13="","",IF(AND(OR(B6={"01 CAS: CAHPS","02 CCS: CAHPS","03 CCS CCC: CAHPS","04 MAS: CAHPS","05 MCS: CAHPS","06 MCS CCC: CAHPS","06 MCS CCC: CAHPS Simulation","07 HCAHPS","08 HOS Simulation","09 Call Sat","09 Case Management","09 CG CAHPS","09 Disease Management","09 ECHO - Behavioral Health","09 Member Sat","09 OAS CAHPS","09 Provider Sat","09 CAHPS Simulation","50 PCMH - Adult","60 PCMH - Child"}),OR(I13={"Single Stuff","Split"}),OR(J6={"1 - 8.5 x 11","2 - 8.5 x 14","1 - 11 x 17","2 - 11 x 17"})),"#10.5 Window",IF(AND(OR(B6={"01 CAS: CAHPS","02 CCS: CAHPS","03 CCS CCC: CAHPS","04 MAS: CAHPS","05 MCS: CAHPS","06 MCS CCC: CAHPS","06 MCS CCC: CAHPS Simulation","07 HCAHPS","08 HOS Simulation","09 Call Sat","09 Case Management","09 CG CAHPS","09 Disease Management","09 ECHO - Behavioral Health","09 Member Sat","09 OAS CAHPS","09 Provider Sat","09 CAHPS Simulation","50 PCMH - Adult","60 PCMH - Child"}),I13="Double Stuff",OR(J6={"1 - 11 x 17","2 - 11 x 17"})),"6 x 10 Window",IF(AND(OR(B6={"09 MCAHPS Simulation","09 QHP Simulation","10 MA ONLY: MCAHPS","20 PDP ONLY: MCAHPS","30 MA-PDP: MCAHPS","70 ACO CAHPS","80 QHP","90 MIPS CAHPS"}),I20="Single Stuff",I13="Split",OR(J6={"1 - 11 x 17","2 - 11 x 17","3 - 11 x 17"})),"6 x 10 Window",IF(AND(OR(B6={"70 ACO CAHPS","80 QHP","09 QHP Simulation","90 MIPS CAHPS"}),I13="Double Stuff",OR(J6={"1 - 11 x 17","2 - 11 x 17","3 - 11 x 17"})),"6 x 10 Window",IF(AND(OR(B6={"09 MCAHPS Simulation","10 MA ONLY: MCAHPS","20 PDP ONLY: MCAHPS","30 MA-PDP: MCAHPS"}),I13="Double Stuff",J6="3 - 11 x 17"),"9 x 12 Window",""))))))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

This got me much closer! Thank you so much! I'm testing it now and it seems to be working with some scenarios but not with others. It's a start though! Thanks again!
 
Upvote 0
=and(a1=2,a1=3) can you not see that this does not make sense

=if(a1=2,"yes",if(a1=3,"no")) if this formula in cell D1 then D1 will be yes or no

2.......yes
3.......no

call this little table mytable and in D1 =vlookup(a1,mytable,2) will return yes or no

Yes I understand it doesn't make sense as a formula, but I wrote the scenarios out to describe the conditions to which I needed the formula to choose a specific envelope based on information an account person chose from a series of drop down lists. Trying to put it all together to make sense as a workable formula has been a lesson in patience. I'm rearranging my table information now to list in a range so that maybe I can use the index and match functions to get the info I need. Thank you for the suggestion.
 
Upvote 0
This got me much closer! Thank you so much! I'm testing it now and it seems to be working with some scenarios but not with others. It's a start though! Thanks again!

You're welcome.

Hopefully you'll get it sorted out...if the formula now works for some scenarios but not others, unfortunately I can't help you with that since I don't know what the requirements are, only you do...

Good luck, let us know if you need further help.
 
Upvote 0
I13="Double Stuff",..........

the only way to change I13 is to have a formula on I13

or do what you want with a macro....
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,952
Members
448,535
Latest member
alrossman

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