Is there a formula that will show me the maximum value in an array based on a certain criteria? I have a list of parts and the teams that repair them. I want to find out the part in each team that has the highest dollar value. Is there a way to do that without needing to go through each part? I tried a DMAX formula, but apparently did something wrong because it evaluated to "VALUE". The formula I typed was =DMAX('Monthly Demand'!I2:K669, 'Monthly Demand'!I2:I669, 'Monthly Demand'!J2:J669 = "XPT") -- the first was my array, the second was the column with the dollar values of the parts, and the third is the column containing the team name.
Max Value Based on Criteria (1 Viewer)
- Thread starter LadyDi
- Start date
BlueIshDan
☠
- Local time
- Today, 14:14
- Joined
- May 15, 2014
- Messages
- 1,122
If I had the time I would write you a custom function to insert into your sheet.
With that said, I know you know some vba, so I think it is something that you can push through
With that said, I know you know some vba, so I think it is something that you can push through
Rx_
Nothing In Moderation
- Local time
- Today, 11:14
- Joined
- Oct 22, 2009
- Messages
- 2,803
http://www.contextures.com/excelminmaxfunction.html
This is probably what you need.
This goes through the steps of All, 2 columns, and 3 columns.
The Array formula shown twards the end is typically faster than the straight formula if the spreadsheet has a lot of records (e.g. 10,000 or more).
This is probably what you need.
This goes through the steps of All, 2 columns, and 3 columns.
The Array formula shown twards the end is typically faster than the straight formula if the spreadsheet has a lot of records (e.g. 10,000 or more).
Rx_
Nothing In Moderation
- Local time
- Today, 11:14
- Joined
- Oct 22, 2009
- Messages
- 2,803
Thought it might. Thanks for the Thanks!
Be sure and consider marking the orginal post as Solved
It will help others searching for a solution too.
Don't hesitate to post a small example of your solution. I might be inclined to suggest an enhancement to it.
Be sure and consider marking the orginal post as Solved
It will help others searching for a solution too.
Don't hesitate to post a small example of your solution. I might be inclined to suggest an enhancement to it.
Okay, I'm a little confused. This worked yesterday, so I don't understand why I can't get it to work today. This is the formula I'm using : ={MAX(IF('Monthly Demand'!$K$1:$K$48330 = 'Executive Overview'!B51, 'Monthly Demand'!$I$1:$I$48330))}. I don't know if the problem is that column K contains formulas as well. If that is the problem, is there a way to compensate for that?
Rx_
Nothing In Moderation
- Local time
- Today, 11:14
- Joined
- Oct 22, 2009
- Messages
- 2,803
I am not confused, its just normal for me.
If there is a formula in part of the lookup array, it might be necessary to get the value of the cell rather than the formula.
On second thought, verify it is the formula in the field not an error in your formula.
Copy and Paste Value the column that has a formula - then alter your Max Value to use that value column.
This way you can validate if it is in fact the formula in one column causing it or not.
If there is a formula in part of the lookup array, it might be necessary to get the value of the cell rather than the formula.
On second thought, verify it is the formula in the field not an error in your formula.
Copy and Paste Value the column that has a formula - then alter your Max Value to use that value column.
This way you can validate if it is in fact the formula in one column causing it or not.
