#1   Report Post  
carl
 
Posts: n/a
Default Compare Times

I have 2 sets of data like so:

Set1

Time Number
121033 5
122345 6
122744 7

Set2

Time Price
121028 1
121533 2
122240 3
122530 4
122645 5

I am trying to build a formula that will be in column 3 of data set1 that
will look at data set2 and pick the price that has time lessthan or equal to
the time on dataset1.

The result would like like so:

set1

Time Number Price
121033 5 1
122345 6 3
122744 7 5

Thank you in advance.

  #2   Report Post  
Frank Kabel
 
Posts: n/a
Default

Hi
try the following array formula (entered with CTRL+SHIFT+ENTER):
=INDEX('sheet2'!$B$1:$B$20,MATCH(MAX(IF('sheet2'!$ A$1:$A$20<=A1,'sheet2
'!$A$1:$A$20)),$A$1:$A$20,0))
and copy down

--
Regards
Frank Kabel
Frankfurt, Germany

"carl" schrieb im Newsbeitrag
...
I have 2 sets of data like so:

Set1

Time Number
121033 5
122345 6
122744 7

Set2

Time Price
121028 1
121533 2
122240 3
122530 4
122645 5

I am trying to build a formula that will be in column 3 of data set1

that
will look at data set2 and pick the price that has time lessthan or

equal to
the time on dataset1.

The result would like like so:

set1

Time Number Price
121033 5 1
122345 6 3
122744 7 5

Thank you in advance.


  #3   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

On Fri, 3 Dec 2004 07:33:02 -0800, "carl"
wrote:

I have 2 sets of data like so:

Set1

Time Number
121033 5
122345 6
122744 7

Set2

Time Price
121028 1
121533 2
122240 3
122530 4
122645 5

I am trying to build a formula that will be in column 3 of data set1 that
will look at data set2 and pick the price that has time lessthan or equal to
the time on dataset1.

The result would like like so:

set1

Time Number Price
121033 5 1
122345 6 3
122744 7 5

Thank you in advance.


If the times in Set 2 are sorted in ascending order, as in your example, then:

=VLOOKUP(A2,Set2,2)

Where A2 contains a Time from Set1; Set2 is a defined name encompassing that
table, (and 2 is the second column in Data Set2 which is where the Price is
stored).


--ron
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
Is there a way to compare 2 spreadsheets with Excel? Dave Peterson Excel Discussion (Misc queries) 3 March 29th 05 12:36 AM
Adding/Averaging Times in Excel JD Excel Discussion (Misc queries) 2 January 5th 05 06:07 PM
Whats the function to count the total times a word is displayed Monk Excel Discussion (Misc queries) 3 December 10th 04 11:39 PM
Compare Columns Michael Nesi Excel Discussion (Misc queries) 1 December 6th 04 09:45 PM
How can use IF function more than 7 times in a cell wuwu Excel Worksheet Functions 4 November 13th 04 11:23 AM


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

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

About Us

"It's about Microsoft Excel"