Forum Discussion
Solved
Varying Assigned Values
I have some questions about functions that I can't seem to quite figure out.
I have a spreadsheet for rating vendor performance and it has five sections. Each section is considered a percentage of the whole rating score (sections A - C are each 25% of the total, section D is 15% of the total, and section E is 10% of the total score). In each section, the inadequate rating = 50 point, the below standard is 75 points, standard is 100 points, above standard is 125 points, and superior is 150 points. The point value for each question is then determined by the number of questions in each section. For example, there are 5 questions in section A, so inadequate answers will each equal 10 points, below standard will equal 15, standard is 20, above standard is 25, and superior is 30.
Now... I don't want the values of each answered question to appear, only an "X" on what prints. So, I thought I could select for the rating columns to require either an "X" or to be left blank, and then on a separate tab I could enter a formula for the actual values if an "X" is present. No problem, right? Well, I can't figure out how to generate the formula from one page to the other. Usually, if I want to reference a cell on another page, I just hit =+ and then the cell, but I can't get that to work. Then, I want to add the actual values so that's what shows in the column totals for each section.
There is also a rating column showing and it needs to show the percent of the total section that the question's answer applies to. So, if I marked question 1 in section A as inadequate, then I need the percentage of that section's value to show as 10%.
As if that's not tricky enough, my supervisor wants to add another column for a discretionary rating. So, if he wants to change the total rating percentage of a question/section, he can and it will distribute the values correctly according to how each question is rated. For example, in section A, if all of the questions were rated as standard, then each one would give a 20% total rating for that section. But, if question 2 seems to be much more important than one of the other questions and he wants to bump the percentage up on that question to show as 40% of the total for that section, he wants the remaining 60% to be equally distributed between the remaining values depending on their rating.
Is ANY of this possible?
I have tried to figure out what you are trying to do and have created a sample file. I hope this is a first start to solve your problem.
You can overwrite the values in Sheet1 column D with a value. The other values of the same section will be recalculated.
You can delete the columns J, K, and L of Sheet1. The values are calculated again in Sheet2.
Give me some feedback if you were able to do something with this.
I have tried to figure out what you are trying to do and have created a sample file. I hope this is a first start to solve your problem.
You can overwrite the values in Sheet1 column D with a value. The other values of the same section will be recalculated.
You can delete the columns J, K, and L of Sheet1. The values are calculated again in Sheet2.
Give me some feedback if you were able to do something with this.- Also note that:
Section 1 has 5 questions
Section 2 has 5 questions
Section 3 has 4 questions
Section 4 has 5 questions
Section 5 has 3 questions
Have you been able to review my prior post with my sample table? Below is a sample table of what part of my spreadsheet looks like. This is on the first tab and we will have a large number of people using this. It's going to be an actual form that they will fill out and print, so we want to make it as user-friendly and legible as possible. What I'd like to do is to have the users be able to put an "x" in each of the rating columns and then the section totals and rating areas to fill in automatically with the data from the values on a second or third tab.
The sample file you provided would help, I think, if it was on a different tab. I'm just not sure how to set up that first tab so that it doesn't have a lot of formulas pulling from other areas that are able to be edited or deleted by the end-users. Also on your sample file, if the points column were whole numbers, I think that would help (at least in my mind) to show the totals in the format that the supervisor wanted.
The rating column needs to be the percent of the section that particular line applies to and the section total as a percentage of the performance score should be the portion of the 100% total performance score that section applies to. If my supervisor wants to change the rating percent for the section, he wants the other values to auto-fill. I believe that your sample was allowing column D to be altered, but we need the end percentage in column L to be what is alterable. My supervisor is wanting to do this with an extra column titled "Discretionary Rating" and when he puts something different in there, he wants the other information to automatically fill in correctly.Also, if there is an Inadequate or Below Standard rating for any question in a section, then that section is limited to no higher than a standard rating. Is it possible to do that?
Section A (25%) Inadequate Below Std Standard Above Std Superior Rating Question 1 x 2.5% Question 2 x 3.75% Question 3 x 5.00% Question 4 x 6.25% Question 5 x 7.50% Section A Total 10 15 20 25 30 25%
