Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old November 18th 07, 06:01 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2007
Posts: 2
Default 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?

  #2   Report Post  
Old November 18th 07, 06:39 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2006
Posts: 347
Default 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?

  #3   Report Post  
Old November 18th 07, 06:40 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 15,768
Default 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.
  #4   Report Post  
Old November 18th 07, 06:44 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 8,856
Default 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?


  #5   Report Post  
Old November 18th 07, 07:32 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2007
Posts: 2
Default 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?



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Searching two columns for information Colin Excel Worksheet Functions 3 July 3rd 07 04:38 PM
Searching Columns and Rows Cliff Excel Worksheet Functions 3 April 3rd 06 12:52 AM
Searching Columns mully Excel Discussion (Misc queries) 2 February 3rd 06 12:50 PM
searching columns and rows lsu-i-like Excel Discussion (Misc queries) 5 June 20th 05 07:08 PM
Searching Columns Aviator Excel Discussion (Misc queries) 3 January 26th 05 11:13 PM


All times are GMT +1. The time now is 08:29 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017