Microsoft Excel 2010 - complete product...

Cells B3:M3 contain Jan to Dec. Cells A4:A16 contain 2008 to 2020. Cells B4:M16 contain sales amounts. Using the =LARGE(array,k) and the =SMALL(array,k) formulas provides the 3 largest and 3 smallest sales amounts. What formula will show the corresponding MONTH and YEAR for each of these sales amounts?

Conditional formatting will disclose/highlight the LARGEST and SMALLEST sales amounts using MIN and MAX.

message edited by jmichels

Try this: With your Data like:

A B C D E F G H I J K L M 1) 2) 3) |Jan | Feb | Mar | Apr | May | Jun | JUL | AUG | SEP | OCT | NOV | DEC 4) 2008 | | | | | | | 100 | | | | | 5) 2009 | | | | | | | | | | | | 6) 2010 | | 200 | | | | | | | | | | 7) 2011 | | | | | | | | | | | | 8) 2012 | | | | | | | | | | | | 9) 2013 | | | | | | | | | | | | 10) 2014 | | | | 300 | | | | | | | | 11) 2015 | | | | | | | | | | | | 12) 2016 | | | | | | | | | | | | 13) 2017 | | | | | | | | | | | | 14) 2018 | | | | | | | | | | | | 15) 2019 | | | | | | | | | | | | 16) 2020 | | | | | | | | | | | | 17) 18) TOTAL| M/Y 19) 100 | JUL-2008 20) 200 | FEB-2010 21) 300 | APR-2014Enter your Target Totals starting in cell A19

Enter the formula in Cell B19, Drag down as many rows as needed.

Formula is an Array Formula andmustbe entered using:

CTRL-SHIFT-ENTER=IF(COUNTIF($B$4:$M$16,A19)=0,"",INDEX($B$3:$M$3&"-"&$A$4:$A$16,MAX(IF($B$4:$M$16=A19,ROW($B$4:$M$16)-ROW($B$4)+1)),MAX(IF($B$4:$M$16=A19,COLUMN($B$4:$M$16)-COLUMN($B$4)+1))))

It is very long, so you might want to Copy&Paste.

See how that works for you.

MIKE

Mike - you definitely are quite knowledgeable in scripting Excel formulas. Your formula, of course, worked flawlessly and provided EXACTLY the result needed.

Your formula was easily modified to show the MIN values too.

Thanks for your support!

Glad the formula worked for you, but I'm curious by what you mean

Your formula was easily modified to show the MIN values too.As written, it should have found

any valueyou entered into cells A19, A20 & A21. the Totals SectionMIKE

Cell A19 had this formula =LARGE($B$4:$M$16,1), Cell A20 had =LARGE($B$4:$M$16,2) and Cell A21 had =LARGE($B$4:$M$16,3). Your formula was placed in Cells B19, B20 and B21. Worked flawlessly!!! Cell A24 had this formula =SMALL($B$4:$M$10,1), Cell A25 had =SMALL($B$4:$M$10,2) and Cell A26 had =SMALL($B$4:$M$10,3).

Your formula was placed into the B24, B25 and B26 cells exchanging the MAX to MIN.

~~Not sure why, but when I convert MAX to MIN I get a VALUE error.~~

What type of Data are you entering in the B4:M16 matrix?

Could you post a sample?Also, would you post Your two versions of the formula,

I would like to see why my formula does not work like yours.EDIT ADDED:

Fixed the Value Error, but can see no difference between using MIN or MAX.

MIKE

message edited by mmcconaghy

Also, in case you need/want to, you can incorporate your SMALLL() & LARGE()

formulas into the main formula.Simply replace the reference to A19, A20, etc

with the appropriate SMALL() or LARGE() function like:=IF(COUNTIF($B$4:$M$16,

SMALL($B$4:$M$16,1))=0,"",INDEX($B$3:$M$3&"-"&$A$4:$A$16,MAX(IF($B$4:$M$16=SMALL($B$4:$M$16,1),ROW($B$4:$M$16)-ROW($B$4)+1)),MAX(IF($B$4:$M$16=SMALL($B$4:$M$16,1),COLUMN($B$4:$M$16)-COLUMN($B$4)+1))))And, as best as I can determine, there is no difference between using MAX or MIN in the

formula, you can even use them both in the formula and it does not make a difference.MIKE

The data in the B4:M16 matrix are $ales amounts in dollars and cents. The three LARGEst $ales amounts are shown in Cells A19 to A21 using LARGE(). The Month-Year they occur are shown in Cells B19 to B21 using your formula.

The three SMALLest $ales amounts are shown in Cells A24 to A26 using SMALL(). The Month-Year they occur are shown in Cells B24 to B26 using your formula with MIN instead of MAX. For Example: =IF(COUNTIF($B$4:$M$16,A24)=0,"",INDEX($B$3:$M$3&"-"&$A$4:$A$16,MIN(IF($B$4:$M$16=A24,ROW($B$4:$M$16)-ROW($B$4)+1)),MIN(IF($B$4:$M$16=A24,COLUMN($B$4:$M$16)-COLUMN($B$4)+1))))

Ask Your Question

Weekly Poll