Skip to main content

Find Largest Value in Excel


MAXIFS

One of the new Office 365 functions added in February 2016 is the MAXIFS function. This function, which is similar to SUMIFS, finds the largest value that meets one or more criteria: You can either hard-code the criterion as in row 7 below or point to cells as in row 9. A similar MINIFS function finds the smallest value that meets one or more criteria.

Amounts appears in D12:D125. To get the Largest, use =MAX($D$12:$D$125). To get the 2nd Largest, use =LARGE($D$12:$D$125,2). To get the 3rd Largest, use =LARGE($D$12:$D$125,3). To get the 3rd Smallest, use =SMALL($D$12:$D$125,3). To get the 2nd Smallest, use =SMALL($D$12:$D$125,2). To get the Smallest, use =MIN($D$12:$D$125). To get the Smallest Widget, use =MINIFS($D$12:$D$125,$B$12:$B$125,"Widget"), To get the Largest with 2 Criteria entered in B8:C8, use =MAXIFS($D$12:$D$125,$B$12:$B$125,B8,$C$12:$C$125,C8)

While most people have probably heard of MAX and MIN, but how do you find the second largest value? Use LARGE (rows 2 and 3) or SMALL (rows 4 and 5).

What if you need to sum the top seven values that meet criteria? The orange box below shows how to solve with the new Dynamic Arrays. The green box is the Ctrl+Shift+Enter formula required previously.

Before Dynamic Arrays, you would use: '{=SUM( AGGREGATE(14,4,($B$13:$B$126=$B$2) *($C$13:$C$126=$C$2) *($D$13:$D$126), ROW(INDIRECT("1:"&D2))))}. With Dynamic Arrays, the formula is =SUM(LARGE(FILTER(D13:D126,(B13:B126=B2)*(C13:C126=C21)),SEQUENCE(D2)))

Labels

Labels:

Comments

Post a Comment

Popular posts from this blog

Import CSV In Power BI

  Import CSV file Click on Get Data à More à File option and select Text/CSV . Navigate to the CSV file which needs to be imported FL_insurance_sample.csv . Select the file and click on Open. FL_insurance_sampleDownload In the CSV window on top we have 3 dropdowns, preview of data and data load options.  Select Load and it will load to power query editor window. In the Power query editor the CSV file is loaded as a Queries . In power query editor we can edit, clean and transform the file as required. 3 Dropdowns and Data Load File Origin – Type of file origin. By default its 1252 Wester European (Windows). It’s the file type as per OS and region and country. Delimiter – Delimiter for column separation. By default it detects the delimiter from data , If the delimiter is not from the default options then we can select custom delimiter. Data Type Detection – By default it detects data types of columns based on top 200 rows, we can select entire data or do not detect data type ...
Post a Comment
Read more

Protect Formula Cells

The use of worksheet protection in Excel is a little strange. Using the steps below, you can quickly protect just the formula cells in your worksheet. It seems unusual, but all 16 billion cells on a worksheet start out with their Locked property set to True. You need to unlock all of the cells first: Select all cells by using the icon above and to the left of cell A1. Press  Ctrl+1  (that is the number 1) to open the Format Cells dialog. In the Format Cells dialog, go to the Protection tab. Uncheck Locked. Click OK. While all cells are still selected, select Home, Find & Select, Formulas. At this point, only the formula cells are selected. Press  Ctrl+1  again to display the Format Cells dialog. On the Protection tab, choose Locked to lock all of the formula cells. Locking cells does nothing until you protect the worksheet. On the Review tab, choose Protect Sheet. In the Protect Sheet dialog, choose if you want people to be able to select your formula cells or no...
Post a Comment
Read more

20 Power BI Dax Measures

Power bi 20 dax measures 20 DAX (Data Analysis Expressions) measures in Power BI with examples: Total Sales: scss Copy code Total Sales = SUM (Sales[Amount]) Average Sales Price: scss Copy code Avg Sales Price = AVERAGE (Sales[Amount]) Total Units Sold: mathematica Copy code Total Units Sold = SUM ( Sales [ Quantity ] ) Total Customers: scss Copy code Total Customers = COUNTROWS (Customer) Total Products: mathematica Copy code Total Products = COUNTROWS ( Product ) Maximum Sales Amount: scss Copy code Max Sales Amount = MAX (Sales[Amount]) Minimum Sales Amount: scss Copy code Min Sales Amount = MIN (Sales[Amount]) Sales Growth Percentage: mathematica Copy code Sales Growth % = ( Total Sales - [ Total Sales Last Year ] ) / [ Total Sales Last Year ] Total Profit: scss Copy code Total Profit = SUM (Sales[Profit]) Total Orders: scss Copy code Total Orders = COUNTROWS (Orders) Total Customers with Sales: css Copy code Total Customers with Sales = COUNTROWS( FILTER ...
Post a Comment
Read more