| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
MIN ARRAY FORMULA
Hi {=ADDRESS(MIN(IF($I$9:$I$373=MIN($I$9:$I$373)*($I$ 9:$I$373<0),ROW($I$9:$I$373))),MIN(IF($I$9:$I$373 =MIN($I$9:$I$373)*($I$9:$I$373<0),COLUMN($I$9:$I$ 373))))} This formula only works fine except when there are zeros in column I even though the formula is looking at vlaues < 0 For example: rows 9 to 84 have values, rows 85 to 373 have 0. The formula gives the result of I85. If rows 85 to 373 are left blank the result is correct. Can anyone help? -- bookman |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
MIN ARRAY FORMULA
I'm assuming that you want the cell address of the minimum value in a
column. Since your dealing with a single, *known* column (Column I), I don't understand why your calculating the second argument of the Address() function. Simply plug in "9" for the ninth column which is "I". If my assumptions are correct, this should work for you: =ADDRESS(MATCH(SMALL(I9:I373,COUNTIF(I9:I373,0)+1) ,I9:I373,0)+8,9) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "bookman3" wrote in message ... Hi {=ADDRESS(MIN(IF($I$9:$I$373=MIN($I$9:$I$373)*($I$ 9:$I$373<0),ROW($I$9:$I$373))),MIN(IF($I$9:$I$373 =MIN($I$9:$I$373)*($I$9:$I$373<0),COLUMN($I$9:$I$ 373))))} This formula only works fine except when there are zeros in column I even though the formula is looking at vlaues < 0 For example: rows 9 to 84 have values, rows 85 to 373 have 0. The formula gives the result of I85. If rows 85 to 373 are left blank the result is correct. Can anyone help? -- bookman |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
MIN ARRAY FORMULA
Hi
That works a treat. I have 2 supplementary questions If the same minimum value appears more than once in the list,how can I find the last entry? I replaced SMALL with MAX. That worked but if there are more than 2 maximum values in the list, how can I find the last entry? -- bookman "bookman3" wrote: Hi {=ADDRESS(MIN(IF($I$9:$I$373=MIN($I$9:$I$373)*($I$ 9:$I$373<0),ROW($I$9:$I$373))),MIN(IF($I$9:$I$373 =MIN($I$9:$I$373)*($I$9:$I$373<0),COLUMN($I$9:$I$ 373))))} This formula only works fine except when there are zeros in column I even though the formula is looking at vlaues < 0 For example: rows 9 to 84 have values, rows 85 to 373 have 0. The formula gives the result of I85. If rows 85 to 373 are left blank the result is correct. Can anyone help? -- bookman |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
MIN ARRAY FORMULA
Are your numbers sorted in descending order?
-- Biff Microsoft Excel MVP "bookman3" wrote in message ... Hi That works a treat. I have 2 supplementary questions If the same minimum value appears more than once in the list,how can I find the last entry? I replaced SMALL with MAX. That worked but if there are more than 2 maximum values in the list, how can I find the last entry? -- bookman "bookman3" wrote: Hi {=ADDRESS(MIN(IF($I$9:$I$373=MIN($I$9:$I$373)*($I$ 9:$I$373<0),ROW($I$9:$I$373))),MIN(IF($I$9:$I$373 =MIN($I$9:$I$373)*($I$9:$I$373<0),COLUMN($I$9:$I$ 373))))} This formula only works fine except when there are zeros in column I even though the formula is looking at vlaues < 0 For example: rows 9 to 84 have values, rows 85 to 373 have 0. The formula gives the result of I85. If rows 85 to 373 are left blank the result is correct. Can anyone help? -- bookman |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
MIN ARRAY FORMULA
No the nmubers are random, actually the column to the left is in date order
and this column has the amount relating to that date -- bookman "T. Valko" wrote: Are your numbers sorted in descending order? -- Biff Microsoft Excel MVP "bookman3" wrote in message ... Hi That works a treat. I have 2 supplementary questions If the same minimum value appears more than once in the list,how can I find the last entry? I replaced SMALL with MAX. That worked but if there are more than 2 maximum values in the list, how can I find the last entry? -- bookman "bookman3" wrote: Hi {=ADDRESS(MIN(IF($I$9:$I$373=MIN($I$9:$I$373)*($I$ 9:$I$373<0),ROW($I$9:$I$373))),MIN(IF($I$9:$I$373 =MIN($I$9:$I$373)*($I$9:$I$373<0),COLUMN($I$9:$I$ 373))))} This formula only works fine except when there are zeros in column I even though the formula is looking at vlaues < 0 For example: rows 9 to 84 have values, rows 85 to 373 have 0. The formula gives the result of I85. If rows 85 to 373 are left blank the result is correct. Can anyone help? -- bookman |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
MIN ARRAY FORMULA
Ok, do you specifically need the cell address or do want the actual number
but the only way you know how to find it is by using the address formula? We can do either one. -- Biff Microsoft Excel MVP "bookman3" wrote in message ... No the nmubers are random, actually the column to the left is in date order and this column has the amount relating to that date -- bookman "T. Valko" wrote: Are your numbers sorted in descending order? -- Biff Microsoft Excel MVP "bookman3" wrote in message ... Hi That works a treat. I have 2 supplementary questions If the same minimum value appears more than once in the list,how can I find the last entry? I replaced SMALL with MAX. That worked but if there are more than 2 maximum values in the list, how can I find the last entry? -- bookman "bookman3" wrote: Hi {=ADDRESS(MIN(IF($I$9:$I$373=MIN($I$9:$I$373)*($I$ 9:$I$373<0),ROW($I$9:$I$373))),MIN(IF($I$9:$I$373 =MIN($I$9:$I$373)*($I$9:$I$373<0),COLUMN($I$9:$I$ 373))))} This formula only works fine except when there are zeros in column I even though the formula is looking at vlaues < 0 For example: rows 9 to 84 have values, rows 85 to 373 have 0. The formula gives the result of I85. If rows 85 to 373 are left blank the result is correct. Can anyone help? -- bookman |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
MIN ARRAY FORMULA
I know the values, I just want to display the cell references of the latest
largest and smallest values, ignoring zeros as the zero values are next to future dates -- bookman "T. Valko" wrote: Ok, do you specifically need the cell address or do want the actual number but the only way you know how to find it is by using the address formula? We can do either one. -- Biff Microsoft Excel MVP "bookman3" wrote in message ... No the nmubers are random, actually the column to the left is in date order and this column has the amount relating to that date -- bookman "T. Valko" wrote: Are your numbers sorted in descending order? -- Biff Microsoft Excel MVP "bookman3" wrote in message ... Hi That works a treat. I have 2 supplementary questions If the same minimum value appears more than once in the list,how can I find the last entry? I replaced SMALL with MAX. That worked but if there are more than 2 maximum values in the list, how can I find the last entry? -- bookman "bookman3" wrote: Hi {=ADDRESS(MIN(IF($I$9:$I$373=MIN($I$9:$I$373)*($I$ 9:$I$373<0),ROW($I$9:$I$373))),MIN(IF($I$9:$I$373 =MIN($I$9:$I$373)*($I$9:$I$373<0),COLUMN($I$9:$I$ 373))))} This formula only works fine except when there are zeros in column I even though the formula is looking at vlaues < 0 For example: rows 9 to 84 have values, rows 85 to 373 have 0. The formula gives the result of I85. If rows 85 to 373 are left blank the result is correct. Can anyone help? -- bookman |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Array formula SUMIF with 2D sum_range array | Excel Worksheet Functions | |||
| Array formula: how to join 2 ranges together to form one array? | Excel Worksheet Functions | |||
| Find specific value in array of array formula | Excel Worksheet Functions | |||
| meaning of : IF(Switch; Average(array A, array B); array A) | Excel Worksheet Functions | |||
| Array Formula - using LEFT("text",4) in formula | Excel Worksheet Functions | |||
Hybrid Mode
