We help IT Professionals succeed at work.

# Excel 2010 - Formula to show Max of values

on
Dear Experts,

Could you please have a look to the example in the attached file, basically there is a sheet named 'ProducedQty':

from where I would need an Excel formula in the B column of sheet 'MaxProducedQty', which shows the Max values per Products:

Actually it has been marked with green color which values should be shown by that formula

FormulaMax.xlsx
Comment
Watch Question

## View Solutions Only

Microsoft Excel Expert
Top Expert 2014
Commented:

formula used and entered with Control Shift Enter

=MAX(IF(ProducedQty!A:A=MaxProducedQty!A2,ProducedQty!B:B))  dragged down
FormulaMax.xlsx
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
OR you may try this Regular Formula (doesn't require Ctrl+Shift+Enter).......

In B2
``````=MAX(INDEX((ProducedQty!\$A\$2:\$A\$10=A2)*ProducedQty!\$B\$2:\$B\$10,))
``````
and copy down.
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
@Professor
I think that there would be a performance issue with the condition IF(ProducedQty!A:A=MaxProducedQty!A2 because the condition is being checked for the whole column A which is not required. So it would be better to include a max possible range in the conditional statement.
What do you say?
Microsoft Excel Expert
Top Expert 2014

Commented:
@Neeraj,

yes, i just shot an answer,  the best way of course will be using the Excel tables of its nature of dynamic range.

so, this time in the Table format
FormulaMax.xlsx
IT consultant

Commented:
Thanks for both idea
Microsoft Excel Expert
Top Expert 2014

Commented:
Cheers
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome. Glad we could help.
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
@Professor
Yes. That is the neat solution and good idea to have data converted into an excel table so the formula would be self expandable. +1
Microsoft Excel Expert
Top Expert 2014

Commented:
thanks Neeraj
Finance Analyst

Commented:
No formulas required, you could just use a Pivot Table and set the Data Value field to Max.

Set Data list to Table as suggested and then Pivot covers new data and new products are added to the list automatically.

See attached.
FormulaMax.xlsx
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
Good approach Rob!