Solved Return month and year for 3 highest values in chart.

Microsoft Excel 2010 - complete product...
September 18, 2015 at 23:50:42
Specs: Windows 10
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


See More: Return month and year for 3 highest values in chart.

Report •

#1
September 19, 2015 at 09:53:50
✔ Best Answer
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-2014

Enter 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 and must be 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

http://www.skeptic.com/


Report •

#2
September 20, 2015 at 11:42:51
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!


Report •

#3
September 20, 2015 at 18:31:50
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 value you entered into cells A19, A20 & A21. the Totals Section

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
September 20, 2015 at 20:28:27
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.


Report •

#5
September 21, 2015 at 07:08:33
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

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#6
September 21, 2015 at 07:42:31
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

http://www.skeptic.com/


Report •

#7
September 21, 2015 at 15:12:32
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))))


Report •

Ask Question