Value searching in columns - vlookup can not do it?
 

This may not be an easy one to follow, so if you need more information please
let me know!

I have two columns of original data which are linked. Column A is time and B
is acceleration at each time in A. From these two columns of data I need to
calculate new interpolated acceleration data at time points which lie between
the original time points. I need to do this for all (approx 28 data points).
I have written the formulae to calculate the new acceleration data based upon
the new time points, but at the moment I have to identify the original time
points either side of the new time points manually. The two original time
points either side of the new time points are used to calculate the
acceleration. Unfortunately the new time periods are often less than the
original time periods, so I can often have two new interpolated data points
between two original data points. If it was only 1 it would be easy. So I
need something that will identify and return the time value that is
immediately less than the new one, and the value that is immediately greater
than the new value.

I know that this can be done in VBA, but I can not write code at the moment.

Any thoughts?

Value searching in columns - vlookup can not do it?
 

Hi,

You might want to try the MAX and MIN functionS:

MAX(IF(range<new value,range))
MIN(IF(rangenew value,rang))
these are array formula so any formula that you will be using these with
will have to be entered using Ctrl+Shift+Enter

Hope this helps!
Jean-Guy

"Barney W" wrote:

This may not be an easy one to follow, so if you need more information please
let me know!

I have two columns of original data which are linked. Column A is time and B
is acceleration at each time in A. From these two columns of data I need to
calculate new interpolated acceleration data at time points which lie between
the original time points. I need to do this for all (approx 28 data points).
I have written the formulae to calculate the new acceleration data based upon
the new time points, but at the moment I have to identify the original time
points either side of the new time points manually. The two original time
points either side of the new time points are used to calculate the
acceleration. Unfortunately the new time periods are often less than the
original time periods, so I can often have two new interpolated data points
between two original data points. If it was only 1 it would be easy. So I
need something that will identify and return the time value that is
immediately less than the new one, and the value that is immediately greater
than the new value.

I know that this can be done in VBA, but I can not write code at the moment.

Any thoughts?

Value searching in columns - vlookup can not do it?
 

Try these array formulas**:

A1:A10 = range of values
C1 = new value

For the next lower:

=MAX(IF(A1:A10<C1,A1:A10))

For the next higher:

=MIN(IF(A1:A10C1,A1:A10))

Note that if there isn't a next lower or next higher you'll get a result of
0.

Value searching in columns - vlookup can not do it?
 

Can't you sort the data by time points into ascending order, then your
first interpolated point will be between A1 and A2? Perhaps:

=(A1+A2)/2

copied down to row 27 if you have 28 points.

Hope this helps.

Pete

On Nov 18, 6:01 pm, Barney W
wrote:
This may not be an easy one to follow, so if you need more information please
let me know!

I have two columns of original data which are linked. Column A is time and B
is acceleration at each time in A. From these two columns of data I need to
calculate new interpolated acceleration data at time points which lie between
the original time points. I need to do this for all (approx 28 data points).
I have written the formulae to calculate the new acceleration data based upon
the new time points, but at the moment I have to identify the original time
points either side of the new time points manually. The two original time
points either side of the new time points are used to calculate the
acceleration. Unfortunately the new time periods are often less than the
original time periods, so I can often have two new interpolated data points
between two original data points. If it was only 1 it would be easy. So I
need something that will identify and return the time value that is
immediately less than the new one, and the value that is immediately greater
than the new value.

I know that this can be done in VBA, but I can not write code at the moment.

Any thoughts?

Value searching in columns - vlookup can not do it?
 

Hi Many thanks to you all.

Your wisdom has put an end to my many hours of searching and trail and
error! It worked a treat. I added a = into the formula to account for time
values that were the same as the originals, and that seems to work as well.

Once again many thanks.

Barney

"pinmaster" wrote:

Hi,

You might want to try the MAX and MIN functionS:

MAX(IF(range<new value,range))
MIN(IF(rangenew value,rang))
these are array formula so any formula that you will be using these with
will have to be entered using Ctrl+Shift+Enter

Hope this helps!
Jean-Guy

"Barney W" wrote:

This may not be an easy one to follow, so if you need more information please
let me know!

I have two columns of original data which are linked. Column A is time and B
is acceleration at each time in A. From these two columns of data I need to
calculate new interpolated acceleration data at time points which lie between
the original time points. I need to do this for all (approx 28 data points).
I have written the formulae to calculate the new acceleration data based upon
the new time points, but at the moment I have to identify the original time
points either side of the new time points manually. The two original time
points either side of the new time points are used to calculate the
acceleration. Unfortunately the new time periods are often less than the
original time periods, so I can often have two new interpolated data points
between two original data points. If it was only 1 it would be easy. So I
need something that will identify and return the time value that is
immediately less than the new one, and the value that is immediately greater
than the new value.

I know that this can be done in VBA, but I can not write code at the moment.

Any thoughts?