Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Assigning percentage values?
This scenario is a bit tricky, but I hope someone can figure it out.
On worksheets 2, 3, and 4, I have lists of tasks which, for the purposes of this example, are 60, 30, and 10 items long, respectively, for a total of 100 tasks. I've used the =IF function to make them checklists that I'll fill out as certain tasks are fulfilled. On worksheet 1, I've made a chart listing completion percentages of the three task charts: Set 1: 100% Set 2: 0% Set 3: 0% Total: ???% Now, I know that I can use =AVERAGE to find out my average completion percentage. However, I know that completing 100% of Set 1 is equal to 60% overall completion (60/100 tasks complete). By using =AVERAGE, it will will me that I'm 33.3% complete, which is incorrect. I would like to know what function will make the total percentage reflective of that. As a side note, I'm aware that in the example given there are 100 tasks and that I could easily make a function that adds completed tasks from the three worksheets together and divides by 100 to get an overall pecentage even to what I'm describing. In my actual workbook, however, that is not possible, as different each task set is worth a SPECIFIC percentage. I hope someone can make sense of this and help me out. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Assigning percentage values?
If you've got your weightings (60, 30, 10) in A2:A4, and your completions
per task (100%, 0%, 0%) in B2:B4, your overall completion percentage is =SUMPRODUCT(A2:A4,B2:B4)/SUM(A2:A4) (formatted as a percentage) -- David Biddulph "BFrancis" wrote in message ... This scenario is a bit tricky, but I hope someone can figure it out. On worksheets 2, 3, and 4, I have lists of tasks which, for the purposes of this example, are 60, 30, and 10 items long, respectively, for a total of 100 tasks. I've used the =IF function to make them checklists that I'll fill out as certain tasks are fulfilled. On worksheet 1, I've made a chart listing completion percentages of the three task charts: Set 1: 100% Set 2: 0% Set 3: 0% Total: ???% Now, I know that I can use =AVERAGE to find out my average completion percentage. However, I know that completing 100% of Set 1 is equal to 60% overall completion (60/100 tasks complete). By using =AVERAGE, it will will me that I'm 33.3% complete, which is incorrect. I would like to know what function will make the total percentage reflective of that. As a side note, I'm aware that in the example given there are 100 tasks and that I could easily make a function that adds completed tasks from the three worksheets together and divides by 100 to get an overall pecentage even to what I'm describing. In my actual workbook, however, that is not possible, as different each task set is worth a SPECIFIC percentage. I hope someone can make sense of this and help me out. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Assigning a set value to a cell that has a percentage? | Excel Worksheet Functions | |||
Assigning point values? | Excel Worksheet Functions | |||
Assigning values to words | Excel Discussion (Misc queries) | |||
Assigning values to letters | Excel Worksheet Functions | |||
assigning values by intervals | Excel Worksheet Functions |