weighting a value?

J

jvan100

Let's say I have 10 hours to accomplish 10 tasks. Having previousl
accomplished each of these tasks I know some will take longer tha
others. Let's say I want to weight them into three categories,
easiest receives a weight of 1, harder 1.5 and harder yet 2.5. Wha
Excel trick, or someones know how, would be used to spread my 10 hour
appropriately over the 10 tasks
 
G

Gary''s Student

Convert weights into percentages:

t1 1 22.22% 2.222222222
t2 1.5 33.33% 3.333333333
t3 2 44.44% 4.444444444
4.5
This example is for three tasks only
Column A has the task name
Column B has your weights ( row #4 has the sum of the above)
Column C has the percents (=B1/$B$4 in C1)
Column D has the hour breakdown ( =10*C1 in D1)
 
S

Sloth

If I understand you correctly you need to multiply the score by 10/X where X
is the sum of the scores. This will convert the wheight to a time. In the
example below I randomly inserted the scores and they summed to 15. I then
multiplied each number by 10/15 to get the bottom list. The sum of the new
list equals 10. So in this example items with 1 recieve 2/3 of one hour, 1.5
recieve one hour, and 2.5 recieve 2 2/3 of one hour. I hope this is what you
wanted.

1
1.5
2.5
1.5
1
2.5
1
1
1.5
1.5
15

0.666666667
1
1.666666667
1
0.666666667
1.666666667
0.666666667
0.666666667
1
1
10
 
J

joeu2004

jvan100 said:
Let's say I have 10 hours to accomplish 10 tasks.
[....] Let's say I want to weight them into three
categories, easiest receives a weight of 1, harder
1.5 and harder yet 2.5. What Excel trick, or
someones know how, would be used to spread
my 10 hours appropriately over the 10 tasks?
Click to expand...

Suppose you have tasks t1, t2, ..., tN with
corresponding weights w1, w2, ..., wN. In your case,
some of the wX values are the same, selected from
1, 1.5 and 2.5. If the total time is T, then:

t1 = T*w1/SUM(w1:wN)
t2 = T*w2/SUM(w1:wN)
....
tN = T*wN/SUM(w1:wN)

It is important to note that you sum all N weights,
not just the 3 categories of weights in your case.
 
Top