Excel Magic Trick 1291: Reverse Lookup Array Formula to Score Myers-Briggs Test

Download File: http://people.highline.edu/mgirvin/ex...
Learn how to Score Myers-Briggs Assessment (Test) in Excel with Two Formulas:
1. (00:13) Problem Introduction
2. (01:50) Formula 1: to lookup correct letter for each answer. An array Operation, COLUMN function, SUMPRODUCT function and INDEX Function.
3. (07:48) Formula 2: Count how many letters each respondent scored. COUNTIFS Function.

---

Closed Caption:

welcome to exile magic number 1291 hey
if you want to download context I work
bucket so much to 1291 1293 and follow
along
click on the link below the video hey we
have an amazing question here we need to
figure out how to school or myers-briggs
assessment or test in Excel
the myers-briggs test has 32 questions
and each has an answer of either A or B
so over here
question number one control that are all
the way to question 32 we have one
person's answers a be a we need to
figure from this lookup table what
letter gets assigned to each question so
for example want a should get in one be
get an ass to a and to be an e we need
to them
list all the letters and then down here
challenge how many E's how many apps how
many eyes and so on
now here is the lookup table and this
lookup table means if I am trying to
match one I have to find it inside the
middle of a lookup table normally when
we do look up right we're doing vlookup
finding something in the first column or
redoing index and match we have to find
a row header and column header and find
the intersecting value but here we're
doing a reverse lookup we're looking
inside the table and for our example we
need to return the column header
all right now there's going to be three
videos on this each one with a cool
solution but this one we're going to
assume we have to keep the table like
this and so what in the world are we
gonna do well first we can simply in 09
the question number
p.m. percent shift seven the letter now
we can enter this control enter double
click and send it down but what I really
need to do as I need to match this now
this is a rectangular range so at its
essence we're gonna least ask the
question hey Juan where are you in the
rectangular eight so after you put it in
edit mode and then simply use the equal
sign hey answer for question number one
are equal to any of these now there's
never going to be any duplicates in this
situation now I'm gonna hit the f4 key
to lock this now if I evaluate this just
to look at it with f90 you can see it
gives me fall season only one true now
what I really want their instead of a
true is the relative position whatever
letter it is from that array right there
now we're looking at 18 and that means
it would be an witches five so I really
need to find their so watch this
controls the we're going to have to put
parentheses around this gonna multiplied
that array and I need to force the
comparative operator equal sign in the
AM percent to calculate before
multiplying and guess what I really any
position 123 all the way to eight so I'm
simply going to hardcode this even
assuming that the myers-briggs test only
has these eight-letter so watches even
curly brackets one come all the way to a
and then and curly brackets now on I do
this multiplier every false is gonna get
a zero but the one true we'll pick out
whichever relative position is it is
asked to evaluate this ensured up
nothing but there's that five so i'm
looking up one and I'm finding the
relative position of the letter and now
controls d if this test might have more
or less than these eight letters I would
not want to do it this way then I
wouldn't want to do something like
using the function I need to create with
a formula element dynamic array of
sequential numbers now call highlight E
L nine after working now call home right
now looks it E L and delivers an array
of 5 to 12 so I hit after nine that's
not gonna work so what do i do I
subtract from that or a 529 the column
function but now I'm gonna give it a
single cell and hit the f4 key close
parenthesis now 5-5 30 so when I
highlight this each one of the numbers
in this 529 will get subtracted 05 so
when I can def 9027 not what I want
controls the I want to add one back in
now there are other ways to create a
dynamic array of sequential numbers but
this is the most robust if I move this
table or insert columns or other
structural changes this will remain
intact 18 not only that but if I
answered a call on right here to add a
new letter
properly change 1905 highlight this
right here after nine you could see it
gives me 128 if I evaluate the entire
thing F nine there it has that same
thing now all I want is five and because
there will never be duplicates all I
have to do is add this controls the now
this is filled with a bunch of our
operations there is an array operations
there is one there is another one right
there there's one right there so guess
what I don't want to have to use Ctrl
Shift dinner so I'm not going to simply
use the sum function used the song
product function don't notice this is
blue and when I hit tab it's actually
gonna steal all that parentheses right
there that's terrible so I have to make
sure to put another parentheses now some
product usually takes a raid times
arraign times Iranian multiplies dots
the product part and then add that's the
sum part but I am simply using some
product because that our radar can
handle array operations without using
the special keystroke for array formulas
control shift at work so I'm simply
going to add it close parenthesis
control under their the relative
position 41 a double click and send it
down there is the relative position for
to write 3d which is now I want to
control down on make sure after two I
got all the cell references working
looking to escape now I come up here
after two relative position 582 now use
that in a lookup formula and I'm using
index index needs an array of values
it's looking up some simply gonna
highlight the levers there is a form to
lock it
comma now it's as rownumber but I have a
one-way Andre here so what it really
means is relative position so that won't
work at five will tell index within that
range to look up one two three four and
five the letter and I come to the end
close control enter double click and
send it down now we're looking there it
is there's the there's the F right now I
have my column letters each one of the
questions now I come down here for the
final score County yes which will
challenge with a given set of one or
more conditions are criteria so the
criteria range is simply the whole
grains controls down I left for college
and the criteria please count only the E
is within that range right there that's
a relative so reference close
parenthesis control enter so when I copy
it over and any given position countess
will be looking at the letter above
going over here and counting
so there it is myers-briggs test when we
are stuck with a rectangular table we go
ahead and do a number of array
calculations to get relative position
from this rectangular rain
in essence doing a reverse look up to
them look up the column header letter
and then of course we come down here and
you are now on our next two videos will
see probably the smarter way to go which
is to convert this to a proper look up
table and will in our next to the OC
power and then our last video will see a
great look up adding array formula which
will create a single cell formula for
each one of the scores alright we'll see
you next video