Need help with formulas

[Rita]

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

 

[Rick Rothstein \(MVP - VB\)]

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]

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

 

[Rick Rothstein \(MVP - VB\)]

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]

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.

 

[Rick Rothstein \(MVP - VB\)]

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

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]

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.

 

[Harlan Grove]

I think I need to be more specific.
Indeed.

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). . . .

I'll assume row 5 for example formulas. For col E,

=IF(E5="Yes",2,0)

For col F,

=LOOKUP(F5,{0;1;3;6},{0;2;4;6})

Then column H is for those points to be filled in.

Meaning the formula in H5 should be the sum of the points from the
answers in E5 and F5? If so, H5 should be

=IF(E5="Yes",2,0)+LOOKUP(F5,{0;1;3;6},{0;2;4;6})

The last two columns are repeated for several different questions. . . .

Unclear.

Repeated how?

Do you mean there are answers for different questions in columns E and
F in additional rows, or are, say, columns J and K used for another
pair of answers with corresponding points in column M, and columns O
and P for another pair of answers with corresponding points in column
R, etc?

Then at the end there is a Total column that all the points in each row would be added up.

....

This makes it seem like the latter - multiple groups of 4 columns
(with blank columns between groups of answers and corresponding
points) all on the same row. If so, and the points were in every 5th
column, so in columns H, M, R, W, AB, AG, AL, etc, then the total
points for a row could be calculated using the formula (again row 5)

=SUMPRODUCT(--(MOD(COLUMN(H5:AL5),5)=3),E5:AL5)

 

[Rick Rothstein \(MVP - VB\)]

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]

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!!!!!

 

[Rick Rothstein \(MVP - VB\)]

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