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.
"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
|