Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup closest to
I have the following table
Name Date Amount Anemi Test 31-Dec-11 500 Apnea 1 31-Dec-11 300 NB 1 Panamax 31-Dec-13 275 NB 2 Supramax 15-Nov-11 300 Anemi Test 19 Nov 11 123 Anemi Test 30-Nov-11 200 Anemi Test 31-Dec-11 75 I also have a Starting Date which say in this first instance is 1 Jan 12. I want to find and SUM the value for each item nearest to the starting date. In the above the result for Anemi Test would be 375, the SUM for NB 2 Supramax would be 300 and so on. If I were to make the starting date 1 Dec 11 then the result for Anemi Test would be 200 and for NB 2 Supramax would again be 300. Thanks in advance/sgl |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup closest to
I think you need to define what defines a "closest match" value. When you write "SUM the value for each item nearest to the starting date", how many items might that be, and how "nearest" do you need to go. You might be able to adapt the formulas at http://www.cpearson.com/Excel/TablesAndLookups.aspx in the "Closest Match" section near the bottom of the page. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Mon, 16 Mar 2009 06:05:04 -0700, sgl wrote: I have the following table Name Date Amount Anemi Test 31-Dec-11 500 Apnea 1 31-Dec-11 300 NB 1 Panamax 31-Dec-13 275 NB 2 Supramax 15-Nov-11 300 Anemi Test 19 Nov 11 123 Anemi Test 30-Nov-11 200 Anemi Test 31-Dec-11 75 I also have a Starting Date which say in this first instance is 1 Jan 12. I want to find and SUM the value for each item nearest to the starting date. In the above the result for Anemi Test would be 375, the SUM for NB 2 Supramax would be 300 and so on. If I were to make the starting date 1 Dec 11 then the result for Anemi Test would be 200 and for NB 2 Supramax would again be 300. Thanks in advance/sgl |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup closest to | Excel Worksheet Functions | |||
Array Lookup to Find Closest Date and Next Closest Date | Excel Worksheet Functions | |||
Closest value lookup? | Excel Worksheet Functions | |||
how do i look up the closest number valuein a lookup? | Excel Discussion (Misc queries) | |||
Lookup Closest Value | Excel Worksheet Functions |