?
Solved

Excel 2010 - Formula to show Max of values

Posted on 2016-08-25
11
Medium Priority
?
78 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 27

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 33

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 33

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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 27

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 27

Expert Comment

by:ProfessorJimJam
ID: 41769928
Cheers
0
 
LVL 33

Expert Comment

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

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 27

Expert Comment

by:ProfessorJimJam
ID: 41770014
thanks Neeraj
0
 
LVL 34

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 33

Expert Comment

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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Microsoft has changed the look and feel of Azure AD and Microsoft account sign-in pages so that you will have a more unified look and feel when moving between the two interfaces.
With its various features, Office 365 can not only help you with your day-to-day business tasks, it can also do wonders for your marketing campaign.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

850 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