| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
INDEX MATCH SMALL
|
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
INDEX MATCH SMALL
Hi,
Am Mon, 29 Oct 2012 11:49:24 -0700 (PDT) schrieb : 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? 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? Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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... |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| INDEX, SMALL, ROW | Excel Worksheet Functions | |||
| index(match) Wind Uplift Calculations (match four conditions) | Excel Worksheet Functions | |||
| Index,Match,Small | Excel Discussion (Misc queries) | |||
| ISERROR,SMALL,INDEX, MATCH, SMALL?? | Excel Discussion (Misc queries) | |||
| Vlookup/Match/Index/Small - I think | Excel Worksheet Functions | |||
Linear Mode
