Conditional comparison of values.
Hi, I'm trying to select the highest value in a column (which I can do with
=MAX), but only if the value in an adjacent column is a negative number e.g. A B 1 10 18 2 -3 20 3 4 22 Although B3 is the highest value, I want to select B2 as it is the next highest with a negative value in the equivalent row, A2. Presumably I need some sort of condition applied to the MAX function ? |
Conditional comparison of values.
Try this array formula** :
=MAX(IF(A1:A3<0,B1:B3)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Graham" wrote in message ... Hi, I'm trying to select the highest value in a column (which I can do with =MAX), but only if the value in an adjacent column is a negative number e.g. A B 1 10 18 2 -3 20 3 4 22 Although B3 is the highest value, I want to select B2 as it is the next highest with a negative value in the equivalent row, A2. Presumably I need some sort of condition applied to the MAX function ? |
Conditional comparison of values.
Dear Biff
Many thanks for your help, looks to be working OK!! I'll now try to modify it to create a dynamic range. =MAX(IF(G38:G56<0,G38:G56)) "T. Valko" wrote: Try this array formula** : =MAX(IF(A1:A3<0,B1:B3)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Graham" wrote in message ... Hi, I'm trying to select the highest value in a column (which I can do with =MAX), but only if the value in an adjacent column is a negative number e.g. A B 1 10 18 2 -3 20 3 4 22 Although B3 is the highest value, I want to select B2 as it is the next highest with a negative value in the equivalent row, A2. Presumably I need some sort of condition applied to the MAX function ? |
Conditional comparison of values.
Hi, I'm trying to amend the array formula shown in my post below into a
dynamic array formula. I've got as far as the following : {=MAX(IF(OFFSET(C38,0,0,$A$1-ROW(C38)+1,1)<0,(OFFSET(G38,0,0,$A$1-ROW(G38)+1,1))))} But I'm getting an error message returned. Can anyone spot where I'm going wrong ? Many Thanks "Graham" wrote: Dear Biff Many thanks for your help, looks to be working OK!! I'll now try to modify it to create a dynamic range. =MAX(IF(G38:G56<0,G38:G56)) "T. Valko" wrote: Try this array formula** : =MAX(IF(A1:A3<0,B1:B3)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Graham" wrote in message ... Hi, I'm trying to select the highest value in a column (which I can do with =MAX), but only if the value in an adjacent column is a negative number e.g. A B 1 10 18 2 -3 20 3 4 22 Although B3 is the highest value, I want to select B2 as it is the next highest with a negative value in the equivalent row, A2. Presumably I need some sort of condition applied to the MAX function ? |
Conditional comparison of values.
{=MAX(IF(OFFSET(C38,0,0,$A$1-ROW(C38)+1,1)<0,(OFFSET(G38,0,0,$A$1-ROW(G38)+1,1))))}
Assuming there are no empty/blank cells *within* the range. Array entered: =MAX(IF(C38:INDEX(C38:C56,COUNT(C38:C56))<0,G38:IN DEX(G38:G56,COUNT(C38:C56)))) But, why use a dynamic range when the entire range is relatively small? -- Biff Microsoft Excel MVP "Graham" wrote in message ... Hi, I'm trying to amend the array formula shown in my post below into a dynamic array formula. I've got as far as the following : {=MAX(IF(OFFSET(C38,0,0,$A$1-ROW(C38)+1,1)<0,(OFFSET(G38,0,0,$A$1-ROW(G38)+1,1))))} But I'm getting an error message returned. Can anyone spot where I'm going wrong ? Many Thanks "Graham" wrote: Dear Biff Many thanks for your help, looks to be working OK!! I'll now try to modify it to create a dynamic range. =MAX(IF(G38:G56<0,G38:G56)) "T. Valko" wrote: Try this array formula** : =MAX(IF(A1:A3<0,B1:B3)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Graham" wrote in message ... Hi, I'm trying to select the highest value in a column (which I can do with =MAX), but only if the value in an adjacent column is a negative number e.g. A B 1 10 18 2 -3 20 3 4 22 Although B3 is the highest value, I want to select B2 as it is the next highest with a negative value in the equivalent row, A2. Presumably I need some sort of condition applied to the MAX function ? |
Conditional comparison of values.
Dear Biff
Many thanks for your help once again. Although the range is small at present, a new row will be added daily and I want to process data from the entire range, the last 10 entries, and the last 5 entries, all to be updated daily. I've seen previous posts from yourself on this subject that may also be useful. Regards Graham "T. Valko" wrote: {=MAX(IF(OFFSET(C38,0,0,$A$1-ROW(C38)+1,1)<0,(OFFSET(G38,0,0,$A$1-ROW(G38)+1,1))))} Assuming there are no empty/blank cells *within* the range. Array entered: =MAX(IF(C38:INDEX(C38:C56,COUNT(C38:C56))<0,G38:IN DEX(G38:G56,COUNT(C38:C56)))) But, why use a dynamic range when the entire range is relatively small? -- Biff Microsoft Excel MVP "Graham" wrote in message ... Hi, I'm trying to amend the array formula shown in my post below into a dynamic array formula. I've got as far as the following : {=MAX(IF(OFFSET(C38,0,0,$A$1-ROW(C38)+1,1)<0,(OFFSET(G38,0,0,$A$1-ROW(G38)+1,1))))} But I'm getting an error message returned. Can anyone spot where I'm going wrong ? Many Thanks "Graham" wrote: Dear Biff Many thanks for your help, looks to be working OK!! I'll now try to modify it to create a dynamic range. =MAX(IF(G38:G56<0,G38:G56)) "T. Valko" wrote: Try this array formula** : =MAX(IF(A1:A3<0,B1:B3)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Graham" wrote in message ... Hi, I'm trying to select the highest value in a column (which I can do with =MAX), but only if the value in an adjacent column is a negative number e.g. A B 1 10 18 2 -3 20 3 4 22 Although B3 is the highest value, I want to select B2 as it is the next highest with a negative value in the equivalent row, A2. Presumably I need some sort of condition applied to the MAX function ? |
Conditional comparison of values.
Ok, I see why you want a dynamic range.
Thanks for the feedback! -- Biff Microsoft Excel MVP "Graham" wrote in message ... Dear Biff Many thanks for your help once again. Although the range is small at present, a new row will be added daily and I want to process data from the entire range, the last 10 entries, and the last 5 entries, all to be updated daily. I've seen previous posts from yourself on this subject that may also be useful. Regards Graham "T. Valko" wrote: {=MAX(IF(OFFSET(C38,0,0,$A$1-ROW(C38)+1,1)<0,(OFFSET(G38,0,0,$A$1-ROW(G38)+1,1))))} Assuming there are no empty/blank cells *within* the range. Array entered: =MAX(IF(C38:INDEX(C38:C56,COUNT(C38:C56))<0,G38:IN DEX(G38:G56,COUNT(C38:C56)))) But, why use a dynamic range when the entire range is relatively small? -- Biff Microsoft Excel MVP "Graham" wrote in message ... Hi, I'm trying to amend the array formula shown in my post below into a dynamic array formula. I've got as far as the following : {=MAX(IF(OFFSET(C38,0,0,$A$1-ROW(C38)+1,1)<0,(OFFSET(G38,0,0,$A$1-ROW(G38)+1,1))))} But I'm getting an error message returned. Can anyone spot where I'm going wrong ? Many Thanks "Graham" wrote: Dear Biff Many thanks for your help, looks to be working OK!! I'll now try to modify it to create a dynamic range. =MAX(IF(G38:G56<0,G38:G56)) "T. Valko" wrote: Try this array formula** : =MAX(IF(A1:A3<0,B1:B3)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Graham" wrote in message ... Hi, I'm trying to select the highest value in a column (which I can do with =MAX), but only if the value in an adjacent column is a negative number e.g. A B 1 10 18 2 -3 20 3 4 22 Although B3 is the highest value, I want to select B2 as it is the next highest with a negative value in the equivalent row, A2. Presumably I need some sort of condition applied to the MAX function ? |
All times are GMT +1. The time now is 09:20 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com