Forecasting data?

**Jonsmith2212** · 01-11-2017, 10:54 PM

Hello
I have an interesting problem I am trying to solve. I have two datasets that show distance of a sensor from the bottom of a pipe. As the water rises the distance (in inches) gets smaller.
For example the sensor starts at 95 inches, and as water rises the next data point (anywhere from 1 minute to 12 minutes after) will show 92 inches and so on and so on.

Eventually, if the water rises high enough, the sensor "flatlines" and no longer provides an accurate reading. An example of that would be a constant reporting of 16 inches.

So here is what I have been racking my brain about:

At a certain time one of the sensors "flatlined", we will say at 2:00 PM, at a reading of 22 inches. We know for a fact that the water climbed past that twenty two inches to level or 0 inches.
The second dataset continues, we will say until 6:00 PM, before "flatlining" at a reading of 16 inches.

To solve this I have taken the average rise from 2:00 PM until 6:00 PM on this data set, which was 2.86 inches every 15 minutes, and applied it to the other data set to estimate when it reach 0" inches.

I am not entirely sure that this is the best way to forecast the rise for the other sensor. Is there a better way to do this?

**Richard Buttrey** · 01-12-2017, 06:56 AM

Hi,

You are making two basic assumptions
1. Both the sensors are the same type, and if age is a factor the same age.
2. The change in distance is linear with respect to time.

Are both those assumptions true?

If the movement IS linear why do you need to use one sensor to forecast the movement in another. Why not just sample sensor two movements and project forward.

**Jonsmith2212** · 01-12-2017, 09:52 PM

Hi-

The change in distance is not linear. Without boring you to death, these senors are placed on manhole covers which measure the distance of the wastewater to the sensor. Essentially, when one "flatlines" it means it has been submerged.

I have attached sample dataset to look at. I ran the "trend" function on it but I do not think this is the correct statistical formula to use to forecast the missing data.

**MrShorty** · 01-13-2017, 12:34 PM

I am not sure exactly what you are needing to do here, and, from my own experience calibrating and reading sensors, it can be important to carefully define exactly what you want to do in order to devise a good strategy. A lot of that, of course, occurs outside of Excel. Once you understand the strategy you want to implement, then you can figure out how to program that into Excel.

My first observation -- when they are both reading, both sensors seem to give the exact same reading. Would it be acceptable to assume, when one sensor flatlines, that the reading would continue to be the same as the 2nd sensor if it had not flatlined? If not, why not?

**Jonsmith2212** · 01-13-2017, 12:40 PM

Hi there, thank you for the response!

I should have been more clear when I posted the dataset. The two columns on the left are real data and what was returned from one sensor. The columns on the right is the same data from the same sensor only with the trend function used to predict the zero values.

Using the data provided I am trying to figure out when the wastewater would have actually reached 0. We know it has 22.3 inches to go at 3:00, I was just wondering if there was a way, statistically, in excel to forecast that using this dataset

**MrShorty** · 01-13-2017, 01:05 PM

Excel can readily be programmed to do linear and non-linear regressions (the TREND() function is only one of Excel's regression functions), and then solve the resulting equation. Overall procedure I would expect to use:

1) Choose a regression equation. Note that this is not something Excel can readily do -- this is up to the analyst/programmer to choose. This is sometimes the hardest part. A lot of times we default to polynomials when we don't have any better ideas.
2) With the chosen regression equation, derive parameters from linear or non-linear regression methods. In Excel, linear regressions are usually performed using the LINEST() function. Non-linear regressions can be set up and solved using Solver. Note that polynomials (if you go that route) are "linear" functions and can be regressed using the LINEST() function.
3) With the parameters from the regression, solve the equation for the desired value.
4) Evaluate result.

**Jonsmith2212** · 01-13-2017, 01:19 PM

I really appreciate your response. Defaulting to polynomials is where I am at now. I charted the data on a scatter plot and added a trend line. The polynomial trendline (to the order of 6) fit the best; However, would not be able to explain myself if someone asked.

I've got my work cut out for me! Thank you!

Forecasting data?

LinkBack

Thread Tools

Rate This Thread

Display

Forecasting data?

Re: Forecasting data?

Re: Forecasting data?

Re: Forecasting data?

Re: Forecasting data?

Re: Forecasting data?

Re: Forecasting data?

Thread Information

Users Browsing this Thread

Similar Threads

Forecasting data

forecasting seasonal data with excel

Forecasting from historical data with Solver or other technique

Excel 2007 : Forecasting using non linear data

Forecasting and Data Analysis using three variables

forecasting and linking to a data range that is fluctuating

forecasting and linking to a data range that is fluctuating

Bookmarks

Bookmarks

Posting Permissions