Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Searching two columns for information | Excel Worksheet Functions | |||
Searching Columns and Rows | Excel Worksheet Functions | |||
Searching Columns | Excel Discussion (Misc queries) | |||
searching columns and rows | Excel Discussion (Misc queries) | |||
Searching Columns | Excel Discussion (Misc queries) |