If and MAX Statement

jwoolsey08 · Sep 15, 2011

Hey everyone,

I am currently taking an excel class to further my skills. This is a pretty simple question, but I am just not on everyone's level yet so I appreciate the help. This is my first post also.

I'm looking to use a IF and MAX statements to locate the largest number for each region, and have the statement read "HOT" next to the largest number because it is an hot item for the region. Below are my directions for the course.

a. The Hot Item column highlights the “hot” item in each region with the RED word “HOT”. An item is “hot” if its forecasted sales amount in column O is the largest in its region. Each region should have just one hot item. (Hint: use both the IF and MAX functions in column D).<o

></o

>

Please let me know if this is enough to describe it, once again any help is appreciated.
<link rel="File-List" href="file:///C:%5CDOCUME%7E1%5COvprAsst%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_filelist.xml"><link rel="Edit-Time-Data" href="file:///C:%5CDOCUME%7E1%5COvprAsst%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_editdata.mso"><link rel="themeData" href="file:///C:%5CDOCUME%7E1%5COvprAsst%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_themedata.thmx"><link rel="colorSchemeMapping" href="file:///C:%5CDOCUME%7E1%5COvprAsst%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_colorschememapping.xml"><style> <!-- /* Font Definitions */ @font-face {font-family:PMingLiU; panose-1:2 1 6 1 0 1 1 1 1 1; mso-font-alt:新細明體; mso-font-charset:136; mso-generic-font-family:auto; mso-font-format

ther; mso-font-pitch:variable; mso-font-signature:1 134742016 16 0 1048576 0;} @font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-charset:0; mso-generic-font-family:roman; mso-font-pitch:variable; mso-font-signature:-1610611985 1107304683 0 0 159 0;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4; mso-font-charset:0; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:-1610611985 1073750139 0 0 159 0;} @font-face {font-family:"\@PMingLiU"; panose-1:0 0 0 0 0 0 0 0 0 0; mso-font-charset:136; mso-generic-font-family:auto; mso-font-format

ther; mso-font-pitch:variable; mso-font-signature:1 134742016 16 0 1048576 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-unhide:no; mso-style-qformat:yes; mso-style-parent:""; margin-top:0in; margin-right:0in; margin-bottom:10.0pt; margin-left:0in; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-fareast-font-family:PMingLiU; mso-bidi-font-family:"Times New Roman"; mso-fareast-language:ZH-TW;} .MsoChpDefault {mso-style-type:export-only; mso-default-props:yes; font-size:10.0pt; mso-ansi-font-size:10.0pt; mso-bidi-font-size:10.0pt; mso-ascii-font-family:Calibri; mso-fareast-font-family:PMingLiU; mso-hansi-font-family:Calibri;} @page WordSection1 {size:8.5in 11.0in; margin:1.0in 1.0in 1.0in 1.0in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.WordSection1 {page:WordSection1;} --> </style>[FONT=&quot]<v:shapetype id="_x0000_t75" coordsize="21600,21600" o:spt="75" o

referrelative="t" path="m@4@5l@4@11@9@11@9@5xe" filled="f" stroked="f"> <v:stroke joinstyle="miter"> <v:formulas> <v:f eqn="if lineDrawn pixelLineWidth 0"> <v:f eqn="sum @0 1 0"> <v:f eqn="sum 0 0 @1"> <v:f eqn="prod @2 1 2"> <v:f eqn="prod @3 21600 pixelWidth"> <v:f eqn="prod @3 21600 pixelHeight"> <v:f eqn="sum @0 0 1"> <v:f eqn="prod @6 1 2"> <v:f eqn="prod @7 21600 pixelWidth"> <v:f eqn="sum @8 21600 0"> <v:f eqn="prod @7 21600 pixelHeight"> <v:f eqn="sum @10 21600 0"> </v:f> <v

ath o:extrusionok="f" gradientshapeok="t" o:connecttype="rect"> <o:lock v:ext="edit" aspectratio="t"> </o:lock><v:shape id="Picture_x0020_2" o:spid="_x0000_i1025" type="#_x0000_t75" style="width: 468pt; height: 122.25pt; visibility: visible;" o:bordertopcolor="#4f81bd" o:borderleftcolor="#4f81bd" o:borderbottomcolor="#4f81bd" o:borderrightcolor="#4f81bd"> <v:imagedata src="file:///C:%5CDOCUME%7E1%5COvprAsst%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_image001.png" o:title=""> <w:bordertop type="single" width="6"> <w:borderleft type="single" width="6"> <w:borderbottom type="single" width="6"> <w:borderright type="single" width="6"> </w:borderright></w:borderbottom></w:borderleft></w:bordertop></v:imagedata></v:shape></v

ath></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:formulas></v:stroke></v:shapetype>[/FONT]

Andrew Poulsom · Sep 15, 2011

Welcome to MrExcel.

Assuming regions are in column A and forecast sales are in column B, in C1 enter:

=IF(B1=MAX(IF(A$1:A$100=A1,B$1:B$100)),"HOT","")

and confirm with Ctrl+Shift+Enter. If entered correctly Excel will surround the array formula with curly braces. You can copy the formula down.

jwoolsey08 · Sep 15, 2011

I'll try that, thank you for the prompt reply.

If and MAX Statement

jwoolsey08

New Member

Excel Facts

Andrew Poulsom

MrExcel MVP

jwoolsey08

New Member

Similar threads

Forum statistics

Share this page

If and MAX Statement

jwoolsey08

New Member

Excel Facts

Andrew Poulsom

MrExcel MVP

jwoolsey08

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock