I have a sheet that looks like
User# 1/10/2007 1/17/2007 1/21/2007
1 0 21 44
2 0 45 66
3 0 22 76
4 0 13 45
5 0 45 90

So basically, every week the number of columns will increase as I collect
more data -- my table continues getting bigger and bigger.
Someplace then I will have an column of equations for the current data
for each user that looks like
= (D2-c2) * something

Where "D2" and "C2" should be the two most recent columns in the above table


This seems like such an obvious thing, yet I've rarely seen a sample
spreadsheet that deals with a constantly growing table. I have struggled with
this problem in various forms for a long time and have never found a good
solution.
The alternative here would be to manually shift the data over each period
when
entering the new data - or manually edit those equations.

Quick fix to this problem that i could think of would be using the =MAX
function coupled with the =LOOKUP function (assuming the dates are in
assending order). cant quite understand what you are asking. Max obviosly
finds the most recent date and the lookup can reference what cells you need

"Joe Porkka [MSFT]" wrote:

> I have a sheet that looks like
> User# 1/10/2007 1/17/2007 1/21/2007
> 1 0 21 44
> 2 0 45 66
> 3 0 22 76
> 4 0 13 45
> 5 0 45 90
>
> So basically, every week the number of columns will increase as I collect
> more data -- my table continues getting bigger and bigger.
> Someplace then I will have an column of equations for the current data
> for each user that looks like
> = (D2-c2) * something
>
> Where "D2" and "C2" should be the two most recent columns in the above table
>
>
> This seems like such an obvious thing, yet I've rarely seen a sample
> spreadsheet that deals with a constantly growing table. I have struggled with
> this problem in various forms for a long time and have never found a good
> solution.
> The alternative here would be to manually shift the data over each period
> when
> entering the new data - or manually edit those equations.
>

You could use the LARGE function to find the two largest dates in row 1.

LARGE(1:1,1) finds first largest date
LARGE(1:1,2) finds second largest date

Then, you do something like:

=(HLOOKUP(LARGE(1:1,1),A1:Z10,2)-HLOOKUP(LARGE(1:1,2),A1:Z10,2))*something

Adjust the ranges to meet your needs.

HTH,
Elkar


"Joe Porkka [MSFT]" wrote:

> I have a sheet that looks like
> User# 1/10/2007 1/17/2007 1/21/2007
> 1 0 21 44
> 2 0 45 66
> 3 0 22 76
> 4 0 13 45
> 5 0 45 90
>
> So basically, every week the number of columns will increase as I collect
> more data -- my table continues getting bigger and bigger.
> Someplace then I will have an column of equations for the current data
> for each user that looks like
> = (D2-c2) * something
>
> Where "D2" and "C2" should be the two most recent columns in the above table
>
>
> This seems like such an obvious thing, yet I've rarely seen a sample
> spreadsheet that deals with a constantly growing table. I have struggled with
> this problem in various forms for a long time and have never found a good
> solution.
> The alternative here would be to manually shift the data over each period
> when
> entering the new data - or manually edit those equations.
>

Joe -

You are running into 2 separate issues:

The first is trying to use a spreadsheet to track data that screams out to
be in a DATABASE.

The second is that your data is structured awkwardly.

Based on your sample data, you are far better off to have 3 and only 3
columns:

UserID, Date, and Value

Once Your data is in that format, you can use a simple SUMPRODUCT() formula
to calculate the value you are seeking.

Another point about the suggested layout is that it allows you to do pivot
tables and other analyses easy as anything, while your current structure
makes analysis very difficult.


"Joe Porkka [MSFT]" wrote:

> I have a sheet that looks like
> User# 1/10/2007 1/17/2007 1/21/2007
> 1 0 21 44
> 2 0 45 66
> 3 0 22 76
> 4 0 13 45
> 5 0 45 90
>
> So basically, every week the number of columns will increase as I collect
> more data -- my table continues getting bigger and bigger.
> Someplace then I will have an column of equations for the current data
> for each user that looks like
> = (D2-c2) * something
>
> Where "D2" and "C2" should be the two most recent columns in the above table
>
>
> This seems like such an obvious thing, yet I've rarely seen a sample
> spreadsheet that deals with a constantly growing table. I have struggled with
> this problem in various forms for a long time and have never found a good
> solution.
> The alternative here would be to manually shift the data over each period
> when
> entering the new data - or manually edit those equations.
>

Hey that sounds like the right approach...
You are right - eventually I do want a database, but in the meantime I want
to get something working a little sooner.

I don't understand how SUMPRODUCT does the computation necessary.
Given the table of 3 columns (user, date, currentData), what I would want is
(in real life the data would be sorted by date, not user, but that shouldn't
matter here at all).
A B C
1 User Date Reading
2 1 1/10/2007 0
3 1 1/17/2007 21
4 1 1/21/2007 44
5 2 1/10/2007 0
6 2 1/17/2007 45
7 2 1/21/2007 66
8 3 1/10/2007 0
9 3 1/17/2007 22
10 3 1/21/2007 76
11 4 1/10/2007 0
12 4 1/17/2007 13
13 4 1/21/2007 45
14 5 1/10/2007 0
15 5 1/17/2007 45
16 5 1/21/2007 90


For each unique user
select 2 most recent rows and subtract currentData.
So I would end up with data that looks like:
user Delta Comment
1 23 "C4-c3"
2 21 "c7-c6"
3 54 "c10-c9"
4 32
5 45



"Duke Carey" wrote:

> Joe -
>
> You are running into 2 separate issues:
>
> The first is trying to use a spreadsheet to track data that screams out to
> be in a DATABASE.
>
> The second is that your data is structured awkwardly.
>
> Based on your sample data, you are far better off to have 3 and only 3
> columns:
>
> UserID, Date, and Value
>
> Once Your data is in that format, you can use a simple SUMPRODUCT() formula
> to calculate the value you are seeking.
>
> Another point about the suggested layout is that it allows you to do pivot
> tables and other analyses easy as anything, while your current structure
> makes analysis very difficult.
>
>
> "Joe Porkka [MSFT]" wrote:
>
> > I have a sheet that looks like
> > User# 1/10/2007 1/17/2007 1/21/2007
> > 1 0 21 44
> > 2 0 45 66
> > 3 0 22 76
> > 4 0 13 45
> > 5 0 45 90
> >
> > So basically, every week the number of columns will increase as I collect
> > more data -- my table continues getting bigger and bigger.
> > Someplace then I will have an column of equations for the current data
> > for each user that looks like
> > = (D2-c2) * something
> >
> > Where "D2" and "C2" should be the two most recent columns in the above table
> >
> >
> > This seems like such an obvious thing, yet I've rarely seen a sample
> > spreadsheet that deals with a constantly growing table. I have struggled with
> > this problem in various forms for a long time and have never found a good
> > solution.
> > The alternative here would be to manually shift the data over each period
> > when
> > entering the new data - or manually edit those equations.
> >

With the user ID in E1, the latest date in F1, and the next latest date in G1

=SUMPRODUCT(--(A2:A16=E1),--(B2:B16=F1),C2:C16)-SUMPRODUCT(--(A2:A16=E1),--(B2:B16=G1),C2:C16)

"Joe Porkka [MSFT]" wrote:

> Hey that sounds like the right approach...
> You are right - eventually I do want a database, but in the meantime I want
> to get something working a little sooner.
>
> I don't understand how SUMPRODUCT does the computation necessary.
> Given the table of 3 columns (user, date, currentData), what I would want is
> (in real life the data would be sorted by date, not user, but that shouldn't
> matter here at all).
> A B C
> 1 User Date Reading
> 2 1 1/10/2007 0
> 3 1 1/17/2007 21
> 4 1 1/21/2007 44
> 5 2 1/10/2007 0
> 6 2 1/17/2007 45
> 7 2 1/21/2007 66
> 8 3 1/10/2007 0
> 9 3 1/17/2007 22
> 10 3 1/21/2007 76
> 11 4 1/10/2007 0
> 12 4 1/17/2007 13
> 13 4 1/21/2007 45
> 14 5 1/10/2007 0
> 15 5 1/17/2007 45
> 16 5 1/21/2007 90
>
>
> For each unique user
> select 2 most recent rows and subtract currentData.
> So I would end up with data that looks like:
> user Delta Comment
> 1 23 "C4-c3"
> 2 21 "c7-c6"
> 3 54 "c10-c9"
> 4 32
> 5 45
>
>
>
> "Duke Carey" wrote:
>
> > Joe -
> >
> > You are running into 2 separate issues:
> >
> > The first is trying to use a spreadsheet to track data that screams out to
> > be in a DATABASE.
> >
> > The second is that your data is structured awkwardly.
> >
> > Based on your sample data, you are far better off to have 3 and only 3
> > columns:
> >
> > UserID, Date, and Value
> >
> > Once Your data is in that format, you can use a simple SUMPRODUCT() formula
> > to calculate the value you are seeking.
> >
> > Another point about the suggested layout is that it allows you to do pivot
> > tables and other analyses easy as anything, while your current structure
> > makes analysis very difficult.
> >
> >
> > "Joe Porkka [MSFT]" wrote:
> >
> > > I have a sheet that looks like
> > > User# 1/10/2007 1/17/2007 1/21/2007
> > > 1 0 21 44
> > > 2 0 45 66
> > > 3 0 22 76
> > > 4 0 13 45
> > > 5 0 45 90
> > >
> > > So basically, every week the number of columns will increase as I collect
> > > more data -- my table continues getting bigger and bigger.
> > > Someplace then I will have an column of equations for the current data
> > > for each user that looks like
> > > = (D2-c2) * something
> > >
> > > Where "D2" and "C2" should be the two most recent columns in the above table
> > >
> > >
> > > This seems like such an obvious thing, yet I've rarely seen a sample
> > > spreadsheet that deals with a constantly growing table. I have struggled with
> > > this problem in various forms for a long time and have never found a good
> > > solution.
> > > The alternative here would be to manually shift the data over each period
> > > when
> > > entering the new data - or manually edit those equations.
> > >

While this works, I'm not thrilled with the way it ends up looking in the
workbook.

I did find "structured references" - which seems to be a better match for
what I'm looking for. I've only just started learning about them - but it
seems like a great new feature in Excel 2007.

"Duke Carey" wrote:

> With the user ID in E1, the latest date in F1, and the next latest date in G1
>
> =SUMPRODUCT(--(A2:A16=E1),--(B2:B16=F1),C2:C16)-SUMPRODUCT(--(A2:A16=E1),--(B2:B16=G1),C2:C16)
>
> "Joe Porkka [MSFT]" wrote:
>
> > Hey that sounds like the right approach...
> > You are right - eventually I do want a database, but in the meantime I want
> > to get something working a little sooner.
> >
> > I don't understand how SUMPRODUCT does the computation necessary.
> > Given the table of 3 columns (user, date, currentData), what I would want is
> > (in real life the data would be sorted by date, not user, but that shouldn't
> > matter here at all).
> > A B C
> > 1 User Date Reading
> > 2 1 1/10/2007 0
> > 3 1 1/17/2007 21
> > 4 1 1/21/2007 44
> > 5 2 1/10/2007 0
> > 6 2 1/17/2007 45
> > 7 2 1/21/2007 66
> > 8 3 1/10/2007 0
> > 9 3 1/17/2007 22
> > 10 3 1/21/2007 76
> > 11 4 1/10/2007 0
> > 12 4 1/17/2007 13
> > 13 4 1/21/2007 45
> > 14 5 1/10/2007 0
> > 15 5 1/17/2007 45
> > 16 5 1/21/2007 90
> >
> >
> > For each unique user
> > select 2 most recent rows and subtract currentData.
> > So I would end up with data that looks like:
> > user Delta Comment
> > 1 23 "C4-c3"
> > 2 21 "c7-c6"
> > 3 54 "c10-c9"
> > 4 32
> > 5 45
> >
> >
> >
> > "Duke Carey" wrote:
> >
> > > Joe -
> > >
> > > You are running into 2 separate issues:
> > >
> > > The first is trying to use a spreadsheet to track data that screams out to
> > > be in a DATABASE.
> > >
> > > The second is that your data is structured awkwardly.
> > >
> > > Based on your sample data, you are far better off to have 3 and only 3
> > > columns:
> > >
> > > UserID, Date, and Value
> > >
> > > Once Your data is in that format, you can use a simple SUMPRODUCT() formula
> > > to calculate the value you are seeking.
> > >
> > > Another point about the suggested layout is that it allows you to do pivot
> > > tables and other analyses easy as anything, while your current structure
> > > makes analysis very difficult.
> > >
> > >
> > > "Joe Porkka [MSFT]" wrote:
> > >
> > > > I have a sheet that looks like
> > > > User# 1/10/2007 1/17/2007 1/21/2007
> > > > 1 0 21 44
> > > > 2 0 45 66
> > > > 3 0 22 76
> > > > 4 0 13 45
> > > > 5 0 45 90
> > > >
> > > > So basically, every week the number of columns will increase as I collect
> > > > more data -- my table continues getting bigger and bigger.
> > > > Someplace then I will have an column of equations for the current data
> > > > for each user that looks like
> > > > = (D2-c2) * something
> > > >
> > > > Where "D2" and "C2" should be the two most recent columns in the above table
> > > >
> > > >
> > > > This seems like such an obvious thing, yet I've rarely seen a sample
> > > > spreadsheet that deals with a constantly growing table. I have struggled with
> > > > this problem in various forms for a long time and have never found a good
> > > > solution.
> > > > The alternative here would be to manually shift the data over each period
> > > > when
> > > > entering the new data - or manually edit those equations.
> > > >