Formula with lookup

[- Butch]

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.

 

[Alin Ababei]

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

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

 

[FloMM2]

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.