Formula to calculate Initial Time and End Time after a variation

[ExcelLearner89]

Ok, I'm going to try and explain myself the best i can.

I have a table with some 6 columns and 37842 lines with values of tests done to car tyres.
I've done a graphic with 'Fz' value column over time (ET) which translates to a graphic like this.
The orange represents the FZ values.

I've been searching Excel formulas for some hours now but i can't find a way to do this.
What I want is when FZ suffers a sudden variation(like you can see in the graphic), for excel to tell me the initial time (A point) and the ending time (B point) and to calculate the AVERAGE value of FZ between those points. So I end up with a smaller table like the example bellow:

Average FZ------ET initial------ET final
- 850---------------0-------------148
- 575--------------148-----------242
etc etc etc

(Well apparently I can't post images, so you can't see the graphic)
I'll try to illustrate it with symbols(ignore the '-'). Fz values over time.


-----------------------------------------|xxxxxxxxx|
--------------|xxxxxxxxx|------------|-------------|
--------------|------------|xxxxxxxxx|-------------|xxxxxxx.......
(A)xxxxxxxx|(B)


Thanks a lot in advance. I use plenty of this tables and i usually do this table by hand, but with 37842 values it's not an easy task.

[Olly]

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[ExcelLearner89]

Ok thank you. I followed your instructions, but the file was too big to upload, so I removed all the columns that don't matter to me from sheet1 and everything below the line 10000, those columns extend to the line 37842, but this is enough for me to explain what i Need.

Once again, thanks a lot. To have this process automated by excel would help me a LOT in the future.


EDIT:
Ok, with this formula I was able to detect variations in the FZ column and comparing to the graphic in Sheet3 it's accurate.

=IF(OR(AND($E3>($E2+100);$E4>($E3-100));AND($E3<($E2+100);$E4<($E3-100)));"VARIATION";"OK")

Now I have a new column with 37817 "OK's" and 23 "VARIATIONs" LOL
At least it is able to detect variations more than 100 above or 100 below. I'm still trying to find a way for it to tell me the ET value everytime a "VARIATION" appears and for it to calculate the AVERAGE FZ of all values before said variation...

[MrShorty]

I am not an expert in this kind of signal processing. I will say that I am not surprised that your searches for a single formula did not find anything, because I don't expect that there is a single, built in formula for this kind of signal processing. Some with the ingenuity and patience may be able to come up with a long complex single cell mega array formula that will do it, but I am a firm believer in using multiple cells with relatively simple formulas to perform complex tasks like this.

On the few occasions where I do tackle signal processing problems, I find that my first step is almost always to add some kind of "change" or "slope" calculation to the data. Your description seems more interested in absolute change (specifically detecting an absolute change greater than 100), so my first step would be to add a column to calculate absolute change. I chose column M and entered =E3-E2 into M3. Copy/fill this down to the bottom of the data.

The next thing I usually want is some kind of increasing number/integer that will uniquely identify each region of interest. I choose column N for this column and enter 0 in N2 and =IF(ABS(M3)>100,N2+1,N2) into N3. Copy/paste/fill N3 to the bottom of the table. Now I have a column of increasing integers from 0 to 6 that identify each segment.

With those two columns added, retrieving the stopwatch time for each segment is a simple lookup function (MATCH() will return the first row number for each integer, and INDEX() can be used to retrieve the time stamp from column B), and the average for each segment is a simple AVERAGEIFS() function =AVERAGEIFS(E:E,N:N,0), for example, will return the average of the starting baseline segment. At this point, I will assume that you are familiar with INDEX() and MATCH() and AVERAGEIFS() and can put those formulas together.

Will something like that work for you?

[ExcelLearner89]

Yes of course. It doesn't have to be a massive formula in a Cell...it can use multiple cells and it has to use multiple cells, each column of cells obtaining a certain value.

What I'm trying right now, after the formula I posted before, is to get the ET value that corresponds to the same line as a "VARIATION"

Thanks for helping

[MrShorty]

What I'm trying right now, after the formula I posted before, is to get the ET value that corresponds to the same line as a "VARIATION"

As I indicated, after adding in column N, pulling the ET value for each variation is a simple lookup function. I had assumed that you were already familiar with Excel's lookup functions, but perhaps not. If I were doing this I would:

Enter 0, 1, 2, 3, 4, 5, 6 into R1:R6 (or some other convenient range where I wanted my summary table to be). The row number where each variation occurs would then be a simple MATCH() function =MATCH(R1,$N$2:$N$10000,0). The ET value can then be extracted with an INDEX() function =INDEX($B$2:$B$10000,MATCH(R1,$N$2:$N$10000,0)). If I enter that formula into S1 and copy down to S6, then I have the start times for each variation. End time for each variation is simply the start time for the next variation. If I want it on the same row, I can enter =S2 into T1 and copy down.

INDEX() help file: https://support.office.com/en-us/art...2-b56b061328bd
MATCH() help file: https://support.office.com/en-us/art...9-533f4a37673a

[ExcelLearner89]

Thanks, that actually helped a lot with the ETs part. Thank you.

Now I just need the AVERAGE Fz between each variation, but that I think I can figure out.

Again thank you for your help

[Olly]

You can handle this with Power Query.

Format your source data as a table, then use:

Please Login or Register  to view this content.

Loaded to a table, this returns:

Capture.PNG

See worked example in attached file.

[ExcelLearner89]

Sweet mother of jesus...what are you in the Matrix or something?

Thats like magic...programmed magic...thank you a LOT man.

The previous answers already helped me a LOT but this one not only gets the job done but also turns it into a presentable table...amazing

[AliGW]

PowerQuery = Pandora's Box > once opened, it cannot be closed again.

Being in the matrix isn't a bad way of describing it, actually!

[Olly]

Glad to help.

Power Query is pretty cool - well worth learning.