2
$\begingroup$

i did an experiment and recorded a value over time. the value always starts at zero and slowly rises over time. once the value reaches a certain threshold the experiment is stopped. i repeated this experiment several times and now have a bunch of measurement series (values over time).

if plotted (time on x axis and measured value on y axis) the curves all look quite similar. some curves raise a litte faster. some a little slower.

i want to "average" this curve. but not the average of the value at a certain time but instead average of time needed to reach a certain value. or in other words: not the average of the y axis values but the average of the x axis values. i hope that makes sense.

here a contrived example to show some actual numbers. the first column is time. the following columns are the measured values of the experiments. in this example the threshold value to stop the experiment is 10.

1  0  0  0
2  1  2  3
3  3  4  6
4  4  6  9
5  6  8 12
6  7 10
7  9
8 10

the typical average function of spreadsheet program will do, for example for time point 2: avg(1,2,3). but what i want is the average of the time required to reach, for example the value 6: avg(5,4,3). and that for all (interpolated) values between 0 and 10.

i imagine that this is a common statistics operation. i was expecting spreadsheets programs like excel or libreoffice calc to have a prepared function for this task. i looked over some of the functions related to "average" but none seem to do what i want. i have tried searching for some other functions but they all seem to do something different. here are some of the terms i searched for: average, pivot, normalize, inverse.

if answering please have mercy with my lack of skill in statistics. please don't just name drop techniques. please explain in layman terms. also maybe my understanding of an "average curve" is nonsensical. then please explain how i can "average" my many similar curves so that it makes sense.

$\endgroup$
3
  • $\begingroup$ This is an uncommon operation, so could you explain what property this average curve is intended to represent? That is, how will you interpret it? $\endgroup$
    – whuber
    Jun 24, 2021 at 15:55
  • 1
    $\begingroup$ if a customer asks "how long till it reaches value y?" i can say "on average x time units". or the other way around: if after x time units the value is still far off from y then probably something went wrong and we can start to investigate early. $\endgroup$
    – Lesmana
    Jun 24, 2021 at 16:18
  • $\begingroup$ I see: this looks like a question about the distribution of hitting times of a process. $\endgroup$
    – whuber
    Jun 24, 2021 at 17:38

1 Answer 1

1
$\begingroup$

i made a solution. do not know if scientifically correct. good enough for my use case.

problem description: naive average of values at time x does not work because not all time x has all values.

1  0  0  0  avg(0,0,0)
2  1  2  3  avg(1,2,3)
3  3  4  6  ...
4  4  6  9  ...
5  6  8 12  ...
6  7 10     avg(7,10,?) <- does not work
7  9        avg(9,?,?)
8 10        ...

first column is time. following columns are the measured values. last column is average of values. since experiment is stopped if value reaches 10 there are no values for the remaining times.

my solution: do average of time required to reach value y.

first create a column of theoretical measurement values. in my case those are values between 0 and 10. depends on your value you will want more or less steps between min and max values. for this example i will use step of 1.

then in the following columns, for each experiment, calculate the time needed to reach value y in the first column.

then we can calculate the average of time needed to reach value y. this is now unproblematic because the time columns will always be complete.

this will look something like this:

 0  1  1  1  avg(1,1,1)
 1  2  1  1  avg(2,1,1)
 2  2  2  1  ...
 3  3  2  2  ...
 4  4  3  2  ...
 5  4  3  2  ...
 6  5  4  3  avg(5,4,3)
     ...
10  8  6  5  avg(8,6,5)

here is how to calculate the time needed to reach value y in libreoffice calc

=MATCH($A1; data.B$1:B$last)

in column A we have the theoretical measured values. in sheet data we have original measurement data. in sheet data column B we have measurement of first experiment. in column C and following we have measurement of following experiments. last is the last row with values. it can be any large number larger than any column.

here is documentation https://wiki.documentfoundation.org/Documentation/Calc_Functions/MATCH

note this only works if your values are increasing monotonously (there are no smaller values after a larger value). it should also work if it is decreasing monotonously.

the first few rows might give error when the first real measurement values are smaller than the first theoretical measurement values. then you can wrap MATCH in IFERROR with a suitable default value like this

=IFERROR(MATCH(...); 1)

to plot use scatter plot with lines connecting the dots.

the advantage of scatter plot is that you can separately select the column for the x axis and the column for y axis for each data set.

for the actual measurement you will want the time column on x axis and the measurement values column on the y axis. this is the default chart setting.

for the average you want the calculated average time column on the x axis and the theoretical measurement values column on the y axis. this you have to add manually.

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.