Maximum Sales

[Sandra]

I have a sheet with year column, month column, sales_1,
sales_2, sales_3, etc. (Sales_1 being sales for
location1, Sales_2 being sales for location 2, etc.)

I want to show which year has the maximum sales for June
for a particular location. Which year has the maximum
sales for July for a particular location. And each other
month.

Help!!

 

[Harald Staff]

Pivot Table !!
(Data menu)

(!!)

HTH. Best wishes Harald

 

[Domenic]

Assuming Column A contains the year, Column B contains the month, and
C2:E10 contains your data...

Labels...

A1: Year
B1: Month
C1:E1 Sales_1, Sales_2, Sales_3

Formulas...

I2, Maximum Sales:

=MAX(IF(B2:B10=G2,INDEX(C2:E10,0,MATCH(H2,C1:E1,0))))
entered using CTRL+SHIFT+ENTER

J2, Year:

=INDEX(A2:A10,MATCH(1,(B2:B10=G2)*(INDEX(C2:E10,0,MATCH(H2,C1:E1,0))=I2),
0))
entered using CTRL+SHIFT+ENTER

....where G2 contains the month of interest and H2 contains the sales
location of interest. Add sales locations and adjust the range
accordingly.

Hope this helps!

 

[Sandra]

Thanks for replying. I did a pivot table. But there are
14 years of 12 months for 14 locations. My boss wants a
report (on paper) that shows one location and each
highest month with the year listed, something like this:
Chicago Jan Feb March
1992 6,000,000 2004 2,000,000 1995 7,000,000

I can't figure out how to show every month with its
ACCOMPANYING YEAR and without the other numbers. It
seems to be 3 dimensional.

I will appreciate any ideas you have.

 

[Sandra]

Thank you for replying.

I'm afraid I'm stumped right from the beginning. How can
B2:B10 (which is several rows) = G2 (which is only 1
cell)? I took this part of the formula out by itself,
and it returned "false". What am I missing?

 

[Domenic]

I understand from your other post that you're looking for a solution
other than the one I offered. As Harald pointed out, Pivottables may be
the way to go. For help, have a look at the following:

http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.cpearson.com/excel/pivots.htm

 

[Sandra]

No, I'm looking for ANY way to solve it. I am VERY
interested in your solution. I was hoping you could
clarify it for me. Please explain how several rows can
equal 1 cell. I know there is something I am missing,
and I want to learn.

Thank you!!!!

 

[Domenic]

Please explain how several rows can
equal 1 cell.

That expression works within the context of the array formula.

I am VERY
interested in your solution.

Having taken a second look at it, it appears that my solution may not be
appropriate, even if adapted to suit your requirements as I now
understand them.

For example, if we take the month of June for one of your locations,
there may be two years with the same amount equalling the maximum for
the month.

In this case, the formula would return the first occurrence. So if the
maximum for June is, let's say, 6,000,000 and both 2002 and 2004 have
that same maximum amount, the formula would return 2002 if it came first
in your list or 2004 if it came first.

I suppose that if you're interesting in returning the latest date, and
you sort your list by year in descending order, it "might" work.

If you're still interested in my solution, let me know and I'll be happy
to provide it.

 

[Harald Staff]

Ouch! Agreed, not easy. I'll post back if inspiration strikes.

Best wishes Harald

 

[Sandra]

Yes, I AM interested. Probably the numbers won't ever be
the same for two months. I will have numbers down to the
nearest dollar (maybe even cents), and it will be a large
coincidence if that occurs.

Thank you!

 

[Domenic]

Okay, hopefully this is what you're looking for:

Sheet 2
---------

1) First row contains your headers
2) Column A contains the year
3) Column B contains the month
4) Column C, D, and E contain your sales figures for each location (you
can add new locations and adjust the formula to suit your actual table)

So we'll have something like this...

Year.....Month.....Sales_1.....Sales_2.....Sales_3
2002.....May.....800.....150.....170
2003.....June.....300.....900.....500
2004.....June.....200.....450.....600

Sheet 1
---------

First row contains the months (for simplicity we'll work with January,
February, and March only):

B1: enter January

D1: enter February

F1: enter March

Column A contains locations starting at A2:

Sales_1
Sales_2
Sales_3
etc.

B2, copied down, and copied to Columns D and F:

=INDEX(Sheet2!$A$2:$A$10,MATCH(1,(Sheet2!$B$2:$B$10=B$1)*(INDEX(Sheet2!$C
$2:$E$10,0,MATCH($A2,Sheet2!$C$1:$E$1,0))=C2),0))

....enter the formula using CTRL+SHIFT+ENTER, and not just ENTER

C2, copied down, and copied to Columns E and G:

=MAX(IF(Sheet2!$B$2:$B$10=B$1,INDEX(Sheet2!$C$2:$E$10,0,MATCH($A2,Sheet2!
$C$1:$E$1,0))))

....enter the formula using CTRL+SHIFT+ENTER, and not just ENTER

 

[Sandra]

What is with the [Control] [Shift] [Enter]?

-----Original Message-----
Okay, hopefully this is what you're looking for:

Sheet 2
---------

1) First row contains your headers
2) Column A contains the year
3) Column B contains the month
4) Column C, D, and E contain your sales figures for each location (you
can add new locations and adjust the formula to suit your actual table)

So we'll have something like this...

Year.....Month.....Sales_1.....Sales_2.....Sales_3
2002.....May.....800.....150.....170
2003.....June.....300.....900.....500
2004.....June.....200.....450.....600

Sheet 1
---------

First row contains the months (for simplicity we'll work with January,
February, and March only):

B1: enter January

D1: enter February

F1: enter March

Column A contains locations starting at A2:

Sales_1
Sales_2
Sales_3
etc.

B2, copied down, and copied to Columns D and F:

=INDEX(Sheet2!$A$2:$A$10,MATCH(1,(Sheet2!$B$2:$B$10=B$1)* (INDEX(Sheet2!$C
$2:$E$10,0,MATCH($A2,Sheet2!$C$1:$E$1,0))=C2),0))

....enter the formula using CTRL+SHIFT+ENTER, and not just ENTER

C2, copied down, and copied to Columns E and G:

=MAX(IF(Sheet2!$B$2:$B$10=B$1,INDEX(Sheet2! $C$2:$E$10,0,MATCH($A2,Sheet2!
$C$1:$E$1,0))))

....enter the formula using CTRL+SHIFT+ENTER, and not just ENTER


.

 

[Domenic]

What is with the [Control] [Shift] [Enter]?

Unlike other formulas, array formulas are entered using
CONTROL+SHIFT+ENTER.

For a more detailed explanation, see the help menu under "About array
formulas and how to enter them".

Have you been able to get the formulas to work?

 

[Sandra]

Thanks, I need to study up on arrays. Yes, the formulas
worked. In the Month column, the formulas returned #N/A.
Is that what it was supposed to do? In the columns
beside each month it returned 0 except for the maximum
sales figure for the year. Do you have any advice as to
how to include the year in the output, because my problem
is how to report the YEAR that that had the highest sales
for each MONTH for a particular location. Each location
can have its own report.

Thanks again! I look forward to hearing from you.

-----Original Message-----

What is with the [Control] [Shift] [Enter]?

Unlike other formulas, array formulas are entered using
CONTROL+SHIFT+ENTER.

For a more detailed explanation, see the help menu under "About array
formulas and how to enter them".

Have you been able to get the formulas to work?
.

 

[Domenic]

It looks like you may not have entered the formulas using
CONTROL+SHIFT+ENTER. You need to make sure that when you enter the
formula, you press CONTROL+SHIFT+ENTER instead of just ENTER (hold the
CONTROL and SHIFT keys, then while those keys are pressed, hit the ENTER
key). Once the formula is entered this way, Excel will place braces
around the formula like this...

{=INDEX(Sheet2!$A$2:$A$10,MATCH(1,(Sheet2!$B$2:$B$10=B$1)*(INDEX(Sheet2!$
C$2:$E$10,0,MATCH($A2,Sheet2!$C$1:$E$1,0))=C2),0))}

According to the information you provided, this is the sample table I'm
working with on Sheet 2...

Year.....Month.....Sales_1......Sales_2.....Sales_3
2002.....May.....800.....150.....170
2002.....June.....300.....900.....500
2003.....June.....800.....450.....600

Here's the resulting table I have on Sheet 1, using the formulas I
provided...

......May.....| |.....June.....| |
Sales_1.....2002.....800.....2003.....800
Sales_2.....2002.....150.....2002.....900
Sales_3..... 2002.....170.....2003.....600

Does this help?

 

[Sandra]

Yeaa!!!! It works. I can't believe I was so dense. I
kept reading control SHIFT enter. But I kept pressing
control SPACEBAR enter.
I have now studied and understand arrays pretty well. I
have studied and understand MATCH pretty well. What I
don't understand is why in the first formula you use "*".
Thanks for opening a whole new method for me.
Please explain the times symbol.

 

[Domenic]

What I
don't understand is why in the first formula you use "*".
Please explain the times symbol.

Let's take the following example...

A 1 Red
B 2 Blue
C 3 Green
A 4 Purple
E 5 Yellow

If we want to return the value in Column C when the corresponding value
in Column A is "A" and Column B is "4" we would have the following
formula...

=INDEX(C1:C5,MATCH(1,(A1:A5="A")*(B1:B5=4),0))
entered using CTRL+SHIFT+ENTER

Conditionals like...

A1:A5="A"

....return an array of logical values like...

{TRUE;FALSE;FALSE;...}

So if we take a look at...

(A1:A5="A")*(B1:B5=4)

....the following arrays of values are returned...

{TRUE;FALSE;FALSE;TRUE;FALSE}*{FALSE;FALSE;FALSE;TRUE;FALSE}

Since the numerical equivalent of TRUE/FALSE is 1/0, respectively, the
two arrays multiplied (multiplication is represented by an asterisk * in
Excel) return the following array of values...

{0;0;0;1;0}

The MATCH function then matches the lookup value (in this case "1")
against this array returning the number 4 which specifies the position
within the array. The INDEX function then uses this as a reference to
return the value in Column C, in this case "Purple".

 

[Sandra]

You are a wonderful teacher! Makes perfect sense. Thank
you! Do you teach for a living?

 

[Domenic]

You are a wonderful teacher! Makes perfect sense. Thank
you!

You're very welcome!

Do you teach for a living?

Not at all. I'm just an Excel enthusiast. Believe it or not, I find it
very interesting.

Take care!