|
INDEX MATCH SMALL
Can someone help me build a formula that will
Return this answers from the data below My lookup value will be my date i.e 10/24 My answer on this one would 1st lowest= 4:00 pm 20% 17... second lowest would be 3:00 pm 65% 16 .. I think a Index,Match and Samll will work I just cant put it together. *The 2 Lowest Service Levels on column E11 and E12 *What interval they came in at on Column D11 and D12 *Howmany Calls came in during those intervals on Column F11 and F12 C D E F Date Interval SL Calls 1 10/24/2012 3:00 PM 65% 16 2 10/24/2012 4:00 PM 20% 17 3 1/24/1900 5:00 PM 87% 18 4 10/25/2012 6:00 PM 60% 19 10/25/2012 7:00 PM 16% 20 Lookup Value 10/24/2012 11 What interval What SL Howmany Calls 12 What interval What SL Howmany Calls |
INDEX MATCH SMALL
|
INDEX MATCH SMALL
On Monday, October 29, 2012 12:33:02 PM UTC-5, wrote:
Can someone help me build a formula that will Return this answers from the data below My lookup value will be my date i.e 10/24 My answer on this one would 1st lowest= 4:00 pm 20% 17... second lowest would be 3:00 pm 65% 16 . I think a Index,Match and Samll will work I just cant put it together. *The 2 Lowest Service Levels on column E11 and E12 *What interval they came in at on Column D11 and D12 *Howmany Calls came in during those intervals on Column F11 and F12 C D E F Date Interval SL Calls 1 10/24/2012 3:00 PM 65% 16 2 10/24/2012 4:00 PM 20% 17 3 1/24/1900 5:00 PM 87% 18 4 10/25/2012 6:00 PM 60% 19 10/25/2012 7:00 PM 16% 20 Lookup Value 10/24/2012 11 What interval What SL Howmany Calls 12 What interval What SL Howmany Calls Clause I get and error, What is ;KKLEINSTE(WENN AND ZEILE IN THE Formula? |
INDEX MATCH SMALL
Hi,
Clause I get and error, What is ;KKLEINSTE(WENN AND ZEILE IN THE Formula? sorry that i sent you the formula in german language. Here is the correct formula: =INDEX(D$2:D$10,SMALL(IF($C$2:$C$10=$C$11,ROW($1:$ 9)),ROW(A1))) Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
INDEX MATCH SMALL
On Monday, October 29, 2012 12:33:02 PM UTC-5, wrote:
Can someone help me build a formula that will Return this answers from the data below My lookup value will be my date i.e 10/24 My answer on this one would 1st lowest= 4:00 pm 20% 17... second lowest would be 3:00 pm 65% 16 . I think a Index,Match and Samll will work I just cant put it together. *The 2 Lowest Service Levels on column E11 and E12 *What interval they came in at on Column D11 and D12 *Howmany Calls came in during those intervals on Column F11 and F12 C D E F Date Interval SL Calls 1 10/24/2012 3:00 PM 65% 16 2 10/24/2012 4:00 PM 20% 17 3 1/24/1900 5:00 PM 87% 18 4 10/25/2012 6:00 PM 60% 19 10/25/2012 7:00 PM 16% 20 Lookup Value 10/24/2012 11 What interval What SL Howmany Calls 12 What interval What SL Howmany Calls I get #NA Even after I hit cntrl shift enter, Does the ,Row(A1))) Supose to be on A1 Even tho there is no data there? |
INDEX MATCH SMALL
Hi,
Am Mon, 29 Oct 2012 19:54:02 +0100 schrieb Claus Busch: Row(A1) = 1 that returns you the smallest value. If you drag it down it will change to Row(A2) and gives you the second smallest. is in C11 your Lookup value? have a look for the workbook "jc": https://skydrive.live.com/#cid=9378A...121822A3%21191 Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
INDEX MATCH SMALL
On Monday, October 29, 2012 12:33:02 PM UTC-5, wrote:
Can someone help me build a formula that will Return this answers from the data below My lookup value will be my date i.e 10/24 My answer on this one would 1st lowest= 4:00 pm 20% 17... second lowest would be 3:00 pm 65% 16 . I think a Index,Match and Samll will work I just cant put it together. *The 2 Lowest Service Levels on column E11 and E12 *What interval they came in at on Column D11 and D12 *Howmany Calls came in during those intervals on Column F11 and F12 C D E F Date Interval SL Calls 1 10/24/2012 3:00 PM 65% 16 2 10/24/2012 4:00 PM 20% 17 3 1/24/1900 5:00 PM 87% 18 4 10/25/2012 6:00 PM 60% 19 10/25/2012 7:00 PM 16% 20 Lookup Value 10/24/2012 11 What interval What SL Howmany Calls 12 What interval What SL Howmany Calls I donthave access to open that from work but Thank you. I hope I can figure it out, that formula is pretty close tho. |
INDEX MATCH SMALL
On Monday, October 29, 2012 12:33:02 PM UTC-5, wrote:
Can someone help me build a formula that will Return this answers from the data below My lookup value will be my date i.e 10/24 My answer on this one would 1st lowest= 4:00 pm 20% 17... second lowest would be 3:00 pm 65% 16 . I think a Index,Match and Samll will work I just cant put it together. *The 2 Lowest Service Levels on column E11 and E12 *What interval they came in at on Column D11 and D12 *Howmany Calls came in during those intervals on Column F11 and F12 C D E F Date Interval SL Calls 1 10/24/2012 3:00 PM 65% 16 2 10/24/2012 4:00 PM 20% 17 3 1/24/1900 5:00 PM 87% 18 4 10/25/2012 6:00 PM 60% 19 10/25/2012 7:00 PM 16% 20 Lookup Value 10/24/2012 11 What interval What SL Howmany Calls 12 What interval What SL Howmany Calls I was able to view the file, and the formula is only retreiving the first 2 rows of data no matter what SL % or dates I put... |
INDEX MATCH SMALL
Hi,
Am Mon, 29 Oct 2012 14:27:15 -0700 (PDT) schrieb : I was able to view the file, and the formula is only retreiving the first 2 rows of data no matter what SL % or dates I put... if you sort your table by days and SL, the first 2 rows have the 2 smallest SL Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
INDEX MATCH SMALL
Hi,
Am Tue, 30 Oct 2012 08:43:16 +0100 schrieb Claus Busch: if you sort your table by days and SL, the first 2 rows have the 2 smallest SL pls look again for the workbook. I changed it for your expectation Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
INDEX MATCH SMALL
Hi again,
Am Tue, 30 Oct 2012 10:02:14 +0100 schrieb Claus Busch: pls look again for the workbook. I changed it for your expectation if you are at work and can't lool for the workbook: Your Lookup date in C11 in E11: =SMALL(OFFSET($C$1,MATCH($C$11,C2:C10,0),2,COUNTIF (C2:C10,$C$11)),ROW(A1)) in D11: =INDEX(D$2:D$10,MATCH(C11&E11,$C$2:$C$10&$E$2:$E$1 0,0)) in F11: =INDEX(F$2:F$10,MATCH(C11&E11,$C$2:$C$10&$E$2:$E$1 0,0)) The formulas in D11 and F11 are array formulas to enter with CTRL+Shift+Enter Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
INDEX MATCH SMALL
Hi,
Am Tue, 30 Oct 2012 10:12:33 +0100 schrieb Claus Busch: =SMALL(OFFSET($C$1,MATCH($C$11,C2:C10,0),2,COUNTIF (C2:C10,$C$11)),ROW(A1)) change the formula in E11 to: =SMALL(OFFSET($C$1,MATCH($C$11,$C$2:$C$10,0),2,COU NTIF($C$2:$C$10,$C$11)),ROW(A1)) Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
INDEX MATCH SMALL
On Monday, October 29, 2012 12:33:02 PM UTC-5, wrote:
Can someone help me build a formula that will Return this answers from the data below My lookup value will be my date i.e 10/24 My answer on this one would 1st lowest= 4:00 pm 20% 17... second lowest would be 3:00 pm 65% 16 . I think a Index,Match and Samll will work I just cant put it together. *The 2 Lowest Service Levels on column E11 and E12 *What interval they came in at on Column D11 and D12 *Howmany Calls came in during those intervals on Column F11 and F12 C D E F Date Interval SL Calls 1 10/24/2012 3:00 PM 65% 16 2 10/24/2012 4:00 PM 20% 17 3 1/24/1900 5:00 PM 87% 18 4 10/25/2012 6:00 PM 60% 19 10/25/2012 7:00 PM 16% 20 Lookup Value 10/24/2012 11 What interval What SL Howmany Calls 12 What interval What SL Howmany Calls Thank you Clause this works |
| All times are GMT +1. The time now is 05:06 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com