Excel - SUM Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

Need a spreadsheet for a performance review 1-5 with explanations. I would like to have the scores sum at the end
Solved by K. U. in 50 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 31/07/2018 - 03:45
Hi
User 31/07/2018 - 03:45
Hello
User 31/07/2018 - 03:45
I would like to have either radio buttons or a drop down list in the field next to RATING
Excelchat Expert 31/07/2018 - 03:46
sure
User 31/07/2018 - 03:46
Those are the options
Excelchat Expert 31/07/2018 - 03:47
you are building the excel for the peformace from the start?
User 31/07/2018 - 03:47
To get even more tricky, I would like the value selected in the rating box to convert to a number in C1
User 31/07/2018 - 03:47
Yes, from scratch
Excelchat Expert 31/07/2018 - 03:48
So let start from the top..!st will you have individual sheet for each employee?
Excelchat Expert 31/07/2018 - 03:48
Usually there is a list of enployee followed by a list of things you want to rate them on
Excelchat Expert 31/07/2018 - 03:48
employee
User 31/07/2018 - 03:48
In my perfect world, I will send the spreadsheet to the supervisor that will complete and email back to me.
User 31/07/2018 - 03:49
I will type the name into the name box.
Excelchat Expert 31/07/2018 - 03:49
oh ok then what are the points on which your rated like here you have aAttitude
User 31/07/2018 - 03:49
Everything will be scored 1-5
Excelchat Expert 31/07/2018 - 03:50
so in the drop down you want 1-5 to be displayed along with the explanation?
User 31/07/2018 - 03:50
The "Supervisor Comments" will be a free form box to type anything valuable or to explain.
User 31/07/2018 - 03:50
Yes
Excelchat Expert 31/07/2018 - 03:51
hmm how about next to it?
Excelchat Expert 31/07/2018 - 03:51
can the description come next to the rating box?
User 31/07/2018 - 03:51
I know how to make it a drop down but I don't know how to make the Score number sum what the total is
User 31/07/2018 - 03:51
Yes, next to is perfect!
User 31/07/2018 - 03:51
I just need 3 to be meets expectations, etc.
Excelchat Expert 31/07/2018 - 03:52
got it..
Excelchat Expert 31/07/2018 - 03:52
now Score is just waht you would select in rating?
Excelchat Expert 31/07/2018 - 03:52
1-5?
User 31/07/2018 - 03:53
Yes, the supervisor selects the score in the rating box and then the score adds up all of the different topics (attitude, perfomance, customer service, etc)
Excelchat Expert 31/07/2018 - 03:53
how would you want the topics to be displayed
User 31/07/2018 - 03:53
radio button or drop down.
User 31/07/2018 - 03:54
Oh, topics, will be listed
Excelchat Expert 31/07/2018 - 03:54
where will perfomance come based on that i can build the formula
User 31/07/2018 - 03:54
THey will rate attitude first, then perfomance below and below that will be customer serivce
User 31/07/2018 - 03:55
Make sense/
Excelchat Expert 31/07/2018 - 03:56
yes
Excelchat Expert 31/07/2018 - 03:56
perfect
Excelchat Expert 31/07/2018 - 03:56
so score is individual?
User 31/07/2018 - 03:57
Each topic has an individual and then a Grand total at the bottom
Excelchat Expert 31/07/2018 - 03:57
ok give me few min to work on it please stay here
User 31/07/2018 - 03:57
OK, thanks
User 31/07/2018 - 04:02
No pressure, but time is ticking. What happens at 0:00
Excelchat Expert 31/07/2018 - 04:02
he he :)
[Uploaded an Excel file]
Excelchat Expert 31/07/2018 - 04:02
we get extension for 20 min when clock hits 2
Excelchat Expert 31/07/2018 - 04:02
can you check this excel
User 31/07/2018 - 04:03
OMG it's amazing!!
Excelchat Expert 31/07/2018 - 04:03
:)
User 31/07/2018 - 04:03
If I lock the fields, will they still be able to select a score?
Excelchat Expert 31/07/2018 - 04:04
no
User 31/07/2018 - 04:04
OK, that's fine.
Excelchat Expert 31/07/2018 - 04:04
the cell cannot be protected
User 31/07/2018 - 04:05
Is there a way to alert if they skip a topic?
Excelchat Expert 31/07/2018 - 04:06
hmm i need to change the formula a bit do you want that?
Excelchat Expert 31/07/2018 - 04:06
also if you want to add more topics..copy one of the topics
User 31/07/2018 - 04:06
If it's not too much to ask
Excelchat Expert 31/07/2018 - 04:07
say attitude a16:k21
Excelchat Expert 31/07/2018 - 04:07
then paste it at the bottom
User 31/07/2018 - 04:07
Perfect
Excelchat Expert 31/07/2018 - 04:09
[Uploaded an Excel file]
Excelchat Expert 31/07/2018 - 04:09
[Uploaded an Excel file]
Excelchat Expert 31/07/2018 - 04:09
then right click and do a inset copied cell all the formulas will work
Excelchat Expert 31/07/2018 - 04:10
reference will change automatically
User 31/07/2018 - 04:10
Ok, I'm fairly comfortable with that.
Excelchat Expert 31/07/2018 - 04:10
good let me update the formula then
Excelchat Expert 31/07/2018 - 04:16
just few min there isn't a direct way to do it so just few min to get a hack
User 31/07/2018 - 04:16
OK
User 31/07/2018 - 04:22
What is a Beta Period?
Excelchat Expert 31/07/2018 - 04:26
HI
Excelchat Expert 31/07/2018 - 04:26
Not sure
User 31/07/2018 - 04:26
Hello
Excelchat Expert 31/07/2018 - 04:27
Building formula
Excelchat Expert 31/07/2018 - 04:27
Few more min
User 31/07/2018 - 04:27
OK
Excelchat Expert 31/07/2018 - 04:31
[Uploaded an Excel file]
Excelchat Expert 31/07/2018 - 04:31
can you please check
Excelchat Expert 31/07/2018 - 04:32
you there
User 31/07/2018 - 04:32
Yes, checking now
User 31/07/2018 - 04:33
It looks the same as the first one. Is it suppose to have fireworks or something when they skip lol
Excelchat Expert 31/07/2018 - 04:33
:) no i was trying to get the average of score in the 1st one
Excelchat Expert 31/07/2018 - 04:33
then figured it cannot be one when you skip the ratting as score ia automated to 0
User 31/07/2018 - 04:34
Ok, this will work. Thank you so much for your help
Excelchat Expert 31/07/2018 - 04:34
welcome :)
Excelchat Expert 31/07/2018 - 04:34
please do leave a feedback once you end the session
User 31/07/2018 - 04:34
Will do. thanks again

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.