| 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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
MIN ARRAY FORMULA
Ok, I'm assuming that latest means the furthest down the column. Like this:
A1 = 90 A2 = 5 A3 = 90 A4 = 5 Max address would be A3. Min address would be A4 Address for the max: =ADDRESS(LOOKUP(2,1/(I9:I373=MAX(I9:I373)),ROW(I9:I373)),9,4) Address for the min: Array entered** =ADDRESS(MAX(IF(I9:I373=MIN(IF(I9:I3730,I9:I373)) ,ROW(I9:I373))),9,4) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "bookman3" wrote in message ... 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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
MIN ARRAY FORMULA
Both are *array* formulas.
For *last* minimum entry: =ADDRESS(MAX((I9:I373=MIN(I9:I30))*ROW(I9:I373)),9 ) For *last* maximum entry: =ADDRESS(MAX((I9:I373=MAX(I9:I30))*ROW(I9:I373)),9 ) -- Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. Also, CSE *must* be used when revising the formula. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "bookman3" wrote in message ... 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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
MIN ARRAY FORMULA
*TYPOS*
Both are *array* formulas. For *last* minimum entry: =ADDRESS(MAX((I9:I373=MIN(I9:I373))*ROW(I9:I373)), 9) For *last* maximum entry: =ADDRESS(MAX((I9:I373=MAX(I9:I373))*ROW(I9:I373)), 9) -- Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. Also, CSE *must* be used when revising the formula. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "RagDyeR" wrote in message ... Both are *array* formulas. For *last* minimum entry: =ADDRESS(MAX((I9:I373=MIN(I9:I30))*ROW(I9:I373)),9 ) For *last* maximum entry: =ADDRESS(MAX((I9:I373=MAX(I9:I30))*ROW(I9:I373)),9 ) -- Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. Also, CSE *must* be used when revising the formula. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "bookman3" wrote in message ... 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 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
MIN ARRAY FORMULA
*BAD* morning -
Correction for *last* minimum entry: =ADDRESS(MAX((I9:I373=SMALL(I9:I373,COUNTIF(I9:I37 3,0)+1))*ROW(I9:I373)),9) -- Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. Also, CSE *must* be used when revising the formula. -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "RagDyeR" wrote in message ... *TYPOS* Both are *array* formulas. For *last* minimum entry: =ADDRESS(MAX((I9:I373=MIN(I9:I373))*ROW(I9:I373)), 9) For *last* maximum entry: =ADDRESS(MAX((I9:I373=MAX(I9:I373))*ROW(I9:I373)), 9) -- Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. Also, CSE *must* be used when revising the formula. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "RagDyeR" wrote in message ... Both are *array* formulas. For *last* minimum entry: =ADDRESS(MAX((I9:I373=MIN(I9:I30))*ROW(I9:I373)),9 ) For *last* maximum entry: =ADDRESS(MAX((I9:I373=MAX(I9:I30))*ROW(I9:I373)),9 ) -- Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. Also, CSE *must* be used when revising the formula. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "bookman3" wrote in message ... 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 |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
MIN ARRAY FORMULA
For *last* minimum entry:
=ADDRESS(MAX((I9:I373=MIN(I9:I373))*ROW(I9:I373)) ,9) That won't exclude 0s. -- Biff Microsoft Excel MVP "RagDyeR" wrote in message ... *TYPOS* Both are *array* formulas. For *last* minimum entry: =ADDRESS(MAX((I9:I373=MIN(I9:I373))*ROW(I9:I373)), 9) For *last* maximum entry: =ADDRESS(MAX((I9:I373=MAX(I9:I373))*ROW(I9:I373)), 9) -- Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. Also, CSE *must* be used when revising the formula. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "RagDyeR" wrote in message ... Both are *array* formulas. For *last* minimum entry: =ADDRESS(MAX((I9:I373=MIN(I9:I30))*ROW(I9:I373)),9 ) For *last* maximum entry: =ADDRESS(MAX((I9:I373=MAX(I9:I30))*ROW(I9:I373)),9 ) -- Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. Also, CSE *must* be used when revising the formula. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "bookman3" wrote in message ... 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 |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
MIN ARRAY FORMULA
I'll assume by now, you've seen my last *VERY BAD MORNING* revision!
Tough to prioritize time when two transformers go at the same time.<bg -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "T. Valko" wrote in message ... For *last* minimum entry: =ADDRESS(MAX((I9:I373=MIN(I9:I373))*ROW(I9:I373) ),9) That won't exclude 0s. -- Biff Microsoft Excel MVP "RagDyeR" wrote in message ... *TYPOS* Both are *array* formulas. For *last* minimum entry: =ADDRESS(MAX((I9:I373=MIN(I9:I373))*ROW(I9:I373)), 9) For *last* maximum entry: =ADDRESS(MAX((I9:I373=MAX(I9:I373))*ROW(I9:I373)), 9) -- Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. Also, CSE *must* be used when revising the formula. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "RagDyeR" wrote in message ... Both are *array* formulas. For *last* minimum entry: =ADDRESS(MAX((I9:I373=MIN(I9:I30))*ROW(I9:I373)),9 ) For *last* maximum entry: =ADDRESS(MAX((I9:I373=MAX(I9:I30))*ROW(I9:I373)),9 ) -- Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. Also, CSE *must* be used when revising the formula. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "bookman3" wrote in message ... 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 |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
MIN ARRAY FORMULA
Tough to prioritize time when two transformers go at the same time.<bg
I can relate! -- Biff Microsoft Excel MVP "RagDyer" wrote in message ... I'll assume by now, you've seen my last *VERY BAD MORNING* revision! Tough to prioritize time when two transformers go at the same time.<bg -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "T. Valko" wrote in message ... For *last* minimum entry: =ADDRESS(MAX((I9:I373=MIN(I9:I373))*ROW(I9:I373 )),9) That won't exclude 0s. -- Biff Microsoft Excel MVP "RagDyeR" wrote in message ... *TYPOS* Both are *array* formulas. For *last* minimum entry: =ADDRESS(MAX((I9:I373=MIN(I9:I373))*ROW(I9:I373)), 9) For *last* maximum entry: =ADDRESS(MAX((I9:I373=MAX(I9:I373))*ROW(I9:I373)), 9) -- Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. Also, CSE *must* be used when revising the formula. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "RagDyeR" wrote in message ... Both are *array* formulas. For *last* minimum entry: =ADDRESS(MAX((I9:I373=MIN(I9:I30))*ROW(I9:I373)),9 ) For *last* maximum entry: =ADDRESS(MAX((I9:I373=MAX(I9:I30))*ROW(I9:I373)),9 ) -- Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. Also, CSE *must* be used when revising the formula. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "bookman3" wrote in message ... 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 | |||
Linear Mode
