Hi, I'm trying to write a formula that will calculate "points" for a numeric value that is weighted when a user inputs a number in an adjacent cell.

For ex: a user gives a "4" to a characteristic which has a 15% weight, it translates to "12 points" or if a user gives a "2" to a 20% weighted characteristic, it gives "8 points". The weights add up to 100% but the total points don't have to (unless every characteristic has a "5" - the highest point value possible.)

If anyone can help, I'd appreciate it. Thanks

Based on your examples, the weight appears to be divided by 5 and then multiplied by the user input. A B C D 1 User 2 Weight Input Score Formula 3 15 4 12 =A3/5*B3 4 20 2 8 =A4/5*B4

Thank you, sir. I knew the weight looked divisibile by 5, I just couldn't get my head wrapped around why. I thought the points would be calculated differently based on the different weighting.

In essence, they are. An input of 4 for a 15 weight is not as "important" as an input of 4 for a 20 weight - 12 vs 16 in the final calculation.

The only other way to make one question more important than another would be to have (for example) 1 - 5 as the available inputs for one question and 1 - 7 as the available inputs for another.

That's a lot more complicated especially if you are assigning text to the inputs (Very Unhappy, Somewhat Happy, Estatic, etc.)

It's also easier to modify the survey by changing the questions and the associated weightings than it is to change the questions and the number of available inputs.

Ask Your Question

Weekly Poll

Would you ride in a self-driving car from Tesla?

Discuss in The Lounge

Poll History