Solved

Excel 2010 - Formula to show Max of values

Posted on 2016-08-25
11
63 Views
Last Modified: 2016-08-25
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
0
Comment
Question by:csehz
11 Comments
 
LVL 25

Assisted Solution

by:ProfessorJimJam
ProfessorJimJam earned 250 total points
ID: 41769899
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
 
LVL 29

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 250 total points
ID: 41769918
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
 
LVL 29

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41769919
@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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 41769926
@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
 
LVL 1

Author Closing Comment

by:csehz
ID: 41769927
Thanks for both idea
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 41769928
Cheers
0
 
LVL 29

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41769935
You're welcome. Glad we could help.
0
 
LVL 29

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41769937
@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
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 41770014
thanks Neeraj
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 41770143
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
 
LVL 29

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41770453
Good approach Rob!
1

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question