Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 191
Default Compare a value to a list of values

I have a table that looks like this

First date Last Date QValue H Value
2006-01-01 2006-04-26 10 3
2006-04-26 11 4
2006-04-27 12 5
2006-04-28 13 6

The problem is people enter data into the table 2 different ways, some
people use first date and last date and the values as in row 1 above. Other
people will date fill down so each day appears in the first date column with
the values as in lines 2,3,and 4. Some people could do it both ways like the
table above, since getting people to do it only one way isn't an option, I am
trying to create a formula that can search the table regardless of which
method people use to input data. I need a formula that can take 2006-01-02
and look for it in the first date column and recognize that 2006-01-02 falls
between 2006-01-01 and 2006-04-26, and return the corresponding value.

Thanks
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 459
Default Compare a value to a list of values

Jamie wrote:
I have a table that looks like this

First date Last Date QValue H Value
2006-01-01 2006-04-26 10 3
2006-04-26 11 4
2006-04-27 12 5
2006-04-28 13 6

The problem is people enter data into the table 2 different ways, some
people use first date and last date and the values as in row 1 above.
Other people will date fill down so each day appears in the first
date column with the values as in lines 2,3,and 4. Some people could
do it both ways like the table above, since getting people to do it
only one way isn't an option, I am trying to create a formula that
can search the table regardless of which method people use to input
data. I need a formula that can take 2006-01-02 and look for it in
the first date column and recognize that 2006-01-02 falls between
2006-01-01 and 2006-04-26, and return the corresponding value.

Thanks



Hi Jamie,

If I well understand (but I'm not sure...), you can use the SUMPRODUCT
function:

=SUMPRODUCT((First_dat_range=your_date)*(First_da t_range<=your_date);(your_value_range))



--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy


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
compare values in auto filter uw805 Excel Worksheet Functions 4 July 6th 06 08:44 AM
How do I generate a list from a range of values robo7084 Excel Worksheet Functions 2 July 6th 06 01:48 AM
if specific value from list A equals one of the values from list b... broer konijn Excel Worksheet Functions 7 June 14th 06 06:28 AM
Extracting Values on one list and not another B Schwarz Excel Discussion (Misc queries) 4 January 7th 05 02:48 PM
Selecting data from a list based on entered values GrantM Excel Discussion (Misc queries) 1 December 20th 04 11:59 AM


All times are GMT +1. The time now is 11:43 AM.

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"