Gantt chart - need help understanding how the formula works

[J Singh]

Hi

Could you please explain how the following formula works - what does "fillblock" in the formula means? Tried to make sense of how the formula works but couldn't…

=IF(AND(COLUMNS($F$5:H6)>=$C6,COLUMNS($F$5:H6)<=$D6),IF(COLUMNS($F$5:H6)-$C6<ROUND(($D6-$C6+1)*$E6,0),fillblock,""),"")



thanks

 

[Somendra Misra]

Hi Singh,

The formula has two IF condition checking as below:

=IF(AND(COLUMNS($F$5:H6)>=$C6,COLUMNS($F$5:H6)<=$D6),IF(COLUMNS($F$5:H6)-$C6<ROUND(($D6-$C6+1)*$E6,0),fillblock,""),"")

The red portion is checking if the cell is between the start and end week mention in column C and column D.

If above is YES then it will check the blue portion that is checking how much percentage of job is completed. So say a task is for five week. CF will put five cell with blue colour. And each cell is 20% of the task so if %age completion is 40% so the blue part will put the special character in the cell this is the special character
█

defined through fillblock in name manager.

Access the name manager through formula tab or keyboard shortcut Ctrl+F3, you can see there.

If any of the above condition is not TRUE than the formula will put a null string "" in the cell.

Hope this will clear the formula.

Regards,

 

[J Singh]

Hi Singh,

The formula has two IF condition checking as below:

=IF(AND(COLUMNS($F$5:H6)>=$C6,COLUMNS($F$5:H6)<=$D6),IF(COLUMNS($F$5:H6)-$C6<ROUND(($D6-$C6+1)*$E6,0),fillblock,""),"")

The red portion is checking if the cell is between the start and end week mention in column C and column D.

If above is YES then it will check the blue portion that is checking how much percentage of job is completed. So say a task is for five week. CF will put five cell with blue colour. And each cell is 20% of the task so if %age completion is 40% so the blue part will put the special character in the cell this is the special character
█

defined through fillblock in name manager.

Access the name manager through formula tab or keyboard shortcut Ctrl+F3, you can see there.

If any of the above condition is not TRUE than the formula will put a null string "" in the cell.

Hope this will clear the formula.

Regards,

Thanks Somendra Mishra - thanks for explaining how the IF statements and conditional formatting works - Sorry I should have been more clear in my ask, i want to know how excel knows to only fill partial cell and not the full cell if % task completed is anything but 100%

 

[NARAYANK991]

Hi Singh ,

You should be able to figure this out yourself ; just take a closer look at the following :

1. The formula does nothing but fill the relevant cells with the character represented by the named range fillblock. This is a vertical block.

2. There is a range format which does something ; select the cells and look at every aspect of their format.

3. The conditional formatting , in this case , is very simple ; it does nothing but ....

To understand what is happening , first clear the range of its conditional formatting rules , and see what is displayed.

Narayan

 

[Somendra Misra]

Hi Singh,

Sorry for not understanding your query, but I don't think the filling of block is appropriate, Let us say:

You have a task of one week Activity of row 11, if you put 50% than the block will be there giving a wrong visual information that the task is completed.

Consider another scenario: let us consider the same activity is for two week, so first block will come after 30% and second one will come at 80% so a difference of 50%. I don't think that's the correct information.

Compare it with the CF of Data Bar.

Regards,