Excel Formula for calculating point values

June 8, 2009 at 08:58:00
Specs: Windows XP
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


See More: Excel Formula for calculating point values

Report •


#1
June 8, 2009 at 09:39:31
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


Report •

#2
June 8, 2009 at 09:51:58
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.

Report •

#3
June 8, 2009 at 10:08:24
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.


Report •

Related Solutions


Ask Question