Roger Govier said:
I see what you mean with your example, and from Eric's subsequent
posting you had interpreted his request correctly.
However, all is not lost. It may not be what any of us would have
expected, but one can get it to work correctly.
No, bugs in the recalculation functionality appearing in features that
have been part of Excel for decades may not be what any of us would
have expected. Specific bugs are never expected (THAT's WHY WE CALL
THEM *BUGS*), though some bugs should be expected whenever Microsoft
combines thorough revision of software packages.
If one makes fundamental changes in the way this is done. Not
unreasonable in NEW workbooks, but a PITA for EXISTING workbooks
already using pre-XL2007 Data > Table.
Follow your steps through to the third line.
Before entering your array formula, highlight cells D1:E4
Insert tab>Data Table>no headers.
Here's another mistake you seem to make repeatedly. There is NOTHING
in the Insert tab labeled Data Table. There's Table in the Tables
section. I'll suppose that's what you mean. You may need to give some
thought to correcting your terminology since the only Data Table as a
single label in the entire ribbon is the one under the Data tab's Data
Tools section under the What If Analysis drop-down, which in this
instance is what the OP meant and what I immediately understood him to
mean. You might have picked up on the problem more quickly if you
weren't so confused on the precise terminology.
Everything in pushed down one row as excel creates it's own headers
of column1 and column2
Enter your array formula in E2, adjusted for the row increase.
=MAX(IF(A1:A20=D2,B1:B20))
Enter in GI your Average formula, again adjusted by 1 row
=AVERAGE(E3:E5)
Yes, the G1 formula now changes/recalcs whenever minimal recalc is
triggered.
Don't bother with Data>(DataTools) What If>Data Table (in fact it
will be greyed out).
And all you lose is the recalculation granularity provided by the
Automatic Except for Data Tables (oops, I was wrong, there's the other
instance of Data Tables in the same label in the ribbon). This setting
affects ONLY the recalculation of the pseudofunction TABLE(). For
particularly large data tables using particularly awkward formulas
calling volatile functions that should produce different results
infrequently, your suggestion would be a major step in the wrong
direction with regard to recalc performance.
And your suggestion only works with data tables that use only the
column input cell. There's no equivalent using new tables to hack
equivalents of data tables using row input cells with or without
column input cells. With columns A and B as before but everything else
cleared,
Enter {0,1,2} into E1:G1.
Enter the array formula =MAX(IF(A1:A15=D1,B1:B15)) in D2.
Select D1:G2 and run the ribbon command Data > (Data Tools)
What If Analysis > Data Table. Select D1 as the row input cell.
Enter the formula =AVERAGE(E2:G2) in I1.
Repeatedly press [F9] and watch the values in E2:G2 change while I1
remains the same.
This is contrived, and row-input-only data tables could always be
transposed to column only ones, but try row and column input data
tables such as the following.
Enter Month, Region, Sales in A1:C1, respectively.
Select A2:A49, type
=INT((ROW()+2)/4)
and press [Ctrl]+[Enter].
Select B2:B49, type
=LOOKUP(MOD(ROW(),4),{0;1;2;3},{"N";"S";"E";"W"})
and press [Ctrl]+[Enter].
Select C2:C49, type
=INT(100000*RAND())
and press [Ctrl]+[Enter].
Enter Month and Region into E1:F1, respectively.
Select I1:T1, type
=COLUMNS($I1:I1)
and press [Ctrl]+[Enter].
Enter N, S, E, W in H2:H5, respectively.
Enter the formula =DSUM(A1:C49,"Sales",E1:F2) in H1.
Enter the formula =SUM(I2:T5) in G1.
Select H1:T5 and run the Data > (Data Tools) What If Analysis > Data
Table. Select E2 as the row input cell and F2 as the column input
cell.
Repeatedly press [F9] and watch I2:T5 and G1.
Given tables' structured referencing, there's no way to hack this in
with tables. You could use Pivot Tables instead of data tables, but
pivot tables aren't included in recalculation like data tables are
(optionally). Pivot tables have to be refreshed manually or via event
handlers. Data tables are automatically recalculated/refreshed upon
minimal recalc when calculation is set to Automatic or upon full
recalc, [Alt]+[Ctrl]+[F9], when calculation is set otherwise.
Spin this as much as you want, this is a bug and a reduction in
functionality. If you have no clue how this feature may have been used
in previous versions, you may not see any reason to miss it. Others
may have different experiences.
4, respectively.