Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-03-2019, 10:18 AM
Trapshooter1956 Trapshooter1956 is offline Chart question Windows 7 32bit Chart question Office 2007
Advanced Beginner
Chart question
 
Join Date: Mar 2017
Posts: 31
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 Chart question Windows 7 64bit Chart question Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
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
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #3  
Old 01-04-2019, 12:25 AM
ArviLaanemets ArviLaanemets is offline Chart question Windows 8 Chart question Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
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, 13 views)
Reply With Quote
  #4  
Old 01-04-2019, 07:21 AM
Trapshooter1956 Trapshooter1956 is offline Chart question Windows 7 32bit Chart question Office 2007
Advanced Beginner
Chart question
 
Join Date: Mar 2017
Posts: 31
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 Chart question Windows 8 Chart question Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
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 Chart question Windows 7 32bit Chart question Office 2007
Advanced Beginner
Chart question
 
Join Date: Mar 2017
Posts: 31
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 Chart question Windows 7 32bit Chart question Office 2007
Advanced Beginner
Chart question
 
Join Date: Mar 2017
Posts: 31
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, 10 views)
Reply With Quote
  #8  
Old 01-05-2019, 01:55 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Chart question Windows 7 64bit Chart question Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
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
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #9  
Old 01-07-2019, 07:47 AM
Trapshooter1956 Trapshooter1956 is offline Chart question Windows 7 32bit Chart question Office 2007
Advanced Beginner
Chart question
 
Join Date: Mar 2017
Posts: 31
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 Chart question Windows 8 Chart question Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

Do you read answers sometimes?
Attached Files
File Type: xlsx 2019 Process Control Graph issues.xlsx (301.0 KB, 8 views)
Reply With Quote
  #11  
Old 01-08-2019, 07:59 AM
Trapshooter1956 Trapshooter1956 is offline Chart question Windows 7 32bit Chart question Office 2007
Advanced Beginner
Chart question
 
Join Date: Mar 2017
Posts: 31
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 Chart question Windows 8 Chart question Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
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 Chart question Windows 7 32bit Chart question Office 2007
Advanced Beginner
Chart question
 
Join Date: Mar 2017
Posts: 31
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 Chart question Windows 8 Chart question Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
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 Chart question Windows 7 32bit Chart question Office 2007
Advanced Beginner
Chart question
 
Join Date: Mar 2017
Posts: 31
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
Chart question 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
Chart question 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

Other Forums: Access Forums

All times are GMT -7. The time now is 06:33 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft