Nested Statistical If Statements with Max in Excel

Use Nested Statistical If Statements with Max in Excel. Technique used to display text or do calculation only if cell value is maximum or minimum.

How To Use Nested Statistical If Statements with Max in Excel. It is a very useful technique which is used to display text or do calculation only if cell’s value is maximum or minimum from given range of cells in active sheet. In that case of logical testing, it will contains a nested statistical function like MAX or MIN.

For example, if a salesmen total sales is maximum than all others in total sales column then print a message “Best Salesmen” in remark field. If the logical test is false, then print a blank message. When we type the formula for logical testing, it should be readable that we can use two types of cell address or references in this formula. The first reference is salesmen total sales is displays in relative cell reference while second reference represent the range of total sales of all salesmen’s in absolute cell reference.

Nested Statistical If Statements with Max:

Max(): It’s is very important statistical function in MS Excel. This function is specially used to displays the maximum or largest value from the given range of cells.

Must Read: Excel Formula RANK: Tips to Get Ranking Position

Note: When we copy this formula in range c3:c6, the second reference of total salesmen’s sales will not be changed due to using absolute cell reference, it will be constant.

Step 1. Create the following sheet in excel.

Nested-If-Statements-with-Max-Function

Step 2. Take cursor on cell C2 and type following formula in Remark column:  =IF (B2=MAX (B2:B6),”Best Salesmen”, ” “) then press F4 function key to convert the range B2:B6 from Relative to Absolute cell reference, it looks like given below:
=IF (B2=MAX ($B$2:$B$6),”Best Salesmen”, ” “)  , then press enter.

Nested-If-Statements-with-Max-Function

Step 3. Now drag this formula up to cell C7 or you can also select the range from C2:C7 then press ALT+D shortcut key to view the result.

Nested-If-Statements-with-Max-Result

Must Read: MS Excel : Vlookup Function with a Spreadsheet

I hope you like this Nested Statistical If Statements with Max guide. Give your important suggestion in the comment box and also share this article with your friends, office staff members and many more to show your knowledge in excel. Thanks.

Leave a Reply

Thanks to leave a comment. Please note that all comments are moderated according to our comment policy and your email address will NOT be published. Please Do NOT use keywords in the name field. Lets have a meaningful conversation.