|
min and if
Hi
Anyone can solve this problem I have 2 rows a b c d e f 46 55 66 46 55 56 I used min fuction it gives me the first 46 I want to to hvae the 2 46 with the names a and d If can not solve this since it has one criterion Thanks for any help Jam |
min and if
I suggest that you transpose your data from rows into columns. Filtering can
be applied to column data, but not row data. In column form, you can just turn on Autofilter and select the 46 values. -- Gary's Student "jam" wrote: Hi Anyone can solve this problem I have 2 rows a b c d e f 46 55 66 46 55 56 I used min fuction it gives me the first 46 I want to to hvae the 2 46 with the names a and d If can not solve this since it has one criterion Thanks for any help Jam |
min and if
Hi!
Assume this data is in the range A1:F2 a b c d e f 46 55 66 46 55 56 Enter this formulas as an array using the key combination of CTRL,SHIFT,ENTER in cell A5 and copy across to F5: =IF(COLUMNS($A:A)<=COUNTIF($A2:$F2,MIN($A2:$F2)),I NDEX($A1:$F1,SMALL(IF($A2:$F2=MIN($A2:$F2),COLUMN( $A1:$F1)-COLUMN($A:$A)+1),COLUMNS($A:A))),"") Enter this formula in A6 and copy across to F6: =IF(A5="","",INDEX($A2:$F2,MATCH(A5,$A1:$F1,0))) The results will be: ...........A..........B..........C..........D..... .....E..........F.......... 5........a...........d............................ ................................ 6.......46.........46............................. .............................. Biff "jam" wrote in message oups.com... Hi Anyone can solve this problem I have 2 rows a b c d e f 46 55 66 46 55 56 I used min fuction it gives me the first 46 I want to to hvae the 2 46 with the names a and d If can not solve this since it has one criterion Thanks for any help Jam |
min and if
Hi Biff
Thanks for your reply Up to now it still give me an error in formula in $F2,MIN, I do not know why? Itrierd to put the foemula in {} it gives error yoo Thanks for help Jam Biff wrote: Hi! Assume this data is in the range A1:F2 a b c d e f 46 55 66 46 55 56 Enter this formulas as an array using the key combination of CTRL,SHIFT,ENTER in cell A5 and copy across to F5: =IF(COLUMNS($A:A)<=COUNTIF($A2:$F2,MIN($A2:$F2)),I NDEX($A1:$F1,SMALL(IF($A2:$F2=MIN($A2:$F2),COLUMN( $A1:$F1)-COLUMN($A:$A)+1),COLUMNS($A:A))),"") Enter this formula in A6 and copy across to F6: =IF(A5="","",INDEX($A2:$F2,MATCH(A5,$A1:$F1,0))) The results will be: ..........A..........B..........C..........D...... ....E..........F.......... 5........a...........d............................ ................................ 6.......46.........46............................. .............................. Biff "jam" wrote in message oups.com... Hi Anyone can solve this problem I have 2 rows a b c d e f 46 55 66 46 55 56 I used min fuction it gives me the first 46 I want to to hvae the 2 46 with the names a and d If can not solve this since it has one criterion Thanks for any help Jam |
min and if
Post the *EXACT* formula you tried along with the data involved.
Biff "jam" wrote in message ups.com... Hi Biff Thanks for your reply Up to now it still give me an error in formula in $F2,MIN, I do not know why? Itrierd to put the foemula in {} it gives error yoo Thanks for help Jam Biff wrote: Hi! Assume this data is in the range A1:F2 a b c d e f 46 55 66 46 55 56 Enter this formulas as an array using the key combination of CTRL,SHIFT,ENTER in cell A5 and copy across to F5: =IF(COLUMNS($A:A)<=COUNTIF($A2:$F2,MIN($A2:$F2)),I NDEX($A1:$F1,SMALL(IF($A2:$F2=MIN($A2:$F2),COLUMN( $A1:$F1)-COLUMN($A:$A)+1),COLUMNS($A:A))),"") Enter this formula in A6 and copy across to F6: =IF(A5="","",INDEX($A2:$F2,MATCH(A5,$A1:$F1,0))) The results will be: ..........A..........B..........C..........D...... ....E..........F.......... 5........a...........d............................ ................................ 6.......46.........46............................. .............................. Biff "jam" wrote in message oups.com... Hi Anyone can solve this problem I have 2 rows a b c d e f 46 55 66 46 55 56 I used min fuction it gives me the first 46 I want to to hvae the 2 46 with the names a and d If can not solve this since it has one criterion Thanks for any help Jam |
min and if
Hi Biff
I did exactly the same as you suggested I put in a new worksheet from a1 to f1 abcdef, and in a2to f2 the numbers I copied the same formula that you posted Still give error Any help Thanks Jam |
min and if
Hi Biff
I did exactly the same as you suggested I put in a new worksheet from a1 to f1 abcdef, and in a2to f2 the numbers I copied the same formula that you posted Still give error Any help Thanks Jam |
| All times are GMT +1. The time now is 06:24 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com