Glenn Ray
asked on
Find the SUM of the Latest Values in a Table
This is a re-packaging of a series of questions recently posed regarding calculations needed on the bottom-most or most-recent data in a table.
Background:
The attached workbook contains an Excel Table showing daily sales data. Sales are only recorded on weekdays and non-holidays, so there are gaps in the table and that is normal (if not ideal).
There is a need to show the last N-number of days of sales, counting by the sales reported. Currently, there are a set of formulas (H8 and I8 to determine the rows containing the latest sales within N-days), a helper column (K) of functions used to determine the first applicable row, and a final formula to sum up the total (F8).
Request:
Create a single formula in F8 that replicates this process without the need of the functions in H8 and I8 or the helper column in K.
Note that the formula in F8 can immediately be re-written as:
=SUM(INDIRECT("B"&MAX($K$2 :$K$366)&" :B"&MATCH( 10^10,B:B, 1)))
but I've left it "dependent" so you can see the steps better.
The key problem for me has been trying to create an array function that reproduces the result in H8. I've tried this as an array function ([Ctrl]+[Shift]+[Enter]):
=SUMPRODUCT((COUNT(OFFSET( $B$1,ROW(2 :366)-1,0, $D$8*2,1)) =$D$8)*ROW (2:366))
but Excel doesn't like the ROW(2:366) argument inside the OFFSET and it fails.
Thanks,
-Glenn
SumLatestValues.xlsx
Background:
The attached workbook contains an Excel Table showing daily sales data. Sales are only recorded on weekdays and non-holidays, so there are gaps in the table and that is normal (if not ideal).
There is a need to show the last N-number of days of sales, counting by the sales reported. Currently, there are a set of formulas (H8 and I8 to determine the rows containing the latest sales within N-days), a helper column (K) of functions used to determine the first applicable row, and a final formula to sum up the total (F8).
Request:
Create a single formula in F8 that replicates this process without the need of the functions in H8 and I8 or the helper column in K.
Note that the formula in F8 can immediately be re-written as:
=SUM(INDIRECT("B"&MAX($K$2
but I've left it "dependent" so you can see the steps better.
The key problem for me has been trying to create an array function that reproduces the result in H8. I've tried this as an array function ([Ctrl]+[Shift]+[Enter]):
=SUMPRODUCT((COUNT(OFFSET(
but Excel doesn't like the ROW(2:366) argument inside the OFFSET and it fails.
Thanks,
-Glenn
SumLatestValues.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
My own preference is to swap the two INDEX functions for the first and last cells, but otherwise, this is an excellent solution.
Regards,
-Glenn