Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Modify the existing formula
Dear sir,
There is 1 data set is assumed running in A1 down, viz.: In A1 down is: 1,2,3,4,5,6,7,8,9, and 10 In F1 down is: 20, 7, 1, 12 and 8 And I have a excel forumula in the cell M1 as below: {=IF(ROWS(M$1:M1)<=SUM(--(COUNTIF($F$1:$F$22,$A$1:$A$10)0)),INDEX($A$1:$A$ 10, SMALL(IF(COUNTIF($F$1:$F$22,$A$1:$A$10)0,ROW($A$1 :$A$10)-MIN(ROW($A$1:$A$10)) +1),ROWS(M$1:M1))),"")} And then I drag the formula from M1 to M5. Therefore, the formula will give me the result "1" in cell M1, "7" in cell M2 and "8" in cell M3. The formula is extracted the value from F column according to the database in A column. The formula also display the value from lowest value to highest value. My question is that how can I modify the existing formula if I want to display value in this order: 7,1 and 8. This order is exactly the order which find in F column. Many thanks, Wilchong -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...l-new/200812/1 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Modify the existing formula
Try this in M1, array-entered:
=IF(ROWS($1:1)SUM(COUNTIF(A$1:A$10,F$1:F$5)),"",I NDEX(F$1:F$5,SMALL(IF(COUNTIF(A$1:A$10,F$1:F$5),RO W(F$1:F$5)),ROWS($1:1)))) Copy down to M5. -- Max Singapore http://savefile.com/projects/236895 Downloads:21,000 Files:365 Subscribers:65 xdemechanik --- "wilchong via OfficeKB.com" <u43231@uwe wrote in message news:8ead8d080a297@uwe... Dear sir, There is 1 data set is assumed running in A1 down, viz.: In A1 down is: 1,2,3,4,5,6,7,8,9, and 10 In F1 down is: 20, 7, 1, 12 and 8 And I have a excel forumula in the cell M1 as below: {=IF(ROWS(M$1:M1)<=SUM(--(COUNTIF($F$1:$F$22,$A$1:$A$10)0)),INDEX($A$1:$A$ 10, SMALL(IF(COUNTIF($F$1:$F$22,$A$1:$A$10)0,ROW($A$1 :$A$10)-MIN(ROW($A$1:$A$10)) +1),ROWS(M$1:M1))),"")} And then I drag the formula from M1 to M5. Therefore, the formula will give me the result "1" in cell M1, "7" in cell M2 and "8" in cell M3. The formula is extracted the value from F column according to the database in A column. The formula also display the value from lowest value to highest value. My question is that how can I modify the existing formula if I want to display value in this order: 7,1 and 8. This order is exactly the order which find in F column. Many thanks, Wilchong -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...l-new/200812/1 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Modify the existing formula
Dear Max,
Many thanks for your advice and suggested formula, it works very well. However, I found one thing which I don't really understand. The suggested formula will give me the result as : 1, 12 & 0 after I moved the whole series of data (20, 7, 1, 12 and 8) one cell downward (it means the series data starting from F2). After I study the formular, I found that the result back to 7, 1 & 8 after I revised the range in the formular ROWS starting from 1. I don't understand why it will happen? Is that any way I can revise the suggested formula even though I move the set data from F1 to F10? Many thanks, Wilchong Max wrote: Try this in M1, array-entered: =IF(ROWS($1:1)SUM(COUNTIF(A$1:A$10,F$1:F$5)),"", INDEX(F$1:F$5,SMALL(IF(COUNTIF(A$1:A$10,F$1:F$5),R OW(F$1:F$5)),ROWS($1:1)))) Copy down to M5. Dear sir, [quoted text clipped - 22 lines] Many thanks, Wilchong -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...l-new/200812/1 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Modify the existing formula
If you change this term:
ROW(F$1:F$5) in the earlier expression to: ROW($1:$5) that'll make it robust for your movement of the data series in F1:F5 to wherever -- Max Singapore http://savefile.com/projects/236895 Downloads:21,000 Files:370 Subscribers:66 xdemechanik --- "wilchong via OfficeKB.com" <u43231@uwe wrote in message news:8ebba08d24c0e@uwe... Dear Max, Many thanks for your advice and suggested formula, it works very well. However, I found one thing which I don't really understand. The suggested formula will give me the result as : 1, 12 & 0 after I moved the whole series of data (20, 7, 1, 12 and 8) one cell downward (it means the series data starting from F2). After I study the formular, I found that the result back to 7, 1 & 8 after I revised the range in the formular ROWS starting from 1. I don't understand why it will happen? Is that any way I can revise the suggested formula even though I move the set data from F1 to F10? Many thanks, Wilchong |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Modify Formula | Excel Worksheet Functions | |||
How do I delete or modify an existing drop down list? | Excel Discussion (Misc queries) | |||
Modify a Formula | Excel Worksheet Functions | |||
Modify an existing Cell formula using VBA | Excel Discussion (Misc queries) | |||
How do I modify an existing worksheet to remove columns & contents | Excel Discussion (Misc queries) |