My one month formula is the one i listed earlier --
=(VLOOKUP(TODAY(),Performance!$A$4:$E$10000,2,FALS E)-(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?
"Frank Kabel" wrote:
Hi
what is the exact formula you have tried and what result did you get?
--
Regards
Frank Kabel
Frankfurt, Germany
"Ted Metro" schrieb im Newsbeitrag
...
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" wrote:
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(Perfo rmance!$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
"Ted Metro" schrieb im
Newsbeitrag
...
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
|