Formula based on the lastest date for date

[Andy B]

I'm trying to base a simple '% of the total' formula on the latest range of
data at the latest date. Every time i insert a new column to update the data
i have to manually change the formula as i don't know how to write a formula
that will pick up the latest date and base the formula on that column.

can anyone help with this?

thanks
andy

 

[Gary''s Student]

Say you have a row or column that contains dates. Because dates are numbers,
the latest date is the MAX of the row or column.

 

[Pete_UK]

Perhaps you could explain what you have and what you are trying to do
in a bit more detail. Post any formulae that you have, whether they
work or not.

Pete

 

[Andy B]

Pete,

this is what it looks like, I want the % of portfolio colum to to take work
out automatically the percentage as i add in new columns over time with
updated values...

(the only formula i have in there is '% of portfolio' ie fund value/total
portfolio value)

Value Value Value % of
Fund 07/05/08 06/06/08 04/07/08 Portfolio
Fund 1 £29,337.63 £28,887.07 £26,653.90 33.94%
Fund 2 £14,769.50 £14,615.92 £13,677.61 17.42%
Fund 3 £13,674.17 £13,595.00 £12,147.22 15.47%
Fund 4 £9,947.85 £9,920.07 £9,876.94 12.58%
Fund 5 £20,190.88 £19,242.85 £16,169.34 20.59%
Totals £87,920.03 £86,260.91 £78,525.01

thanks
andy

 

[Pete_UK]

At the moment, then, you have 4 columns before your percentage column,
and I presume you want to insert a new 5th column and adjust the
formulae in the %age column to refer to that new column?

I would suggest instead that you insert a new column between the third
and fourth, and then copy what was the 4th column into the new 4th
column. Then you can enter new data in column 5 and your formulae will
still refer to this one.

Hope this helps.

Pete

 

[Claudemonet]

check whether the formula contains $ sign

eg.
cell A1 = Apple

you want to link cell B2 equal to value of A1

your formula bar for B2 will show =A1
- this will allow B2 content to update as A1 move

but

if your formula bar for B2 will show =$A$1
then if A1 (ie. Apple) move, B2 will only pickup the latest cell content
(eg. if you input orange in A1, you cell B2 will = orange).

Hope the above helps