Hello,
I'm wondering if there is anyone here that can help me with a burn down chart I have built for a project.
It was tough trying to find anything online regarding excel burn down charts so I built one using this youtube tutorial:
" YouTube .com/watch?v=AR2KgOYAqU8&list=PLWd3I4Gj-9XuEPPs1nGTvVK56K3Zc1PFD&index=3 "
I would like to have the ability to dynamically change the burndown chart using slicers for the MS# and Ref# columns though.
I'm looking to find a way to populate the Planned and Actual columns ('Burndown Chart Data') with visible data only once 'Source Data' table is sliced or filtered.
I was thinking of using a SUMPRODUCT, SUBTOTAL and OFFSET formula in the burn down chart table to help fix the COUNTIFS issue with displaying only visible data like in the article below:
" exceljet. net/formula/count-visible-rows-only-with-criteria#:~:text=To%20count%20visible%20rows%20only%20with%20criteria%2C%20you,criteria%20like%20COUNTIF%20or%20SUMIF%20without%20some%20help "
The issue I'm finding is that the COUNTIFS formula counts all data in the 'Source Data' Planned and Actual columns and not just visible (sliced) data.
I'm having issued trying to get it to display correctly. Just wondering if some of the Excel wizards on here can find a solution.
Here is a link to the example Excel spreadsheet on onedrive:
This is how the Burn down chart is setup:
Data from a SharePoint list is imported into Excel with Power Query and then displayed in the 'Source Data' table
The 'Burndown Chart Data' table then extracts the Planned and Actual dates from 'Source Date' and counts the dates within the range of the Date field in 'Burndown Chart Data'.
The formula used for this is:
Planned Completion:
=COUNTIFS('Source Data'!F:F,">="&A3,'Source Data'!F:F,"<="&MAX(A$1:A$29))
Actual Completion:
=IF(A3>TODAY(),NA(),MAX(B$1:B$29)-COUNTIFS('Source Data'!G:G,">="&$A$3,'Source Data'!G:G,"<="&A3))
The issue I'm finding is that the COUNTIFS formula counts all data in the 'Source Data' Planned and Actual columns and not just visible (sliced) data.
The formula I was modifying and hoping to display visible data only was something like this:
=SUMPRODUCT(('Source Data'!F:F,">="&A3,'Source Data'!F:F,"<="&MAX(A$1:A$29))*(SUBTOTAL(103,OFFSET(A3,ROW('Source Data'!F:F)-MIN(ROW('Source Data'!F:F)),0))))
FYI I have also posted this to 1 other Excel forum:
Thanks for your help!
-Jason
I'm wondering if there is anyone here that can help me with a burn down chart I have built for a project.
It was tough trying to find anything online regarding excel burn down charts so I built one using this youtube tutorial:
" YouTube .com/watch?v=AR2KgOYAqU8&list=PLWd3I4Gj-9XuEPPs1nGTvVK56K3Zc1PFD&index=3 "
I would like to have the ability to dynamically change the burndown chart using slicers for the MS# and Ref# columns though.
I'm looking to find a way to populate the Planned and Actual columns ('Burndown Chart Data') with visible data only once 'Source Data' table is sliced or filtered.
I was thinking of using a SUMPRODUCT, SUBTOTAL and OFFSET formula in the burn down chart table to help fix the COUNTIFS issue with displaying only visible data like in the article below:
" exceljet. net/formula/count-visible-rows-only-with-criteria#:~:text=To%20count%20visible%20rows%20only%20with%20criteria%2C%20you,criteria%20like%20COUNTIF%20or%20SUMIF%20without%20some%20help "
The issue I'm finding is that the COUNTIFS formula counts all data in the 'Source Data' Planned and Actual columns and not just visible (sliced) data.
I'm having issued trying to get it to display correctly. Just wondering if some of the Excel wizards on here can find a solution.
Here is a link to the example Excel spreadsheet on onedrive:
This is how the Burn down chart is setup:
Data from a SharePoint list is imported into Excel with Power Query and then displayed in the 'Source Data' table
The 'Burndown Chart Data' table then extracts the Planned and Actual dates from 'Source Date' and counts the dates within the range of the Date field in 'Burndown Chart Data'.
The formula used for this is:
Planned Completion:
=COUNTIFS('Source Data'!F:F,">="&A3,'Source Data'!F:F,"<="&MAX(A$1:A$29))
Actual Completion:
=IF(A3>TODAY(),NA(),MAX(B$1:B$29)-COUNTIFS('Source Data'!G:G,">="&$A$3,'Source Data'!G:G,"<="&A3))
The issue I'm finding is that the COUNTIFS formula counts all data in the 'Source Data' Planned and Actual columns and not just visible (sliced) data.
The formula I was modifying and hoping to display visible data only was something like this:
=SUMPRODUCT(('Source Data'!F:F,">="&A3,'Source Data'!F:F,"<="&MAX(A$1:A$29))*(SUBTOTAL(103,OFFSET(A3,ROW('Source Data'!F:F)-MIN(ROW('Source Data'!F:F)),0))))
FYI I have also posted this to 1 other Excel forum:
Project Burndown Chart- Slice and display only visible data
Hello, I'm wondering if there is anyone here that can help me with a burn down chart I have built for a project. It was tough trying to find anything online regarding excel burn down charts so I built one using this youtube tutorial: " YouTube...
www.excelforum.com
Thanks for your help!
-Jason
