Looking for Formula to interpolate values between data in variable rows.

A

Alastair

New member
Joined
Jan 24, 2017
Messages
5
Reaction score
0
Points
0
Location
Sydney
Excel Version(s)
Office 365 Mac
Hi there.

I'm hoping that someone can assist with a formula for the following scenario:

Context:
I use an Excel workbook to observe and assess the facilitation skills of workplace trainers.
One of the essential elements I need to observe is the pace of their delivery (ideal, too fast, too slow, etc ...).
Currently I use values ranging from 2 (very fast) down to -2 (very slow) with 0 being the ideal pace.
Whenever I enter a pace rating, a time-stamp is added to an adjacent cell so I have a reference for the observation.

I've attached a workbook with the following values:


  • Column A: Time-stamp of manual pace rating entry
  • Column B: Manual pace rating entry
  • Each row (whether blank or populated) represents a minute during the observation duration - eg: a 30 minute observation would contain data within 30 rows.

Request:
I'm hoping for a formula in Column C that will:


  1. Copy an adjacent pace value from Column B, if one exists
  2. Otherwise, calculate an interpolated pace value between the previous and next pace values in Column B

Goal:
Ideally, Column C will contain source data for a graph that will show gradual changes in pace, mapped over the duration of the observed session.

What I've tried already:
  • The Forecast function
  • The Forecast-linear function

In both cases I could only get these to work between data points in fixed cell references. Unfortunately this won't work in my situation as the pace data in Column B could be entered at any point in time.

Thanks in advance:
I accept that what I'm requesting may not be possible, or at the very least will likely require considerable thought and time. I'm genuinely grateful for any consideration, let alone resolution, of this request.

With thanks,

Al.
 

Attachments

  • Book1.xlsx
    8.9 KB · Views: 6
Not a formula, as you've discovered, it's convoluted to apply. Instead, try this on the active sheet:
Code: 
Sub blah()
For Each are In Columns("B").SpecialCells(xlCellTypeBlanks).Areas
  Set Rng = are.Offset(-1, 1).Resize(are.Rows.Count + 2)
  Rng.Offset(, -1).Copy Rng
  step = (Rng.Cells(Rng.Rows.Count).Value - Rng.Cells(1).Value) / (Rng.Rows.Count - 1)
  Rng.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, step:=step, Trend:=False
Next are
End Sub
See attached.
 

Attachments

  • ExcelGuru11084Book1.xlsm
    19.2 KB · Views: 6
Last edited:
Thank you p45cal.

Your solution is exactly the result I was hoping for, and far more compact too.

With much appreciation,

Al.
 
You must log in or register to reply here.
Back
Top