SUBTOTAL, INDIRECT and ADDRESS functions

[A.Eglauer]

I have data in C21:E86 which represent test results on certain days.
The date of the test is in column C, the machine name is in column D
and the test result is in column E. There are 3 different types of
machines and the dates range over several months. What I wish to do
is calculate an average test result for the most recent week (I don't
mean the last 7 days, I mean the average results from last Saturday to
this Friday). I can do that, but when I filter the results based on
the type of machine, the average still calculates for all the machines
in the last week. I am using the SUBTOTAL, INDIRECT and ADDRESS
functions to select the range of results from last Saturday to this
Friday.

It seems the INDIRECT pulls the actual values from the results, so
filtering the data makes no difference on the SUBTOTAL function.

The formula I have in cell E14 (the calculation is above all the data)
is:
=SUBTOTAL(1,(INDIRECT(ADDRESS(MATCH($K$10,$C$21:$C$86,1)+20,COLUMN())):INDIRECT(ADDRESS(MATCH($K$10-7,$C$21:$C$86)+21,COLUMN()))))

In $K$10 I have calculated "this Friday's date" as follows:
=MAX($C$21:$C$86)+6-WEEKDAY(MAX($C$21:$C$86))

Can anyone help out? I'd appreciate a response to the e-mail address
provided as well, if it's not too much trouble.

Thanks,
Armand

 

[Paul Corrado]

A,

You could create an area for you output as follows:

Start Date End Date Machine Average

and place the following Array formula in the average column.

=AVERAGE(IF(A1:A33<="end date",IF(A1:A33>="start
date",IF(B1:B33="machine",C1:C33))))

array entered (ctrl+shift+enter) will appear in "{ }"

Where A1:C33 would be replaced with the range of your table. Also, I've used
">=" & "<=" so the date range includes the start and end dates. You can
change that to suit your needs.

Sumproduct will also work, but I think the array may be shorter to calculate
the average as you would need to both count and sum the values.

HTH

PC