Permutation Calculation Formula with a TWIST

[cnyoon2]

Hi,

I need help finding a formula that calculates 8 possible Yes/No/NA calculations from 8 questions to one output.

So for instance, there are 8 questions that answer Yes/No/NA, and depending on how those questions are answered there is ONE choice that comes out. ALL of this info has already been manually entered into one big database spreadsheet tab. Someone actually did this manually, however now we want to apply this to an actual formula. How can I go about doing this? It's essentially like a lookup of some sort. I have hundreds of people taking this 8 question questionnaire and the results are logged into this excel file and now I need to apply this formula to get my ONE choice output from scanning this list of all possible permulations.

How can I do this?

Best,
Chris

(P.S.) I am using Excel 2010!

[mehmetcik]

Hi Chris

Couldn't you change your data into numbers? if you have 8 numbers

and each number is 1 if n/a 2 if no 3 if yes then you have a number between 11111111 and 33333333
so all you need is the match or vlookup function.

[cnyoon2]

No sorry, pretty much I'm asking how to do a 8 cell Match lookup. It's a multi cell Vlookup, to return a value. Ideas?

[XOR LX]

Hi,

So when you say "this info has already been manually entered into one big database spreadsheet tab", am I to assume that you have all the results for every single combination of answers?

So, for example, if I had Person1's eight answers in Sheet1 A1:H1, and I also had this database of every single combination of answers in Sheet2 A2:I6562 (I make the number of combinations for 8 answers with 3 choices each to be 3^8=6561), with the answers in columns A-H and the desired result in column I, I could obtain Person1's result via:

=INDEX(Sheet2!$I$2:$I$6562,MATCH(A1&B1&C1&D1&E1&F1&G1&H1,INDEX(Sheet2!$A$2:$A$6562&Sheet2!$B$2:$B$6562&Sheet2!$C$2:$C$6562&Sheet2!$D$2:$D$6562&Sheet2!$E$2:$E$6562&Sheet2!$F$2:$F$6562&Sheet2!$G$2:$G$6562&Sheet2!$H$2:$H$6562,,),0))

Regards

[cnyoon2]

Yes, this seems to be on the right track but i was wondering how i could make the desired result draw from the last column. XOR LX your correct in that I have a large database of all possible combinations in a separate sheet. Each combination has one desired result in the last column, how do I draw from that?

[XOR LX]

"...but i was wondering how i could make the desired result draw from the last column"

Not sure I understand. Re-read the assumptions posted with my formula; it is doing precisely that (column I in my hypothetical set-up - obviously amend to suit your situation.)

Regards

[cnyoon2]

My permutation outcomes are in Sheet2 in from E4:O132. Yes there are only 132 options because if you run our particular series of questions there are only certain combinations, not a full 6232 which makes sense, just take as is. There are 8 answers to the questions and they are in E, F, G, H, with I being a blank column all the way down, then J, K, L, M and then N being a blank column then O has all the desired results.

Similarly in my Sheet1 I have all the answers to people's questions, all in nice rows, in M, N, O, P and with Q being blank column then the rest in R, S, T, U. Not sure if the blank spaces are a factor but regardless I want to pull the last column, O in my sheet 2 to be called. I have the formula set in Sheet1 in column W (with column V being blank).

Best,
Chris

[XOR LX]

Ok, but just as you are unable to adapt the formula I posted to suit your actual ranges, I am equally unable to post a formula-based solution based on the information you are providing.

It would seem that you posting a sample workbook is the best way forward now. Obviously remove/replace any sensitive/confidential information, though make sure that the actual set-up, cell/range references are identical to that of your actual workbook.

Regards

[cnyoon2]

Ok i cleaned it up and posted it here:

[XOR LX]

You were nearly correct with the formula you had in there. Should be, in W2 and copy down:

=INDEX(Sheet2!$O$4:$O$132,MATCH(M2&N2&O2&P2&R2&S2&T2&U2,INDEX(Sheet2!$E$4:$E$132&Sheet2!$F$4:$F$132&Sheet2!$G$4:$G$132&Sheet2!$H$4:$H$132&Sheet2!$J$4:$J$132&Sheet2!$K$4:$K$132&Sheet2!$L$4:$L$132&Sheet2!$M$4:$M$132,,),0),)

However, you'll notice that you will have quite a few #N/As in there, and I'm slightly confused, as you said that your Sheet2 contained all possible answer combinations. Take the first line, for example: the formula in W2 is returning #N/A as there simply isn't to be found anywhere in Sheet2 the combination of answers: N, N, M, N, Y, Y, M, N. Similarly for row 3, etc. (in fact you don't get your first genuine match until row 11).

Regards

[cnyoon2]

Thanks XorLX this works good HOWEVER,

The problem I have is that it doesn't refresh automatically unless I click it inside it and then press enter. Is there a way to correct this?

Meaning if I click and drag it down to copy it or if data changes, I dont see it refreshing.

Best,

[martindwilson]

turn auto-calculation on in excel options

[shg]

If you only have one or a few formulas like that, that's a fine approach -- but every instance of it has to catenate 132 columns and then do a linear search for a match.

If you catenated the results into an extra column in the table, and then sorted the whole table alphabetically by that column, you could use instead

=if(lookup(M2&N2&O2&P2&R2&S2&T2&U2, leftColumn) = M2&N2&O2&P2&R2&S2&T2&U2, lookup(M2&N2&O2&P2&R2&S2&T2&U2, bothColumns), "Oops!")

Or, if you were sure there would be a match, just

=lookup(M2&N2&O2&P2&R2&S2&T2&U2, bothColumns)

Both of those do a binary search, and either would be hundreds of times faster.

[cnyoon2]

Thank you all, this problem has officially been solved and i LOVE IT!!!!!!!!!!!!!!!!! how do i close it out as solved?

[arlu1201]

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.