Tricky Array Question

[ExcelNewb09]

If I have the following set up:

Book1
	C	D	E	F
3	State	Store	Jan'08	Jan'09
4	Ohio	1	838.24	523.69
5	Ohio	2	400.51	627.29
6	Texas	3	823.03	219.65
7	Texas	4	425.84	376.30
8	California	5	818.26	299.65
9	California	6	98.37	301.73
10	Oregon	7	453.04	521.42
11	Oregon	8	93.79	345.68
Sheet1

is it possible to find the STATE which had the least or negative growth (min).

Is the same thing possible if I have This set up:?

Book1
	B	C	D	E
3	Date	State	Store	Sales
4	Jan'08	Ohio	1	838.24
5	Jan'08	Ohio	2	400.51
6	Jan'08	Texas	3	823.03
7	Jan'08	Texas	4	425.84
8	Jan'08	California	5	818.26
9	Jan'08	California	6	98.37
10	Jan'08	Oregon	7	453.04
11	Jan'08	Oregon	8	93.79
12	Jan'09	Ohio	1	523.69
13	Jan'09	Ohio	2	627.29
14	Jan'09	Texas	3	219.65
15	Jan'09	Texas	4	376.30
16	Jan'09	California	5	299.65
17	Jan'09	California	6	301.73
18	Jan'09	Oregon	7	521.42
19	Jan'09	Oregon	8	345.68
Sheet1

 

[barry houdini]

I assume that you want the overall result for each state, taking into account all of the stores?

This formula should give the State with the lowest growth (Texas?) from your first setup

=INDEX(C$4:C$11,MATCH(MIN(SUMIF(C4:C11,C4:C11,F4:F11)/SUMIF(C4:C11,C4:C11,E4:E11)),SUMIF(C4:C11,C4:C11,F4:F11)/SUMIF(C4:C11,C4:C11,E4:E11),0))

confirmed with CTRL+SHIFT+ENTER

The second setup will be less straightforward, I think.....

 

[ExcelNewb09]

=MIN(SUMPRODUCT(($D$4:$D$19=1)*($E$4:$E$19=$E$4:$E$19)*$G$4:$G$19)-SUMPRODUCT(($D$4:$D$19=2)*($E$4:$E$19=$E$4:$E$19)*$G$4:$G$19))

This is close as I could get with the second method;
but it looks like its completely ignoring the min; and basically
summing by date, then subtracting the two.

Any ideas?

 

[Peter_SSs]

How about using a PivotTable? A couple of advantages..

1. Excel does most of the hard work.

2. This might suit better if two (or more) states have equal lowest growth.

See if these would be suitable. You could use formula(s) to extract the name(s) of the relevant states from the PivotTables if needed.

First setup - I have added a 'Growth' column to the table then used it in the PivotTable.

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	State*	*Store*	Jan*'08	Jan*'09	Growth		Sum of Growth
2	Ohio	1	838.24	523.69	-314.55		*State*	Total
3	Ohio	2	400.51	627.29	226.78		California	-315.25
4	Texas	3	823.03	219.65	-603.38		Ohio	-87.77
5	Texas	4	425.84	376.30	-49.54		Oregon	320.27
6	California	5	818.26	299.65	-518.61		Texas	-652.92
7	California	6	98.37	301.73	203.36		Grand Total	-735.67
8	Oregon	7	453.04	521.42	68.38
9	Oregon	8	93.79	345.68	251.89
10
Min Growth 1

Second setup - I have removed the Grand Totals from the rows and added a calculated item column.

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Date	State*	Store	Sales		Sum of Sales	Date
2	Jan*'08	Ohio	1	838.24		State*	Jan*'08	Jan*'09	Growth
3	Jan*'08	Ohio	2	400.51		California	916.63	601.38	-315.25
4	Jan*'08	Texas	3	823.03		Ohio	1238.75	1150.98	-87.77
5	Jan*'08	Texas	4	425.84		Oregon	546.83	867.1	320.27
6	Jan*'08	California	5	818.26		Texas	1248.87	595.95	-652.92
7	Jan*'08	California	6	98.37		Grand Total	3951.08	3215.41	-735.67
8	Jan*'08	Oregon	7	453.04
9	Jan*'08	Oregon	8	93.79
10	Jan*'09	Ohio	1	523.69
11	Jan*'09	Ohio	2	627.29
12	Jan*'09	Texas	3	219.65
13	Jan*'09	Texas	4	376.30
14	Jan*'09	California	5	299.65
15	Jan*'09	California	6	301.73
16	Jan*'09	Oregon	7	521.42
17	Jan*'09	Oregon	8	345.68
18
Min Growth 2