Excel 2010 - Formula to show Max of values

Dear Experts,

Could you please have a look to the example in the attached file, basically there is a sheet named 'ProducedQty':
The sheet from where the values should be took
from where I would need an Excel formula in the B column of sheet 'MaxProducedQty', which shows the Max values per Products:
The target values
Actually it has been marked with green color which values should be shown by that formula

Thanks in advance
FormulaMax.xlsx
LVL 1
csehzIT consultantAsked:
Who is Participating?
 
Subodh Tiwari (Neeraj)Connect With a Mentor Excel & VBA ExpertCommented:
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,))

Open in new window

and copy down.
0
 
ProfessorJimJamConnect With a Mentor Commented:
please see attached file.

formula used and entered with Control Shift Enter

=MAX(IF(ProducedQty!A:A=MaxProducedQty!A2,ProducedQty!B:B))  dragged down
FormulaMax.xlsx
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
@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?
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
ProfessorJimJamCommented:
@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
1
 
csehzIT consultantAuthor Commented:
Thanks for both idea
0
 
ProfessorJimJamCommented:
Cheers
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome. Glad we could help.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
@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
1
 
ProfessorJimJamCommented:
thanks Neeraj
0
 
Rob HensonFinance AnalystCommented:
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
1
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Good approach Rob!
1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.