Dliminate a slope on an Excel graph for values that have not yet been inputted (0 values)

Hi!

I have an Excel graph that receives weekly data input. Currently, the graph simply reverts to 0 or the lowest value on the Y-axis for the next point yet to be inputted.

Is it possible to hide the slope until data is inputted for the corresponding week?

Picture of graph:
Sk?rmbillede 2024-03-28 kl. 18.56.49.png

Graph data:
Sk?rmbillede 2024-03-28 kl. 18.34.47.png


Each of the cells has a formula similar to this, only criteria and week can change. I tried using NA or "" to make the graph ignore the 0 values but no luck.

Please Login or Register  to view this content.

If the chart is built on an Excel table, it will grow as you enter new rows to the table. If you insist on having the data in a fixed range as implied by one of the pictures, you can overlay the data with a named dynamic range that encompasses non-zero data. Since you did not attach a sample workbook, I can't show you how to do that.

Originally Posted by dflak

If the chart is built on an Excel table, it will grow as you enter new rows to the table. If you insist on having the data in a fixed range as implied by one of the pictures, you can overlay the data with a named dynamic range that encompasses non-zero data. Since you did not attach a sample workbook, I can't show you how to do that.

Hi Dflak,

I found the issue to be the file size. I had to reduce the number of sheets so the graphs may look a bit funky. Hope you can help now.

I believe that this is an example of Dflak's suggestion of using defined named range for the Squat amounts.
Refers To for Squat named range: =OFFSET('1RM Tilbageskrivning'!$Y$15,0,0,COUNTIFS('1RM Tilbageskrivning'!$Y$15:$Y$26,">0",'1RM Tilbageskrivning'!$Y$15:$Y$26,"<>"))
Series Values: ='SAMPLE GRAPH4.xlsx'!Squat
Refers To for Styrke named range: =OFFSET('1RM Tilbageskrivning'!$W$15,0,0,COUNTIFS('1RM Tilbageskrivning'!$Y$15:$Y$26,">0",'1RM Tilbageskrivning'!$Y$15:$Y$26,"<>"))
Axis Lable Range: ='1RM Tilbageskrivning'!Styrke
Let us know if you have any questions.

Hi Jete!

Thanks to you both for your input. How the attached sample functions perfectly fits my needs.

However, I am encountering difficulties integrating it into my primary workbook. I tried to replicate the steps, but I encountered errors when inputting the Y-axis data "='SAMPLE GRAPH4 (Excel_Struggler).xlsx'!Squat" (I controlled for the different file name).

Would you mind taking another look? This time it's the main workbook. Bear in mind that the file name is different and also that week 1-11 is named "Styrke, X week" and the last three weeks are named "Peaking, X week". If it's not possible to integrate two different week names into the same graph, feel free to change the entire range to "Styrke".

I changed the scope of the Styrke named range from workbook to 1RM Tilbageskrivning.
I then changed the horizontal axis range to: ='1RM Tilbageskrivning'!Styrke
Let us know if you have any questions.

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.