Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index function error
Hi all, I have a problem with an index function i created. Here is what the function looks like: *=INDEX(SMALL(C3:C782,{1;2;3;4;5}),OR(MATCH(1,A3:A 782=E7,0),MATCH(1,B3:B782=E7,0)))* The intended purpose with it is to find the 5 smallest values from cells C3:C782 if cells A3:A782 MATCH E7 OR if cells B3:B782 Match E7. Hope that makes sense if not i will be happy to elaborate. At the moment i get error #N/A with my statement. Thanks in advance for any help, Motty. -- Motty ------------------------------------------------------------------------ Motty's Profile: http://www.excelforum.com/member.php...o&userid=29233 View this thread: http://www.excelforum.com/showthread...hreadid=490551 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index function error
A very complex formula; not sure what it is trying to do.
I played with it with some dummy data (only row 3 to 16) 1) it must be entered with CRTL+SHIFT+ENTER as it is an array formula) 2) you must coerce the Boolean values to 0/1; I used double negation =INDEX(SMALL(C3:C16,{1;2;3;4;5}),OR(MATCH(1,--(A3:A16=E7),0),MATCH(1,--(B3:B16=E7),0))) Not sure if it does what you want but I got a numeric value. -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Motty" wrote in message ... Hi all, I have a problem with an index function i created. Here is what the function looks like: *=INDEX(SMALL(C3:C782,{1;2;3;4;5}),OR(MATCH(1,A3:A 782=E7,0),MATCH(1,B3:B782=E7,0)))* The intended purpose with it is to find the 5 smallest values from cells C3:C782 if cells A3:A782 MATCH E7 OR if cells B3:B782 Match E7. Hope that makes sense if not i will be happy to elaborate. At the moment i get error #N/A with my statement. Thanks in advance for any help, Motty. -- Motty ------------------------------------------------------------------------ Motty's Profile: http://www.excelforum.com/member.php...o&userid=29233 View this thread: http://www.excelforum.com/showthread...hreadid=490551 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index function error
One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):
D1:D5: =SMALL(IF((A3:A786=E7)+(B3:B786=E7),C3:C786),{1;2; 3;4;5}) In article , Motty wrote: Hi all, I have a problem with an index function i created. Here is what the function looks like: *=INDEX(SMALL(C3:C782,{1;2;3;4;5}),OR(MATCH(1,A3:A 782=E7,0),MATCH(1,B3:B782=E7 ,0)))* The intended purpose with it is to find the 5 smallest values from cells C3:C782 if cells A3:A782 MATCH E7 OR if cells B3:B782 Match E7. Hope that makes sense if not i will be happy to elaborate. At the moment i get error #N/A with my statement. Thanks in advance for any help, Motty. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index function error
Try...
=SMALL(IF((A3:A782=E7)+(B3:B782=E7),C3:C782),1) ....confirmed with CONTROL+SHIFT+ENTER. Change the formula accordingly for the second smallest, third, fourth, and fifth. Hope this helps! In article , Motty wrote: Hi all, I have a problem with an index function i created. Here is what the function looks like: *=INDEX(SMALL(C3:C782,{1;2;3;4;5}),OR(MATCH(1,A3:A 782=E7,0),MATCH(1,B3:B782=E7 ,0)))* The intended purpose with it is to find the 5 smallest values from cells C3:C782 if cells A3:A782 MATCH E7 OR if cells B3:B782 Match E7. Hope that makes sense if not i will be happy to elaborate. At the moment i get error #N/A with my statement. Thanks in advance for any help, Motty. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index function error
Hi all, Thanks for the replies all were very useful I ended up using JE McGimpsey formula as it does exactley what i wanted, the things is the value E7 changes when you want to copy and paste so moving to E8, E9 and so on, which is what i wanted. Thanks for the help guys very much appreciated, :) Motty. -- Motty ------------------------------------------------------------------------ Motty's Profile: http://www.excelforum.com/member.php...o&userid=29233 View this thread: http://www.excelforum.com/showthread...hreadid=490551 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel option to store trendline's coefficients in cells for use | Charts and Charting in Excel | |||
Date & Time | New Users to Excel | |||
How can I download and install ERROR FUNCTION in Excel 2003? ERF. | Excel Worksheet Functions | |||
Lookup Function Error | Excel Worksheet Functions | |||
Erf (the error function) for both negative and positive numbers | Excel Discussion (Misc queries) |