Help with Scores and N/A on a speadsheet

Andrew Poulsom

MrExcel MVP

Joined

Jul 21, 2002

Messages

73,092

• Jun 11, 2014

• #2

Formula in C2 copied down:

Excel 2010
	A	B	C
1	Question	Result	Score
2	Q1	YES	16.67
3	Q2	NO	16.67
4	Q3	YES	16.67
5	Q4	#N/A	0.00
6	Q5	YES	16.67
7	Q6	NO	16.67
8	Q7	YES	16.67
Sheet1

Cell Formulas
Range	Formula
C2	=ISTEXT(B2)*100/COUNTIF(B$2:B$8,"<>#N/A")

 

Upvote 0

M

muzzy

Active Member

Joined

Apr 8, 2014

Messages

333

• Jun 11, 2014

• #3

Thanks that look good But they change there mind now they are looking for this option now. Sorry for this but how wound I be able to add these to the formulas can you help me with it. Thanks very much.

Answer	Points
Exceeds Expectations	16
Meets Expectations	14.29
Needs Improvement	7.15
Did not Meet Expectations	0
N/A

<colgroup><col><col></colgroup><tbody>
</tbody>

 

Upvote 0

Andrew Poulsom

MrExcel MVP

Joined

Jul 21, 2002

Messages

73,092

• Jun 11, 2014

• #4

It seems you can use VLOOKIP:

http://www.contextures.com/xlFunctions02.html

 

Upvote 0

M

muzzy

Active Member

Joined

Apr 8, 2014

Messages

333

• Jun 11, 2014

• #5

I am sorry I am lost

 

Upvote 0

Andrew Poulsom

MrExcel MVP

Joined

Jul 21, 2002

Messages

73,092

• Jun 11, 2014

• #6

What don't you understand in the link I posted?

 

Upvote 0

M

muzzy

Active Member

Joined

Apr 8, 2014

Messages

333

• Jun 11, 2014

• #7

I look at the video for the vlookup but when I try to add it to the formulas I can not get it to work. and Question when I add the vlookup will it override the n/a part. I still need the n/a to work. like you does that works great.

 

Upvote 0

Andrew Poulsom

MrExcel MVP

Joined

Jul 21, 2002

Messages

73,092

• Jun 11, 2014

• #8

Please post some sample data and the results you expect.

 

Upvote 0

M

muzzy

Active Member

Joined

Apr 8, 2014

Messages

333

• Jun 11, 2014

• #9

Here is the sample but I still need the N/A to work for this.

Question	Answer	Score
How well did we meet the customer’s needs?	Meets	14.29
How well did we eliminate the risk of losing the customer?	Needs Improvement	7.14
How well did our recommendation improve the customer experience?	Did not meet	0
How well did we show genuine interest?	N/A	0
How well did we position ourselves well against the competition?	Meets	14.29
How useful and accurate are the notes and call target left for the next call?	Meets	14.29
Was the correct disposition selected?	Needs Improvement	7.14

<tbody>
</tbody>

Totals 75.00

I still need when the answer is n/a it will add the score to the raise of the score that are not 0

This is the results

Answer	Score
Meets	17.86
Needs Improvement	10.71
Did not meet	0
N/A	0
Meets	17.86
Meets	17.86
Needs Improvement	10.71

<tbody>
</tbody>

Total 75.00

 

Upvote 0

Andrew Poulsom

MrExcel MVP

Joined

Jul 21, 2002

Messages

73,092

• Jun 11, 2014

• #10

Why are you adding 3.57 (25/7)?

 

Upvote 0