Table formula using ONLY previous records

**ariklee** · 01-28-2013, 04:31 PM

Hi,

I have a table in Excel 2010 that gets filtered based on one of the fields. In another field is the date, and all records are sorted by date. In a third field, I would like to perform a calculation using only the previous records in the table (earlier dates).

Here is a more robust explanation:

I am calculating subsidence of benchmarks using weekly survey data. The survey data comprises my table, so the fields are coordinates (x, y, z), date, benchmark ID and several other fields that calculate a regression through these points and confidence bands around the regression when filtered by benchmark ID. I want to add another field, which will contain a formula that calculates the progressive standard deviation of the regression slope - so, a formula which uses only the preceding records (earlier in time), not those below (later in time).

Thank you,

Eric

EDIT: I don't need the formula for standard deviation, what I'm looking for is a way to restrict a formula to use only preceding records in the table. It must also work when the table is filtered. Something like IF(row)<CurrentRow THEN (use the filtered data). Or, IF(date) <= ThisRowsDate then (use the filtered data) Thanks

**Pauleyb** · 01-28-2013, 04:58 PM

Just as an example, you can do something like this:
Assuming Column A has the raw data
in B1: =sum($A$1:A1)

Then drag that down to B100 (or whatever). It will provide a running sum of column A.

**ariklee** · 01-28-2013, 05:07 PM

That doesn't seem to work, because it is using data in all the previous cells, including those I have filtered out. I only want to use those cells after being filtered.

See the attached figure. Column S shows the equation you gave me. When I filter by Location (Column C), it is adding in extra data.

table formula.png

**Pauleyb** · 01-28-2013, 05:11 PM

Oh, okay. I didn't see the implication that you wanted to ignore any filtered data. Try looking at the SUBTOTAL functions.

**ariklee** · 01-28-2013, 06:14 PM

Should the "subtotal" go at the bottom of the table? If so, this won't work, because I need a calculation for each record. Here are a few I've tried that don't work:

{=SUM(IF(B328>=[Date],[@Elevation],""))}
{=SUM(IF([@Date]>=[Date],[@Elevation],""))}

**Pauleyb** · 01-29-2013, 10:57 AM

I think you need a UDF for this. This link shows a quick UDF that returns if a row is hidden or not.
http://www.excel-answers.com/microso...is-hidden.aspx
You could put it in a column, then use the results with SUMIFS.

**ariklee** · 01-29-2013, 11:37 AM

I got it. Thanks all for the advice. I can put "=subtotal(1xx, $b$2:b2)" in it's own column in the table. The first parameter in subtotal, if "1xx", will pass over the hidden cells. It takes a couple different columns to calculate the running standard deviation, but it's working!