Formula with lookup

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

- Butch - 23 Jan 2008 01:53 GMT

I want to compare today's prices of 2 mutual funds with their respective
prices yesterday, 7 days ago, and 30 days ago.

My worksheet is constructed thus:

Column D lists the funds.
Column E through column JV are where the daily prices are entered for each
fund.

Row 1 contains the date for each day.

Rows 2 and 3 contain the historical prices for each day.

I would like column A to calculate the change between today's price and
yesterday's, column B to calculate the change between today's price and the
price a week ago, and column C to calculate the change between today's price
and a month ago.

I can't figure out how to do the lookup.

Any help would be appreciated. Thanks.

Reply to this Message

Alin Ababei - 23 Jan 2008 08:35 GMT

> I want to compare today's prices of 2 mutual funds with their respective
> prices yesterday, 7 days ago, and 30 days ago.
[quoted text clipped - 17 lines]
>
> Any help would be appreciated. Thanks.

Hi,

Try these formulas:
a) in A2 you can use =HLOOKUP(TODAY();$E$1:$JV$3;ROW(A2);FALSE)-
HLOOKUP(TODAY()-1;$E$1:$JV$3;ROW(A2);FALSE)

b) in B2 you can use =HLOOKUP(TODAY();$E$1:$JV$3;ROW(B2);FALSE)-
HLOOKUP(TODAY()-7;$E$1:$JV$3;ROW(B2);FALSE)

c) in C2 you can use =HLOOKUP(TODAY();$E$1:$JV$3;ROW(C2);FALSE)-
HLOOKUP(IF(WEEKDAY(EDATE(TODAY();-1))<6;EDATE(TODAY();-1);IF(WEEKDAY(EDATE(TODAY();-1))=6;EDATE(TODAY();-1)-1;EDATE(TODAY();-1)+1));
$E$1:$JV$3;ROW(C2);FALSE)

Of course, the formulas can be copied down for your other funds.

Alin

Reply to this Message

FloMM2 - 23 Jan 2008 09:32 GMT

Butch,
You can use this also:
A1
=NOW()
A3
"Daily Change"
=SUM(HLOOKUP(($A$1),(E$2:IV$50),2,FALSE)-(HLOOKUP($A$1-1),(E$2:IV$50),2,FALSE)))
B3
=SUM(HLOOKUP(($A$1),(E$2:IV$50),2,False)_(hlookup(($A$1-7),(E$2:IV$50),2,false)))
C3
=SUM(hlookup(($A$1),(E42:IV$50),2,False)-(hlookup(($A$1-31),(E$2:IV$50),2,FALSE)))

You will have to edit the formula as you copy it down. Change the "IV$50" to
how ever many lines you have. The number "2" increases with each row down.

hth
Good lick.

> I want to compare today's prices of 2 mutual funds with their respective
> prices yesterday, 7 days ago, and 30 days ago.
[quoted text clipped - 17 lines]
>
> Any help would be appreciated. Thanks.

Reply to this Message