Forum Discussion
zero values and charts
I am trying to create a line chart from a conversion table, involving lb to kg, it has five data columns and the rows are in ascending date. when the chart is produced there are zero values due to missing measurements in the reference table, i am using =CONVERT(I5,"lbm","kg") .
The problem is that it seems whatever method i use the chart either continues to plunge to zero where ther is no value or the table hides the zero values and moves the data up to a different day, thereby getting out of order, the five columns are five measurements during the day
I have tried a lot of methods to ignor the zero and continue to the next data point
I am using Office 19 Excel
It's always preferable to attach the actual workbook rather than a mere image. Would you be so kind as to do that, please, That way we can see what you're working with and not have to attempt to recreate it.
Hi, thank you for your quick reply, yes that would make sense, i am now struggling to attach a file, wont paste, file not allowed, and when i try the attachment icon at the bottom of this dialogue box it does nothing, any ideas
I wasn't aware of the button; I've always been able to just "drag and drop" into the area set up just below the text window for that purpose. Give "Drag and Drop" a try.
Something that never used to work but I think does now (at least with 365) is to detect the zeros or blanks and replace them with #N/A. For me, a suitable formula might be
= LET( x, CONVERT(+w,"lbm","kg"), IF(x, x, NA()) )but you will need to perform the conversion twice and nest the formulas.
Otherwise you could filter your data and use a scatter plot or, most labour intensive, interpolate missing points.
Something else to consider is that no longer do old versions of Excel age gracefully. The new versions of Excel render them obsolete, much as they did to the electro-mechanical adding machine or slide rule.
cant attach a file, button does not work
