Stacked Bar Chart

[surkdidat]

Apologies if I am being really thick here, but I can't get my head around this!

In Column C (18-21) I have Stage 1, Stage 2, Stage 3 Stage 4
In Column D (18-21) I have a start date -
In Column E (18--21) I have an end date
In Column F (18-21) I have the Days Required - this is how the End Date is worked out.

I want to create a bar graph so that on the left are the stages, and across the bottom are the start dates, so it shows how many days each stage was in progress.

Whatever I select isn't working! Please can someone put me out of my misery and advise how I need to do this!

 

[Caleeco]

Hello,

Could you give a few more details as to what you need plotting? The Y axis can't be the 'Stages' as this is a text value. Below is my best guess?

Caleeco

 

[surkdidat]

Sort of like this (with stage 4 of course, but cut it down for illustration purposes)

Thanks you

Hello,

Could you give a few more details as to what you need plotting? The Y axis can't be the 'Stages' as this is a text value. Below is my best guess?

View attachment 14878

Caleeco

 

[Caleeco]

Ah ok, you want a GANTT chart. This will be more than just a chart as the date range is 'additional data'

I would set up some formulas to evaluate if the date on the bottom row is between the start and end date.

So in CELL I18 i have:

=IF(AND(I$7>=$D18,I$7<=$E18),1,0)

Copy down and then across

Then I have setup conditional formatting on each row.
- If cell = 0 > Text is White
- if Cell = 1 > Text is Chosen Colour & Cell Fill is Chosen Colour.

Half done version shown below:

This will then dynamically update.

Hope that helps
Caleeco

 

[surkdidat]

Ah ok, you want a GANTT chart. This will be more than just a chart as the date range is 'additional data'

I would set up some formulas to evaluate if the date on the bottom row is between the start and end date.

So in CELL I18 i have:

=IF(AND(I$7>=$D18,I$7<=$E18),1,0)

Copy down and then across

Then I have setup conditional formatting on each row.
- If cell = 0 > Text is White
- if Cell = 1 > Text is Chosen Colour & Cell Fill is Chosen Colour.

Half done version shown below:
View attachment 14886


Lovely, I will give it a try in the morning. Thanks in advance for your help!

This will then dynamically update.

Hope that helps
Caleeco

 

[Caleeco]

No worries, give me a shout if you run into trouble with it ?

Cheers
Caleeco

 

[Jon Peltier]

Apologies if I am being really thick here, but I can't get my head around this!

In Column C (18-21) I have Stage 1, Stage 2, Stage 3 Stage 4
In Column D (18-21) I have a start date -
In Column E (18--21) I have an end date
In Column F (18-21) I have the Days Required - this is how the End Date is worked out.

View attachment 14848

I want to create a bar graph so that on the left are the stages, and across the bottom are the start dates, so it shows how many days each stage was in progress.

Whatever I select isn't working! Please can someone put me out of my misery and advise how I need to do this!

So select the tasks and start date columns of the data set (C17D21), then press the ctrl key and select the Days Req. column (F17:F21). (I assume the column headers are in row 17.) Insert a stacked column chart.

 

[surkdidat]

Apologies, what is in I$7?

Ah ok, you want a GANTT chart. This will be more than just a chart as the date range is 'additional data'

I would set up some formulas to evaluate if the date on the bottom row is between the start and end date.

So in CELL I18 i have:

=IF(AND(I$7>=$D18,I$7<=$E18),1,0)

Copy down and then across

Then I have setup conditional formatting on each row.
- If cell = 0 > Text is White
- if Cell = 1 > Text is Chosen Colour & Cell Fill is Chosen Colour.

Half done version shown below:
View attachment 14886


This will then dynamically update.

Hope that helps
Caleeco

 

[Caleeco]

Sorry I had created a copy to match your rows for illustation purposes. I forgot to reset the formulas:
Row 7 was just the row for all the date, so in the screenshot above it should be:

=IF(AND(I$22>=$D18,I$22<=$E18),1,0)

Hopefully that works, let me know if not

Cheers
Caleeco

 

[surkdidat]

Superb, thank you - to be fair that should have been obvious!! (rofl)

Sorry I had created a copy to match your rows for illustation purposes. I forgot to reset the formulas:
Row 7 was just the row for all the date, so in the screenshot above it should be:

=IF(AND(I$22>=$D18,I$22<=$E18),1,0)

Hopefully that works, let me know if not

Cheers
Caleeco