# Tricky Array Question

#### ExcelNewb09

##### Board Regular
If I have the following set up:
Book1
CDEF
3StateStoreJan'08Jan'09
4Ohio1838.24523.69
5Ohio2400.51627.29
6Texas3823.03219.65
7Texas4425.84376.30
8California5818.26299.65
9California698.37301.73
10Oregon7453.04521.42
11Oregon893.79345.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
BCDE
3DateStateStoreSales
4Jan'08Ohio1838.24
5Jan'08Ohio2400.51
6Jan'08Texas3823.03
7Jan'08Texas4425.84
8Jan'08California5818.26
9Jan'08California698.37
10Jan'08Oregon7453.04
11Jan'08Oregon893.79
12Jan'09Ohio1523.69
13Jan'09Ohio2627.29
14Jan'09Texas3219.65
15Jan'09Texas4376.30
16Jan'09California5299.65
17Jan'09California6301.73
18Jan'09Oregon7521.42
19Jan'09Oregon8345.68
Sheet1

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

#### barry houdini

##### MrExcel MVP
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

##### Board Regular
Code:
``=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

##### MrExcel MVP, Moderator

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
ABCDEFGHI
1State**Store*Jan*'08Jan*'09GrowthSum of Growth
2Ohio1838.24523.69-314.55*State*Total
3Ohio2400.51627.29226.78California-315.25
4Texas3823.03219.65-603.38Ohio-87.77
5Texas4425.84376.30-49.54Oregon320.27
6California5818.26299.65-518.61Texas-652.92
7California698.37301.73203.36Grand Total-735.67
8Oregon7453.04521.4268.38
9Oregon893.79345.68251.89
10
Min Growth 1

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

Excel Workbook
ABCDEFGHI
1DateState*StoreSalesSum of SalesDate
2Jan*'08Ohio1838.24State*Jan*'08Jan*'09Growth
3Jan*'08Ohio2400.51California916.63601.38-315.25
4Jan*'08Texas3823.03Ohio1238.751150.98-87.77
5Jan*'08Texas4425.84Oregon546.83867.1320.27
6Jan*'08California5818.26Texas1248.87595.95-652.92
7Jan*'08California698.37Grand Total3951.083215.41-735.67
8Jan*'08Oregon7453.04
9Jan*'08Oregon893.79
10Jan*'09Ohio1523.69
11Jan*'09Ohio2627.29
12Jan*'09Texas3219.65
13Jan*'09Texas4376.30
14Jan*'09California5299.65
15Jan*'09California6301.73
16Jan*'09Oregon7521.42
17Jan*'09Oregon8345.68
18
Min Growth 2

Replies
1
Views
175
Replies
4
Views
327
Replies
2
Views
364
Replies
1
Views
326
Replies
2
Views
294

1,191,703
Messages
5,988,179
Members
440,136
Latest member
dandanfielding

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back