Excel average every 0.5 meters, irregular distances between data points

Question

I have a data set that has height values every so often, like topography data in a straight line with GPS coordinates. I used the GPS coordinates and trigonometry to make a cumulative distance column. However, the distance between points varies. Sometimes its 10 cm sometimes its 13, sometimes its 40.

I would like to take the average height every 0.5 meters, but sometimes the distance column doesnt even land on a multiple of 0.5! This would mean my output column would be significantly shorter than my raw data column.

I think my main problem is I do not know what this process is called in order to Google it. Another problem is that the distances are irregular as mentioned above. Things I think may have something to do with it:

averageif?

binning? I do not want a histrogram though, just the data.

Thanks for the help and if you do not know the answer but at least know what I should be writing in the search bars that would be helpful as well. Thanks!

Either the TREND (for linear and/or polynomial curves) or GROWTH (for exponential curves) should allow you to generate a new curve of height vs distance based on the desired distance datapoints. — Ron Rosenfeld
The goals is to have one column with 0.5, 1.0, 1.5 etc. and the depth values that fall in those bins averaged in the next column. — BobbyEK

Ron Rosenfeld Ron Rosenfeld · Accepted Answer · 2014-09-03T19:30:01

Perhaps this will work for you. I made up a series of distance vs height measurements and determined that a third order polynomial curve fit pretty well. (A different curve might best fit your real data, so you would have to alter the formula accordingly). I then used that formula to derive a set of new heights for the desired ditances at, in my example five unit differences. The formula under Extrapolated heights is an ARRAY formula entered into all the cells at once. You select D2:D12, enter the formula in D2 and, hold down CTRL-SHIFT while hitting ENTER. If you did this correctly, you will have the same formula in each cell surrounded by curly braces {...}

Then you can decide how you want to "Average" the heights.

enter image description here

Excel average every 0.5 meters, irregular distances between data points

1 Answers