+ Reply to Thread
Results 1 to 7 of 7

Need formula to generate combination of five columns with three possible answers in each

  1. #1
    Registered User
    Join Date
    03-15-2018
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    27

    Question Need formula to generate combination of five columns with three possible answers in each

    Is formula that will generate a table of possible combinations? I am working on an electronic summary form which helps professional learners understand how close (or far away) they are from achieving certification and what areas they need to work on.

    For this section, I have five columns. Each column represents a different characteristic that must be met in order to achieve "Fidelity" or certification. Each characteristic is labeled either FIDELITY, APPROACHING FIDELITY or NO FIDELITY. The combination of all of those characteristics result in an overall determination. I have attached a sample with the overarching rules. Is there a formula that can generate all possible combinations? Could a pivot table do this? I'd like to avoid VBA if I can.

    Thanks!
    L
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Need formula to generate combination of five columns with three possible answers in ea

    Do you want the result of the "Overall" column?

  3. #3
    Registered User
    Join Date
    03-15-2018
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    27

    Re: Need formula to generate combination of five columns with three possible answers in ea

    Sure, if it's possible to calculate using the parameters. Nested IF AND/OR formulas have worked in other areas for this project. There are some combinations that are unlikely, but possible, so I want to include every possible angle. Once I have that piece done. I will add that to the second part (which you helped me out with yesterday) and that will determine Fidelity in both disciplines. Once I understand how to generate all the possible combinations, I can use it for other sections that are similar but not exactly the same.

    Thanks, John

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Need formula to generate combination of five columns with three possible answers in ea

    Your rule on JP, R anf F does not hold: in rows 2:6 ALL are FIDELITY but rows 4:6 are rated as "APPROACHING FIDELITY".

    What about columns "O" and "A/P"

  5. #5
    Registered User
    Join Date
    03-15-2018
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    27

    Re: Need formula to generate combination of five columns with three possible answers in ea

    That's because O and A/P are either Approaching Fidelity or No Fidelity. O and A/P can be Approaching Fidelity and if JP, R and F are Fidelity, the professional would get Fidelity. However, if they are only Approaching Fidelity or have no fidelity in either O or A/P, they would only be Approaching Fidelity. Conversely, if they have Fidelity in O and A/P and have Approaching Fidelity in any one of the J, R and F characteristics, they would still only be Approaching Fidelity since more weight is given to those first three.

    I know it's really confusing. There are a lot of different pieces to it that are graded with varying weights. Developing an electronic form where the user can input their information over a period of 6 months and have it automatically calculate the level of fidelity in three degrees for each section has been a big challenge.

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Need formula to generate combination of five columns with three possible answers in ea

    Try

    in G2

    =IF(AND(COUNTIF($A2:$C2,"FIDELITY")=3,COUNTIF($D2:$E2,"NO FIDELITY")=0),"FIDELITY",IF(COUNTIF($A2:$C2,"FIDELITY")=3,"APPROACHING FIDELITY",IF(COUNTIF($A2:$C2,"APPROACHING FIDELITY")=3,"APPROACHING FIDELITY",IF(COUNTIF($A2:$E2,"NO FIDELITY")=0,"APPROACHING FIDELITY",IF(COUNTIF($A2:$C2,"NO FIDELITY"),"NO FIDELITY","")))))

  7. #7
    Registered User
    Join Date
    03-15-2018
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    27

    Re: Need formula to generate combination of five columns with three possible answers in ea

    That works perfectly to calculate the Overall Fidelity! You're a total rock star!

    Any ideas on how to generate the full table of combinations? I just posted something on the VBA thread. I've seen some versions similar, but not exactly what I need. I don't know how to write code and so I don't know how to make other examples fit.

    Thanks so much for that solve, John

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Generate list of combination from chosen factors
    By Corsa88 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-21-2016, 01:06 AM
  2. How to generate a list of pairs for all combination possible?
    By treue in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-05-2015, 09:23 AM
  3. [SOLVED] How to generate a list of pairs for all combination possible?
    By Laeticha in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-05-2015, 08:56 AM
  4. Replies: 9
    Last Post: 12-03-2014, 04:47 AM
  5. Replies: 0
    Last Post: 10-14-2014, 10:03 AM
  6. [SOLVED] Formula to generate third column value on the basis 2 different columns value
    By Manish_Gupta in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-20-2014, 01:52 AM
  7. I need to generate a list of combination
    By ciprianb in forum Excel - New Users/Basics
    Replies: 10
    Last Post: 02-28-2011, 01:28 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1