Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 01-03-2019, 10:18 AM
Trapshooter1956 Trapshooter1956 is offline Windows 7 32bit Office 2007
Novice
 
Join Date: Mar 2017
Posts: 21
Trapshooter1956 is on a distinguished road
Default Chart question


I have a chart showing the history of solids retention time related to the consentration of incomig flow of a waste water plant. We input data daily. the issue i'm having is at the end of the daily input the chart lines drop to zero for the remainder of the chart rather than teminating at the end of data entries.
Reply With Quote
  #2  
Old 01-04-2019, 12:21 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Windows 7 64bit Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,335
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
Default

Could it be that your data range includes some not yet used cells?
Perhaps use a dynamic range instead which will adapt to the added data (using Insert Table perhaps)
See https://www.contextures.com/xlNames01.html#Dynamic for suggestions


If need be post a sample sheet with some ( mock-up ) data showing the problem
__________________
Problem solved ? Let others know by clicking " Thread Tools" then " Mark thread as solved".( This can be undone if need be)
Want to thank for the help received ? Click the scales symbol in the upper right corner of a post from the person you want to thank.
Reply With Quote
  #3  
Old 01-04-2019, 12:25 AM
ArviLaanemets ArviLaanemets is offline Windows 8 Office 2016
Expert
 
Join Date: May 2017
Posts: 409
ArviLaanemets will become famous soon enough
Default

Not sure how are you data organized, and what you really want to get. In attached workbook are some dummy examples - maybe they are useful!
Attached Files
File Type: xlsx DynamicsGraphExamples.xlsx (33.4 KB, 7 views)
Reply With Quote
  #4  
Old 01-04-2019, 07:21 AM
Trapshooter1956 Trapshooter1956 is offline Windows 7 32bit Office 2007
Novice
 
Join Date: Mar 2017
Posts: 21
Trapshooter1956 is on a distinguished road
Default I wonder

if there are formulas in the cells would the chart think that was data and bring in a zero to the chart?
Reply With Quote
  #5  
Old 01-04-2019, 08:19 AM
ArviLaanemets ArviLaanemets is offline Windows 8 Office 2016
Expert
 
Join Date: May 2017
Posts: 409
ArviLaanemets will become famous soon enough
Default

Do you want the chart ignore whenever there is value = 0 (when you want this, you must be 100% sure, there never will be value 0 measured), or when there is no value (i.e no measurement was made at given date, and the ProbeValue cell is empty)?

To force line graph to interpolate missing data, in source table must be "#N/A" instead of measurement for break date. E.g. in table tRep1 in column ProbeValue use formula
Code:
=IF(COUNTIFS(tData[ProbeDate],[@ProbeDate],tData[ProbePlace],nRep1Place,tData[ProbeValue],"")=1,NA(),SUMIFS(tData[ProbeValue],tData[ProbeDate],[@ProbeDate],tData[ProbePlace],nRep1Place))
instead.

The only way to have the line graph to have break instead of interpolation I found in google, was to fix it in certain non-dynamic chart.

I'm almost sure I cold make a line graph to have a break instead of interpolation in dynamic graph years ago, but I don't remember anymore how I did it, and I didn't find anything in google too. Maybe it was possible in older versions (used some 15 years ago).
Reply With Quote
  #6  
Old 01-04-2019, 08:35 AM
Trapshooter1956 Trapshooter1956 is offline Windows 7 32bit Office 2007
Novice
 
Join Date: Mar 2017
Posts: 21
Trapshooter1956 is on a distinguished road
Default

Work book is too big to upload. I will do some modifications to it and see if I can get it small enough.
Reply With Quote
  #7  
Old 01-04-2019, 11:53 AM
Trapshooter1956 Trapshooter1956 is offline Windows 7 32bit Office 2007
Novice
 
Join Date: Mar 2017
Posts: 21
Trapshooter1956 is on a distinguished road
Default

Here is the condensed version of the chart i am working with.
As you can see the line representing SRT continues to the end of the year even thou there is no data. The only difference I can see is that the cells the SRT is drawing from have formulas in them to pull data from the input sheet and the 30 min does not.
Is that an issue?
Thanks.
Attached Files
File Type: xlsx Copy of 2019 Process Control Graph issues.xlsx (321.3 KB, 4 views)
Reply With Quote
  #8  
Old 01-05-2019, 01:55 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Windows 7 64bit Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,335
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
Default

Please take a moment to read post #2 which you did not even bother to acknowledge and use Dynamic Ranges instead of entire columns
Thank you
__________________
Problem solved ? Let others know by clicking " Thread Tools" then " Mark thread as solved".( This can be undone if need be)
Want to thank for the help received ? Click the scales symbol in the upper right corner of a post from the person you want to thank.
Reply With Quote
  #9  
Old 01-07-2019, 07:47 AM
Trapshooter1956 Trapshooter1956 is offline Windows 7 32bit Office 2007
Novice
 
Join Date: Mar 2017
Posts: 21
Trapshooter1956 is on a distinguished road
Default

Sorry about missing post 2.
I'm not in the office full time and must have scrolled past it with out realizing it.
I will study it and see if I can figure it out.
Thanks.
Reply With Quote
  #10  
Old 01-08-2019, 06:22 AM
ArviLaanemets ArviLaanemets is offline Windows 8 Office 2016
Expert
 
Join Date: May 2017
Posts: 409
ArviLaanemets will become famous soon enough
Default

Do you read answers sometimes?
Attached Files
File Type: xlsx 2019 Process Control Graph issues.xlsx (301.0 KB, 2 views)
Reply With Quote
  #11  
Old 01-08-2019, 07:59 AM
Trapshooter1956 Trapshooter1956 is offline Windows 7 32bit Office 2007
Novice
 
Join Date: Mar 2017
Posts: 21
Trapshooter1956 is on a distinguished road
Default

Yes I read every response. I've had no formal education in Excel so sometimes I have to study the answers until they make since to me, which sometimes never happens.
I appreciate all responses and didn't intend to offend anyone by not acknowledging their response.
Thanks.
Reply With Quote
  #12  
Old 01-08-2019, 02:01 PM
ArviLaanemets ArviLaanemets is offline Windows 8 Office 2016
Expert
 
Join Date: May 2017
Posts: 409
ArviLaanemets will become famous soon enough
Default

No offense taken - simply confused!

Did you get what you searched for?
Reply With Quote
  #13  
Old 01-08-2019, 02:05 PM
Trapshooter1956 Trapshooter1956 is offline Windows 7 32bit Office 2007
Novice
 
Join Date: Mar 2017
Posts: 21
Trapshooter1956 is on a distinguished road
Default

Yes and no. If i remove the formulas from the cells it is drawing from it works fine. It's not a big deal I was just trying to clean the chart up a little.
Reply With Quote
  #14  
Old 01-08-2019, 11:16 PM
ArviLaanemets ArviLaanemets is offline Windows 8 Office 2016
Expert
 
Join Date: May 2017
Posts: 409
ArviLaanemets will become famous soon enough
Default

In my example, I replaced "" with NA() in your formula on 2nd sheet - as result the line graph was shortened to 2 measurements instead of displaying 0-line to end. Was this what you were looking for or not?
Reply With Quote
  #15  
Old 01-09-2019, 07:38 AM
Trapshooter1956 Trapshooter1956 is offline Windows 7 32bit Office 2007
Novice
 
Join Date: Mar 2017
Posts: 21
Trapshooter1956 is on a distinguished road
Default

Guess some time I need to be hit with a brick to see things. Yes that does what I want, But is there a way to not have the #N/A show up in the cell. I'm kind of anal that way.

Thanks a lot for your help!!!!
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
PPT 2010 - Animated Chart by Series - Animations reset when editing chart agaudet21 PowerPoint 0 10-12-2017 06:18 AM
MS Project 2013 Reports: ACWP in Timephased Line Chart does not match ACWP in Column Chart or Table billcary Project 3 09-22-2014 07:10 AM
Question - Chart dapaq2 Word 0 01-03-2013 01:30 AM
Newbie Question - Chart Data KarynHill PowerPoint 6 01-08-2012 07:22 AM
Outlook chart question projecttoday Outlook 0 11-11-2011 11:29 AM


All times are GMT -7. The time now is 07:24 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft