|
Conditional Max
Hi,
I am trying to get a max age from a range of cells, however the result is depending on the value in another cell. The range looks like this: ID BU Age 11348 JPU 44 12479 FKG 46 12487 LPS 27 14539 LPS 32 17583 FKG 24 39175 LPS 34 45867 JPU 42 74583 FKG 39 I need a formula that will give me the max age in the following way: FKG 46 JPU 44 LPS 34 Is there a way to do this without using a pivot table? Many thanks, MLS |
Conditional Max
Hi,
Try this ARRAY formula. See below on how to enter it =MAX(IF(B1:B8="FKG",C1:C8)) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. Mike "MLS" wrote: Hi, I am trying to get a max age from a range of cells, however the result is depending on the value in another cell. The range looks like this: ID BU Age 11348 JPU 44 12479 FKG 46 12487 LPS 27 14539 LPS 32 17583 FKG 24 39175 LPS 34 45867 JPU 42 74583 FKG 39 I need a formula that will give me the max age in the following way: FKG 46 JPU 44 LPS 34 Is there a way to do this without using a pivot table? Many thanks, MLS |
Conditional Max
Hi Mike,
When I try it gives me a #VALUE! error. Would you know why? MLS "Mike H" wrote: Hi, Try this ARRAY formula. See below on how to enter it =MAX(IF(B1:B8="FKG",C1:C8)) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. Mike "MLS" wrote: Hi, I am trying to get a max age from a range of cells, however the result is depending on the value in another cell. The range looks like this: ID BU Age 11348 JPU 44 12479 FKG 46 12487 LPS 27 14539 LPS 32 17583 FKG 24 39175 LPS 34 45867 JPU 42 74583 FKG 39 I need a formula that will give me the max age in the following way: FKG 46 JPU 44 LPS 34 Is there a way to do this without using a pivot table? Many thanks, MLS |
Conditional Max
Hi,
I can't replicate that, please post the exact formula you used. Mike "MLS" wrote: Hi Mike, When I try it gives me a #VALUE! error. Would you know why? MLS "Mike H" wrote: Hi, Try this ARRAY formula. See below on how to enter it =MAX(IF(B1:B8="FKG",C1:C8)) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. Mike "MLS" wrote: Hi, I am trying to get a max age from a range of cells, however the result is depending on the value in another cell. The range looks like this: ID BU Age 11348 JPU 44 12479 FKG 46 12487 LPS 27 14539 LPS 32 17583 FKG 24 39175 LPS 34 45867 JPU 42 74583 FKG 39 I need a formula that will give me the max age in the following way: FKG 46 JPU 44 LPS 34 Is there a way to do this without using a pivot table? Many thanks, MLS |
Conditional Max
The formula I used was: =MAX(IF(B2:B9="FKG",C2:C9))
The only change I made from the formula you posted was to not include the header row (Row 1) and instead include the bottom row (Row 9). MLS "Mike H" wrote: Hi, I can't replicate that, please post the exact formula you used. Mike "MLS" wrote: Hi Mike, When I try it gives me a #VALUE! error. Would you know why? MLS "Mike H" wrote: Hi, Try this ARRAY formula. See below on how to enter it =MAX(IF(B1:B8="FKG",C1:C8)) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. Mike "MLS" wrote: Hi, I am trying to get a max age from a range of cells, however the result is depending on the value in another cell. The range looks like this: ID BU Age 11348 JPU 44 12479 FKG 46 12487 LPS 27 14539 LPS 32 17583 FKG 24 39175 LPS 34 45867 JPU 42 74583 FKG 39 I need a formula that will give me the max age in the following way: FKG 46 JPU 44 LPS 34 Is there a way to do this without using a pivot table? Many thanks, MLS |
Conditional Max
=SUMPRODUCT(MAX(($B$2:$B$9=E2)*$C$2:$C$9))
Just press ENTER "MLS" wrote: Hi, I am trying to get a max age from a range of cells, however the result is depending on the value in another cell. The range looks like this: ID BU Age 11348 JPU 44 12479 FKG 46 12487 LPS 27 14539 LPS 32 17583 FKG 24 39175 LPS 34 45867 JPU 42 74583 FKG 39 I need a formula that will give me the max age in the following way: FKG 46 JPU 44 LPS 34 Is there a way to do this without using a pivot table? Many thanks, MLS |
Conditional Max
Mike has suggested you an array formula..
An array formula can perform multiple calculations and then return either a single result or multiple results. Array formulas act on two or more sets of values known as array arguments. Each array argument must have the same number of rows and columns. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" If this post helps click Yes --------------- Jacob Skaria "MLS" wrote: The formula I used was: =MAX(IF(B2:B9="FKG",C2:C9)) The only change I made from the formula you posted was to not include the header row (Row 1) and instead include the bottom row (Row 9). MLS "Mike H" wrote: Hi, I can't replicate that, please post the exact formula you used. Mike "MLS" wrote: Hi Mike, When I try it gives me a #VALUE! error. Would you know why? MLS "Mike H" wrote: Hi, Try this ARRAY formula. See below on how to enter it =MAX(IF(B1:B8="FKG",C1:C8)) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. Mike "MLS" wrote: Hi, I am trying to get a max age from a range of cells, however the result is depending on the value in another cell. The range looks like this: ID BU Age 11348 JPU 44 12479 FKG 46 12487 LPS 27 14539 LPS 32 17583 FKG 24 39175 LPS 34 45867 JPU 42 74583 FKG 39 I need a formula that will give me the max age in the following way: FKG 46 JPU 44 LPS 34 Is there a way to do this without using a pivot table? Many thanks, MLS |
Conditional Max
Hi Jacob, thanks for the tip. It now works. I learned something new there!
MLS "Jacob Skaria" wrote: Mike has suggested you an array formula.. An array formula can perform multiple calculations and then return either a single result or multiple results. Array formulas act on two or more sets of values known as array arguments. Each array argument must have the same number of rows and columns. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" If this post helps click Yes --------------- Jacob Skaria "MLS" wrote: The formula I used was: =MAX(IF(B2:B9="FKG",C2:C9)) The only change I made from the formula you posted was to not include the header row (Row 1) and instead include the bottom row (Row 9). MLS "Mike H" wrote: Hi, I can't replicate that, please post the exact formula you used. Mike "MLS" wrote: Hi Mike, When I try it gives me a #VALUE! error. Would you know why? MLS "Mike H" wrote: Hi, Try this ARRAY formula. See below on how to enter it =MAX(IF(B1:B8="FKG",C1:C8)) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. Mike "MLS" wrote: Hi, I am trying to get a max age from a range of cells, however the result is depending on the value in another cell. The range looks like this: ID BU Age 11348 JPU 44 12479 FKG 46 12487 LPS 27 14539 LPS 32 17583 FKG 24 39175 LPS 34 45867 JPU 42 74583 FKG 39 I need a formula that will give me the max age in the following way: FKG 46 JPU 44 LPS 34 Is there a way to do this without using a pivot table? Many thanks, MLS |
Conditional Max
I tried this and it also works so thanks!
"Teethless mama" wrote: =SUMPRODUCT(MAX(($B$2:$B$9=E2)*$C$2:$C$9)) Just press ENTER "MLS" wrote: Hi, I am trying to get a max age from a range of cells, however the result is depending on the value in another cell. The range looks like this: ID BU Age 11348 JPU 44 12479 FKG 46 12487 LPS 27 14539 LPS 32 17583 FKG 24 39175 LPS 34 45867 JPU 42 74583 FKG 39 I need a formula that will give me the max age in the following way: FKG 46 JPU 44 LPS 34 Is there a way to do this without using a pivot table? Many thanks, MLS |
Conditional Max
Hmmm
"MLS" wrote: Hi Jacob, thanks for the tip. It now works. I learned something new there! MLS "Jacob Skaria" wrote: Mike has suggested you an array formula.. An array formula can perform multiple calculations and then return either a single result or multiple results. Array formulas act on two or more sets of values known as array arguments. Each array argument must have the same number of rows and columns. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" If this post helps click Yes --------------- Jacob Skaria "MLS" wrote: The formula I used was: =MAX(IF(B2:B9="FKG",C2:C9)) The only change I made from the formula you posted was to not include the header row (Row 1) and instead include the bottom row (Row 9). MLS "Mike H" wrote: Hi, I can't replicate that, please post the exact formula you used. Mike "MLS" wrote: Hi Mike, When I try it gives me a #VALUE! error. Would you know why? MLS "Mike H" wrote: Hi, Try this ARRAY formula. See below on how to enter it =MAX(IF(B1:B8="FKG",C1:C8)) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. Mike "MLS" wrote: Hi, I am trying to get a max age from a range of cells, however the result is depending on the value in another cell. The range looks like this: ID BU Age 11348 JPU 44 12479 FKG 46 12487 LPS 27 14539 LPS 32 17583 FKG 24 39175 LPS 34 45867 JPU 42 74583 FKG 39 I need a formula that will give me the max age in the following way: FKG 46 JPU 44 LPS 34 Is there a way to do this without using a pivot table? Many thanks, MLS |
Conditional Max
MLS; Mike has mentioned this in his initial post ...Did you miss that?
"MLS" wrote: Hi Jacob, thanks for the tip. It now works. I learned something new there! MLS "Jacob Skaria" wrote: Mike has suggested you an array formula.. An array formula can perform multiple calculations and then return either a single result or multiple results. Array formulas act on two or more sets of values known as array arguments. Each array argument must have the same number of rows and columns. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" If this post helps click Yes --------------- Jacob Skaria "MLS" wrote: The formula I used was: =MAX(IF(B2:B9="FKG",C2:C9)) The only change I made from the formula you posted was to not include the header row (Row 1) and instead include the bottom row (Row 9). MLS "Mike H" wrote: Hi, I can't replicate that, please post the exact formula you used. Mike "MLS" wrote: Hi Mike, When I try it gives me a #VALUE! error. Would you know why? MLS "Mike H" wrote: Hi, Try this ARRAY formula. See below on how to enter it =MAX(IF(B1:B8="FKG",C1:C8)) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. Mike "MLS" wrote: Hi, I am trying to get a max age from a range of cells, however the result is depending on the value in another cell. The range looks like this: ID BU Age 11348 JPU 44 12479 FKG 46 12487 LPS 27 14539 LPS 32 17583 FKG 24 39175 LPS 34 45867 JPU 42 74583 FKG 39 I need a formula that will give me the max age in the following way: FKG 46 JPU 44 LPS 34 Is there a way to do this without using a pivot table? Many thanks, MLS |
| All times are GMT +1. The time now is 09:33 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com