Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Question - 2nd Inquiry
{=SMALL(IF(FREQUENCY(SMALL($E$6:$F$10,ROW(INDIRECT ("1:6"))),SMALL($E$6:$F$10,ROW(INDIRECT("1:6")))) 0,SMALL($E$6:$F$10,ROW(INDIRECT("1:6"))),""),COLUM N()-COLUMN($AC$60)+1)} The above formula displays 6 cell's values and eliminates any duplicates. E F 6/7: 3 7 8/9: 4 7 10/11: 8 8 The result is 3 4 7 8 If I wanted to display another cell in addition to the 6 described, ( G22 ), how would I say it in this formula. When I try, (($E$:$F10,$G22),ROW,,,,,,and increase the value of ("1:6") to ("1:7")I always get too many arguments error. Thanks in advance. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Question - 2nd Inquiry
I think you may be able to simplify things a little.
Assuming "Array" is (E60:F10,G22), enter: AC60: =MIN(Array) AD60: =SMALL(Array,COUNT(Array)-RANK(AC60,Array)+2) (or whatever your range to fill is) and fill right for the unique values. ["Array" could also refer to multiple sheets e.g. Sheet1:Sheet3!E:G] |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Question - 2nd Inquiry
Thanks Lori, I'm not sure where the E60 comes into play. Can you explain?
"Lori" wrote: I think you may be able to simplify things a little. Assuming "Array" is (E60:F10,G22), enter: AC60: =MIN(Array) AD60: =SMALL(Array,COUNT(Array)-RANK(AC60,Array)+2) (or whatever your range to fill is) and fill right for the unique values. ["Array" could also refer to multiple sheets e.g. Sheet1:Sheet3!E:G] |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Question - 2nd Inquiry
=SMALL(E6:F11,D18,COUNT(E6:F11,D18)-RANK(AC60,E6:F11,D18)+2) results in a
too many arguments ( this is the correct array Im using ) Placed in AD60 "Lori" wrote: I think you may be able to simplify things a little. Assuming "Array" is (E60:F10,G22), enter: AC60: =MIN(Array) AD60: =SMALL(Array,COUNT(Array)-RANK(AC60,Array)+2) (or whatever your range to fill is) and fill right for the unique values. ["Array" could also refer to multiple sheets e.g. Sheet1:Sheet3!E:G] |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Question - 2nd Inquiry
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Question - 2nd Inquiry
Made attempts with this with no positive outcome. I do not need a report, I
need to populate cells. I may be not understanding this completely.. "Herbert Seidenberg" wrote: Or with no formulas at all: (bonus: flags repeats) http://www.freefilehosting.net/download/3ed64 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Question - 2nd Inquiry
Hi Shu, you need the extra brackets for multiple ranges:
=SMALL((E6:F11,D18),COUNT(E6:F11,D18)-RANK(AC60,(E6:F11,D18))+2) but my preference is to use a named range instead. (You can do this by selecting the range and typing "Array" in the Name Box which is just to the left of the Formula Bar) "Shu of AZ" wrote: =SMALL(E6:F11,D18,COUNT(E6:F11,D18)-RANK(AC60,E6:F11,D18)+2) results in a too many arguments ( this is the correct array Im using ) Placed in AD60 "Lori" wrote: I think you may be able to simplify things a little. Assuming "Array" is (E60:F10,G22), enter: AC60: =MIN(Array) AD60: =SMALL(Array,COUNT(Array)-RANK(AC60,Array)+2) (or whatever your range to fill is) and fill right for the unique values. ["Array" could also refer to multiple sheets e.g. Sheet1:Sheet3!E:G] |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table inquiry | Excel Discussion (Misc queries) | |||
Counting Function Inquiry | Excel Discussion (Misc queries) | |||
a exact string search inquiry | Excel Worksheet Functions | |||
Newbie Question - Subtraction Formula Question | Excel Discussion (Misc queries) | |||
Pivot Table inquiry | Excel Discussion (Misc queries) |