Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
weighting a value?
Let's say I have 10 hours to accomplish 10 tasks. Having previously accomplished each of these tasks I know some will take longer than 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. What Excel trick, or someones know how, would be used to spread my 10 hours appropriately over the 10 tasks? -- jvan100 ------------------------------------------------------------------------ jvan100's Profile: http://www.excelforum.com/member.php...o&userid=29223 View this thread: http://www.excelforum.com/showthread...hreadid=489476 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
weighting a value?
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) -- Gary's Student "jvan100" wrote: Let's say I have 10 hours to accomplish 10 tasks. Having previously accomplished each of these tasks I know some will take longer than 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. What Excel trick, or someones know how, would be used to spread my 10 hours appropriately over the 10 tasks? -- jvan100 ------------------------------------------------------------------------ jvan100's Profile: http://www.excelforum.com/member.php...o&userid=29223 View this thread: http://www.excelforum.com/showthread...hreadid=489476 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
weighting a value?
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 "jvan100" wrote: Let's say I have 10 hours to accomplish 10 tasks. Having previously accomplished each of these tasks I know some will take longer than 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. What Excel trick, or someones know how, would be used to spread my 10 hours appropriately over the 10 tasks? -- jvan100 ------------------------------------------------------------------------ jvan100's Profile: http://www.excelforum.com/member.php...o&userid=29223 View this thread: http://www.excelforum.com/showthread...hreadid=489476 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
weighting a value?
"jvan100" wrote:
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? 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Percentage doesn't add up | Excel Discussion (Misc queries) | |||
How to apply a weighting | Excel Discussion (Misc queries) | |||
Moving averages in Excel | Excel Discussion (Misc queries) | |||
more than one IF | Excel Discussion (Misc queries) |