Try this (I have adopted Harlan's LOOKUP function approach)... assuming your
data starts in Row 2, put these formulas in the indicated cells and copy
them down as far as required...
F2: =IF(E2="Yes",2,0)
H2: =LOOKUP(G2,{0;1;3;6},{0;2;4;6})
J2: =LOOKUP(I2,{0;1;3;6},{0;2;4;6})
L2: =LOOKUP(K2,{0;1;3;6},{0;2;4;6})
N2: =LOOKUP(M2,{0;1;3;6},{0;2;4;6})
O2: =F2+H2+J2+L2+N2
where Column O is assumed to be your total column.
Rick
"Rita" wrote in message
...
After the "Yes No" columns, there are 4 questions that have the same
potential answers. Yes they are, as you described, G/H, I/J, K/L and M/N,
then the "Total" column. They will ALWAYS remain fixed. Again, thanks to
you
both!!!!!
--
Rita
"Rick Rothstein (MVP - VB)" wrote:
Okay, I think we are getting closer.<g Would the next repeated columns
be I
and J, then the next ones after that K and L, etc.? If so, for how many
questions? Is this number of questions fixed? Are they **always** going
to
be fixed? What column are your totals in? Is that "totals column"
**always**
going to be that column? I think Harlan's on the right track, we just
need
to understand your column layout.
Rick
"Rita" wrote in message
...
Sure, sorry. I always wish there was a way to upload examples. What I
am
trying to say in that statement is that there are more columns but they
would
just repeat column G and H.
--
Rita
"Rick Rothstein (MVP - VB)" wrote:
The thing you have to keep in mind when you are asking a question on a
newsgroup is the people you are asking have no idea what your data
looks
like or how it is arranged on the worksheet; so, you must tell us, in
detail... remember, it is obvious to you (because it is your data and
worksheets) but it is completely unknown to us... we only know what
you
tells. With that said, can you clarify what you mean by "The last two
columns are repeated for several different questions"? Remember... in
detail
please.
Rick
"Rita" wrote in message
...
I think I need to be more specific. This is to compile information
from
questionaires for job postings. Column E can be either yes or no and
we
want
a point (either zero or 2) in column F, column G can have any of the
following anwers; none (zero pts), 1-2 years (2 pts.), 3-5 years (4
pts.),
6+
years (6 pts). Then column H is for those points to be filled in.
The
last
two columns are repeated for several different questions. Then at
the
end
there is a Total column that all the points in each row would be
added
up.
I
hope that makes more sense. Sorry, I'm not very good at explaining
this.
Thanks for your trouble.
--
Rita
"Rick Rothstein (MVP - VB)" wrote:
Give this a try... assuming your data starts in Row 2, put this on
Row
2
in
whatever column you want your total points in...
=IF(A2="Yes",2,0)+IF(B2=0,0,IF(B2<=2,2,IF(B2<=5,4, 6)))
and copy it down as far as necessary.
Rick
"Rita" wrote in message
...
o (Zero) = 0
1-2 = 2
3-5 = 4
6+ = 6
Yes there is another column that we want to have the sum of those
columns
in
each row.
Thanks
--
Rita
"Rick Rothstein (MVP - VB)" wrote:
I think if you tell use what all the point values are for your
Column
2
answers, that might be helpful.
Also, is the final answer you are looking for the sum of Column
1
and
Column
2 on a row per row basis?
Rick
"Rita" wrote in message
...
i am using Excel 2007 and XP. I really need some help with
formulas
to
do
the
following:
column 1: Has yes or no and we want to apply 2 points if yes
and
0
if
no.
column 2: Has 4 possible answers, 0 (zero), 1-2, 3-5 or 6 plus
which
each
are assigned a point value, such as 1-2 is 2 points. We need a
formula
to
put
in the appropriate points.
I hope I explained this clearly. Any help is really
appreciated.
Thanks
so
much
--
Rita