[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Excel 2010 - Formula to show Max of values

Posted on 2016-08-25
11
Medium Priority
?
76 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 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
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 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 33

Expert Comment

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

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

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 …
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

649 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