Forum Discussion

Ste191194's avatar
Ste191194
Copper Contributor
Jan 09, 2022

Running total PIVOT line chart continuing after no data

Hi 

Sorry if this has already been posted, I've been looking all over for a solution. 

 

I have a set of financial data; prior year and YTD.  I am using a running total for the YTD, but it continues on the line chart after there is no data and just shows a straight line.  I am using it for an interactive summary. 

 

Is there a way to stop showing the line when there is no additional data to show? 

 

Any help would be greatly appreciated. 

Thanks

Stephen

Charting
excel
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Jan 10, 2022

    Ste191194 You need to alter the running total formula to return NA() when the current month is empty. That will return #N/A in the data series which will be ignored by the chart.

    Edit: Perhaps a running total formula like below is more elegant than the one shown in the picture.

    =IF(ISBLANK(E4),NA(),SUM($E$4:E4))

Resources