Vlookup and Date

[Ted Metro]

I have one table of balance information and I track the balance daily.

Then I have a summary table that pulls growth numbers from that table for 1
week, 1 month, and 3 months.

The formula in my summary table for 1 month is -

=(VLOOKUP(TODAY()-1,Performance!$A$4:$E$10000,2,FALSE)-(VLOOKUP(DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()-1)),Performance!$A$4:$E$10000,2,FALSE)))/((VLOOKUP(DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()-1)),Performance!$A$4:$E$10000,2,FALSE)))


Here's my problem -

I only enter these numbers during the week. So on December 13th my table
looks back 1 month and errors out because Nomveber 13th was a Saturday and I
didn't enter a number. In this case I need the formula to go to the last
balance entered which would have been November 12th's Friday balance. Is
there a way to modify my formula so that if the date doesn't exist in the
table then it will go to the most recent previous entry -- If one month ago
(or three months ago) is a Saturday or Sunday it calculates off of the Friday
balance?

I guess I could always enter weekend days in my table and just copy and
paste over the data from Friday, but I hope there's an easier way so that I
don't have to add meaningless records to my table.

Ted

 

[Frank Kabel]

Hi
instead of
VLOOKUP(DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()-1)),Performance
!$A$4:$E$10000,2,FALSE)
try using the followung array formula (entered with CTRL+SHIFT+ENTER):


INDEX(Performance!$C$4:$C$10000,MATCH(MAX(IF(Performance!$A$4:$A$10000<
=DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()-1)),Performance!$A$4:$
A$10000)),Performance!$A$4:$A$10000,0))

 

[Ted Metro]

That actually doesn't do what I need it to do.

So right now here is how I have my data set up.

Column A Column B

11/29/2004 986.5
11/30/2004 1000
12/1/2004 1247
12/2/2004 1124
12/3/2004 1160
12/6/2004 1152.5
12/7/2004 1194

So my 1 week formula on 12-7 calculates the growth of 1 week and calculates
-- (1194-1000)/1000 -- and returns 19.4%.

You can see that 12/4 and 12/5 were Saturday and Sunday so I didn't enter a
date. However next month on 1/5/2005 when I calculate my 1 month growth I
won't have a 12/5/2004 date so I need it to pull the most recent number which
is the 12/3/2004 number. At which time it will calculate -- ([1/5/2005
number] - 1160)/1160.

 

[Frank Kabel]

Hi
what is the exact formula you have tried and what result did you get?

--
Regards
Frank Kabel
Frankfurt, Germany

That actually doesn't do what I need it to do.

So right now here is how I have my data set up.

Column A Column B

11/29/2004 986.5
11/30/2004 1000
12/1/2004 1247
12/2/2004 1124
12/3/2004 1160
12/6/2004 1152.5
12/7/2004 1194

So my 1 week formula on 12-7 calculates the growth of 1 week and calculates
-- (1194-1000)/1000 -- and returns 19.4%.

You can see that 12/4 and 12/5 were Saturday and Sunday so I didn't enter a
date. However next month on 1/5/2005 when I calculate my 1 month growth I
won't have a 12/5/2004 date so I need it to pull the most recent number which
is the 12/3/2004 number. At which time it will calculate -- ([1/5/2005
number] - 1160)/1160.

Hi
instead of
VLOOKUP(DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()-1)),Performance
!$A$4:$E$10000,2,FALSE)
try using the followung array formula (entered with CTRL+SHIFT+ENTER):


INDEX(Performance!$C$4:$C$10000,MATCH(MAX(IF(Performance!$A$4:$A$10000<
=DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()-1)),Performance!$A$4:$
A$10000)),Performance!$A$4:$A$10000,0))


--
Regards
Frank Kabel
Frankfurt, Germany

table
for 1
=(VLOOKUP(TODAY()-1,Performance!$A$4:$E$10000,2,FALSE)-(VLOOKUP(DATE(YE
AR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()-1)),Performance!$A$4:$E$10000,
2,FALSE)))/((VLOOKUP(DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()-1)
),Performance!$A$4:$E$10000,2,FALSE))) my
table the
last balance.
Is exist in
the of
the Friday copy
and so
that I

 

[Ted Metro]

My one month formula is the one i listed earlier --

=(VLOOKUP(TODAY(),Performance!$A$4:$E$10000,2,FALSE)-(VLOOKUP(DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY())),Performance!$A$4:$E$10000,2,FALSE)))/((VLOOKUP(DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY())),Performance!$A$4:$E$10000,2,FALSE)))

this takes today's balance, subtracts the balance from 1 month ago and then
calculates the change in percentage terms.

The problem is that on 12/14/2004 when it looks for the 11/14/2004 date it
won't find it because 11/14/2004 was a Sunday. So I'll want it to take the
12/14 balance and subtract the 11/12 balance and then calculate the change.

The problem isn't the vlookup it's the problem with weekends. I only have
weekday data (Monday through Friday), so when 12/14 is a Tuesday and 11/14 is
a Sunday I need the formula to grab the closest possible date, which will be
the 11/12 number.

Does that make sense?

Hi
what is the exact formula you have tried and what result did you get?

--
Regards
Frank Kabel
Frankfurt, Germany

That actually doesn't do what I need it to do.

So right now here is how I have my data set up.

Column A Column B

11/29/2004 986.5
11/30/2004 1000
12/1/2004 1247
12/2/2004 1124
12/3/2004 1160
12/6/2004 1152.5
12/7/2004 1194

So my 1 week formula on 12-7 calculates the growth of 1 week and calculates
-- (1194-1000)/1000 -- and returns 19.4%.

You can see that 12/4 and 12/5 were Saturday and Sunday so I didn't enter a
date. However next month on 1/5/2005 when I calculate my 1 month growth I
won't have a 12/5/2004 date so I need it to pull the most recent number which
is the 12/3/2004 number. At which time it will calculate -- ([1/5/2005
number] - 1160)/1160.

Hi
instead of
VLOOKUP(DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()-1)),Performance
!$A$4:$E$10000,2,FALSE)
try using the followung array formula (entered with CTRL+SHIFT+ENTER):


INDEX(Performance!$C$4:$C$10000,MATCH(MAX(IF(Performance!$A$4:$A$10000<
=DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()-1)),Performance!$A$4:$
A$10000)),Performance!$A$4:$A$10000,0))


--
Regards
Frank Kabel
Frankfurt, Germany

I have one table of balance information and I track the balance
daily.

Then I have a summary table that pulls growth numbers from that table
for 1
week, 1 month, and 3 months.

The formula in my summary table for 1 month is -


=(VLOOKUP(TODAY()-1,Performance!$A$4:$E$10000,2,FALSE)-(VLOOKUP(DATE(YE
AR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()-1)),Performance!$A$4:$E$10000,
2,FALSE)))/((VLOOKUP(DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()-1)
),Performance!$A$4:$E$10000,2,FALSE)))


Here's my problem -

I only enter these numbers during the week. So on December 13th my
table
looks back 1 month and errors out because Nomveber 13th was a
Saturday and I
didn't enter a number. In this case I need the formula to go to the
last
balance entered which would have been November 12th's Friday balance.
Is
there a way to modify my formula so that if the date doesn't exist in
the
table then it will go to the most recent previous entry -- If one
month ago
(or three months ago) is a Saturday or Sunday it calculates off of
the Friday
balance?

I guess I could always enter weekend days in my table and just copy
and
paste over the data from Friday, but I hope there's an easier way so
that I
don't have to add meaningless records to my table.

Ted

 

[Ted Metro]

Looked at excel's help and I think i fixed it. If I change my logical values
to TRUE from FALSE Excel will grab the next largest date that is less than
the date it's looking for, when it cannot find an exact match. This appears
to solve my problem.

Hi
what is the exact formula you have tried and what result did you get?

--
Regards
Frank Kabel
Frankfurt, Germany

That actually doesn't do what I need it to do.

So right now here is how I have my data set up.

Column A Column B

11/29/2004 986.5
11/30/2004 1000
12/1/2004 1247
12/2/2004 1124
12/3/2004 1160
12/6/2004 1152.5
12/7/2004 1194

So my 1 week formula on 12-7 calculates the growth of 1 week and calculates
-- (1194-1000)/1000 -- and returns 19.4%.

You can see that 12/4 and 12/5 were Saturday and Sunday so I didn't enter a
date. However next month on 1/5/2005 when I calculate my 1 month growth I
won't have a 12/5/2004 date so I need it to pull the most recent number which
is the 12/3/2004 number. At which time it will calculate -- ([1/5/2005
number] - 1160)/1160.

Hi
instead of
VLOOKUP(DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()-1)),Performance
!$A$4:$E$10000,2,FALSE)
try using the followung array formula (entered with CTRL+SHIFT+ENTER):


INDEX(Performance!$C$4:$C$10000,MATCH(MAX(IF(Performance!$A$4:$A$10000<
=DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()-1)),Performance!$A$4:$
A$10000)),Performance!$A$4:$A$10000,0))


--
Regards
Frank Kabel
Frankfurt, Germany

I have one table of balance information and I track the balance
daily.

Then I have a summary table that pulls growth numbers from that table
for 1
week, 1 month, and 3 months.

The formula in my summary table for 1 month is -


=(VLOOKUP(TODAY()-1,Performance!$A$4:$E$10000,2,FALSE)-(VLOOKUP(DATE(YE
AR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()-1)),Performance!$A$4:$E$10000,
2,FALSE)))/((VLOOKUP(DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()-1)
),Performance!$A$4:$E$10000,2,FALSE)))


Here's my problem -

I only enter these numbers during the week. So on December 13th my
table
looks back 1 month and errors out because Nomveber 13th was a
Saturday and I
didn't enter a number. In this case I need the formula to go to the
last
balance entered which would have been November 12th's Friday balance.
Is
there a way to modify my formula so that if the date doesn't exist in
the
table then it will go to the most recent previous entry -- If one
month ago
(or three months ago) is a Saturday or Sunday it calculates off of
the Friday
balance?

I guess I could always enter weekend days in my table and just copy
and
paste over the data from Friday, but I hope there's an easier way so
that I
don't have to add meaningless records to my table.

Ted