| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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.worksheet.functions
|
|||
|
|||
|
Lookup closest to
say yr data is in A1:C7
product (i.e. Anemi Test) being in E1 1 Dec 11 in F1 would this: =SUM(IF(E1=$A$1:$A$7,IF(F1-$B$1:$B$7=MIN(IF(F1$B$1:$B$7,F1-$B$1:$B $7,"")),$C$1:$C$7,),)) be helpful? CTRL+SHIFT+ENTER this formula as this is an array-formula On 16 Mar, 14:50, 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup closest to
or simpler:
=SUM(IF((E1=$A$1:$A$7)*(F1-$B$1:$B$7=MIN(IF(F1$B$1:$B$7,F1-$B$1:$B $7,""))),$C$1:$C$7,),) also CTRL+SHIFT+ENTER this On 16 Mar, 15:30, Jarek Kujawa wrote: say yr data is in A1:C7 product (i.e. Anemi Test) being in E1 1 Dec 11 in F1 would this: =SUM(IF(E1=$A$1:$A$7,IF(F1-$B$1:$B$7=MIN(IF(F1$B$1:$B$7,F1-$B$1:$B $7,"")),$C$1:$C$7,),)) be helpful? CTRL+SHIFT+ENTER this formula as this is an array-formula On 16 Mar, 14:50, 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- Ukryj cytowany tekst - - Pokaż cytowany tekst - |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup closest to
Hi,
You may use this array formula (Ctrl+Shift+Enter). D26 will hold Anemi Test and E26 will hold 1 Jan 12 =SUMPRODUCT(--(E18:E24=MAX(IF(($D$18:$D$24=D26)*($E$18:$E$24<=E2 6),E18:E24))*(D18:D24=D26)),F18:F24) I have feeling that I have complicated the solution. I am sure there is an easier non-array formula solution available as well. Nevertheless this solution will work for you. By the way the first answer should be 575 and not 375. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "sgl" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup closest to
Ashish thank you very much that worked very well
sgl "Ashish Mathur" wrote: Hi, You may use this array formula (Ctrl+Shift+Enter). D26 will hold Anemi Test and E26 will hold 1 Jan 12 =SUMPRODUCT(--(E18:E24=MAX(IF(($D$18:$D$24=D26)*($E$18:$E$24<=E2 6),E18:E24))*(D18:D24=D26)),F18:F24) I have feeling that I have complicated the solution. I am sure there is an easier non-array formula solution available as well. Nevertheless this solution will work for you. By the way the first answer should be 575 and not 375. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "sgl" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup closest to
You are welcome. Thank you for the feedback
-- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "sgl" wrote in message ... Ashish thank you very much that worked very well sgl "Ashish Mathur" wrote: Hi, You may use this array formula (Ctrl+Shift+Enter). D26 will hold Anemi Test and E26 will hold 1 Jan 12 =SUMPRODUCT(--(E18:E24=MAX(IF(($D$18:$D$24=D26)*($E$18:$E$24<=E2 6),E18:E24))*(D18:D24=D26)),F18:F24) I have feeling that I have complicated the solution. I am sure there is an easier non-array formula solution available as well. Nevertheless this solution will work for you. By the way the first answer should be 575 and not 375. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "sgl" wrote in message ... 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 | |||
| 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 | |||
| Lookup closest number in list | Excel Discussion (Misc queries) | |||
Linear Mode
