Using the IF, Else function when createing a Pivot Chart in VBA

I would suggest making a PROCESSED_DATA worksheet that does this for you with EXCEL logic, then you can link your chart to THAT data.

Raw data -> Processed Chart Data -> Chart

Use EXCEL to process the data before you put it in a chart, instead of using VBA.
Create a dataset that takes care of the YTD calcs. Then use THAT dataset to make the chart, instead of the RAW data.

Then add the logic to the raw dataset in a formula. I disagree with your statements generally but I have no idea what you are working with.
I have done this before multiple times with excel and/or pivot tables and/or powerpivot and/or VBA always looking for the most simple solution.

Attach a sample workbook if you want more specific advice. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

So I am not exactly sure what you are trying to do but I showed you a step in the right direction.

Find the MAX week number in 2016 that has data, I used a pivot table for that but there are plenty of ways. Then use that to include the data in a YTD_VALUE column in the raw data.
Then you can pivot and see just what you want.

If you wanted you could THEN use VBA to pull out anything that is NOT 0 in the YTD calc and pivot on THAT data for a clean chart.


Previously I think I used PowerPivot to do the heavy lifting for this the last I solved it.

Also if you save a large file as .XLSB it compressed it more so you don't have to zip it.