• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data Bars (%) [SOLVED]

K

Kanagat

New Member
Hi Guru's of Excel,


I have small project which consists of 5 phases with tasks to do (WBS). And I'm using Excel in order to manage it (challenging myself :) )

Excel spreadsheet consists of 2 worksheets

For instance:

1 worksheet (detailed)

WBS 1 Status : Completed/ Late / at risk/ On schedule Task name : Risk analysis

2 worksheet (high level one)

Status of each phase:

My question is "i would like to have data bar on the status of each phase which will show me in percentage completion of each phase.

In order to do that status should read information from sub tasks which have

four statuses completed/ Late / at risk/ On schedule (combobox)


Wherever any changes will be made on 1 worksheet in terms of sub tasks (completed/at risk etc) those changes should be displayed on 2 worksheet in percentage with colors


Lets assume that Completed - green

Late - yellow

at risk - red

on schedule - chose any color


Thank you,

:)
 
Hi Kanagat ,


Is it possible for you to upload a sample workbook with some data in it ?


This forum does not have a provision for either uploading files or attaching files to posts.


What you can do is to use your preferred file-sharing website ( RapidShare , Hotfile , DropBox , SkyDrive , GoogleDocs , SpeedyShare ... ) to upload your file , give others permission to access and download the file , and then post that access link here , in this same topic.


Narayan
 
Hi NARAYANK991


Here the link with an example

https://docs.google.com/file/d/0B88omcoE1QfnYnlpeHJwMHFubnM/edit?usp=sharing


And thank you ))
 
Hi Kanagat ,


Thanks for posting your sample file ; now you have to explain with reference to this file !


Your tab 2 has the following 4 fields : Duration , Risk , Comments , Status. Do you want that only the Status column should be filled in ?


Given that the data on tab 1 is as follows for the task 1 , what should be the output in tab 2 ?

[pre]
Code: 
WBS		Status		Task Name	Dur.	Start	Finish	Work Days	Used Days	Balance	Resource

1	75	On Schedule		1	17	6/1/13	6/18/13	12                		29602	CRM/DB
1	75	On Schedule		2		6/1/13	6/2/13	0                		29590	CRM
X	75	On Schedule		3		6/1/13	6/2/13	0                		29590	DB
1.2	75	On Schedule		4		6/3/13	6/4/13	2                		29592	CRM
X	75	On Schedule		5		6/3/13	6/4/13	2                		29592	DB
[/pre]
Narayan
 
Lets say it that way, to complete one task we have 5 sub tasks (Tab 1)

For instance if one sub task status will be completed in tab 2 status of task will be

20% completed

If all sub tasks will be completed then in tab 2 status of one task will be filled 100%


Hopefully its clear now )))
 
Hi Kanagat ,


Can you explain with reference to your workbook , since it is now available ?


I have posted the data which is available on tab 1 for task 1 ; with reference to this specific data ( not in general ) , can you say what should appear in the Status column in tab 2 against this task ?


Your second column in tab 1 is having 75 against each sub-task ; are these values to be used in arriving at the overall status of task 1 ? If so , what will be the result of 5 sub-tasks having 75 against each of them ?


Narayan
 
Hi NARAYANK991 - Excel ninja )))


https://docs.google.com/file/d/0B88omcoE1QfnQXFUSmc2ZHZwM1U/edit?usp=sharing


please a have a look...
 
Hi Kanagat ,


This is the same file which you uploaded earlier , which I downloaded earlier , and from which I posted the data !


I have in my earlier post asked a specific question ; is this answered in your workbook ?


If not , please answer it here in this topic ; I am repeating this question from my earlier post :



I have posted the data which is available on tab 1 for task 1 ; with reference to this specific data ( not in general ) , can you say what should appear in the Status column in tab 2 against this task ?


Your second column in tab 1 is having 75 against each sub-task ; are these values to be used in arriving at the overall status of task 1 ? If so , what will be the result of 5 sub-tasks having 75 against each of them ?




Narayan
 
Hi NARAYAN,


Well i thought that I've uploaded new one, but it didn't save it oops!


I have posted the data (in terms of data we have 4 options complete/late/ at risk/ on schedule) which is available on tab 1 for task 1 ; with reference to this specific data ( not in general ) , can you say what should appear in the Status column in tab 2 against this task ?

It should calculate

if all tasks completed then status in tab 2 will show 100% and filled with the color.


Complete = 20

Late = - 10

at risk = -20

on schedule = 0


By calculating all the task status it should give the result to the tab 2 status field of the 1 scope


then it should calculate second scope same way.


Than you,

Narayan
 
Hi Kanagat ,


Thanks for the table ; if we consider the specific example of task 1 , then each of the tasks is on schedule ; since you say that if the sub-task is on schedule , it is equal to 0 , adding all of the weightages , we get 0. So should the status cell in tab 2 show 0 % ?


Narayan
 
Yes, you've got it!

But if i will change one of the subtask lets say that it is completed then status

should show 20 % of completed!


Kanagat
 
Hi Kanagat ,


Can you check your file here ?


https://docs.google.com/file/d/0B0KMpuzr3MTVRkJ4V3UzRmZOYk0/edit?usp=sharing


I do not know what is supposed to happen if a task has 6 or more sub-tasks , since in this case , the total can go beyond either +100 or -100.


Narayan
 
Hi Narayan,


Thank you, it works.

"I do not know what is supposed to happen if a task has 6 or more sub-tasks , since in this case , the total can go beyond either +100 or -100." Probably will divide 100 for 6 subtasks )))


Thanks a lot.

Regards,

Kanagat
 
You must log in or register to reply here.
Back
Top