Remember Me?

 Barney W external usenet poster First recorded activity by ExcelBanter: Nov 2007 Posts: 2 Value searching in columns - vlookup can not do it?

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?

 pinmaster external usenet poster First recorded activity by ExcelBanter: Aug 2006 Posts: 347 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:

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?

 T. Valko external usenet poster First recorded activity by ExcelBanter: Nov 2006 Posts: 15,768 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.
 Pete_UK external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 8,856 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:
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?

 Barney W external usenet poster First recorded activity by ExcelBanter: Nov 2007 Posts: 2 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:

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?

 Thread Tools Search this Thread Show Printable Version Search this Thread: Advanced Search Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On Similar Threads Thread Thread Starter Forum Replies Last Post Colin Excel Worksheet Functions 3 July 3rd 07 04:38 PM Cliff Excel Worksheet Functions 3 April 3rd 06 12:52 AM mully Excel Discussion (Misc queries) 2 February 3rd 06 01:50 PM lsu-i-like Excel Discussion (Misc queries) 5 June 20th 05 07:08 PM Aviator Excel Discussion (Misc queries) 3 January 27th 05 12:13 AM

All times are GMT +1. The time now is 03:41 PM. Copyright ©2004-2020 ExcelBanter.