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

Comments

Popular posts from this blog

Send Bulk Email from Excel for Outlook

  Download the File from below Link https://drive.google.com/file/d/1tcb4lzNFgEfDKsvQqCW05sgoiGFEhqcK/view?usp=sharing Instructions are given in the image below. Save the File as Excel Macro - Enabled workbook (.xlsm) Use this file to send bulk emails at a time i personally have sent more than 2000 bulk emails at a time. Error may occur if email id typed contains space etc. Only one email id one cell.

Pivot table for Each item in Report Filter - Hidden Feature

The pivot table below shows products across the top and customers down the side. The pivot table is sorted so the largest customers are at the top. The Sales Rep field is in the report filter. If you open the Rep dropdown, you can filter the data to any one sales rep. This is a great way to create a report for each sales rep. Each report summarizes the revenue from a particular salesperson‘s customers, with the biggest customers at the top. And you get to see the split between the various products. The Excel team has hidden a feature called Show Report Filter Pages. Select any pivot table that has a field in the report filter. Go to the Analyze tab (or the Options tab in Excel 2007/2010). On the far left side is the large Options button. Next to the large Options button is a tiny dropdown arrow. Click this dropdown and choose Show Report Filter Pages.... Excel asks which field you want to use. Select the one you want (in this case the only one available) and click OK. Over the next few...

Turn Data Sideways

Someone built this lookup table sideways, stretching across C1:N2. I realize that I could use HLOOKUP instead of VLOOKUP, but I prefer to turn the data back to a vertical orientation. Copy C1:N2. Right-click in A4 and choose the Transpose option under the Paste Options. Transpose is the fancy Excel word for “turn the data sideways.” I transpose a lot. But I use  Alt+E ,  S ,  E ,  Enter  to transpose instead of the right-click. There is a problem, though. Transpose is a one-time snapshot of the data. What if you have formulas in the horizontal data? Is there a way to transpose with a formula? The first way is a bit bizarre. If you are trying to transpose 12 horizontal cells, you need to select 12 vertical cells in a single selection. Start typing a formula such as  =TRANSPOSE(C2:N2)  in the active cell but do not press Enter. Instead, hold down  Ctrl+Shift  and then press  Enter . This puts a single array formula in the selected cells. T...