?
Solved

Excel 2010 - Formula to show Max of values

Posted on 2016-08-25
11
Medium Priority
?
73 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 26

Assisted Solution

by:ProfessorJimJam
ProfessorJimJam earned 1000 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 32

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 1000 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 32

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 26

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 26

Expert Comment

by:ProfessorJimJam
ID: 41769928
Cheers
0
 
LVL 32

Expert Comment

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

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 26

Expert Comment

by:ProfessorJimJam
ID: 41770014
thanks Neeraj
0
 
LVL 33

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 32

Expert Comment

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

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
New style of hardware planning for Microsoft Exchange server.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
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…

765 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