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

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.
--
Gary''s Student - gsnu200802


"Andy B" wrote:

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

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

On Sep 1, 12:03*pm, Andy B <Andy
wrote:
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

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" wrote:

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

On Sep 1, 12:03 pm, Andy B <Andy
wrote:
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

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

On Sep 1, 1:57*pm, Andy B wrote:
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" wrote:
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

On Sep 1, 12:03 pm, Andy B <Andy
wrote:
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- Hide quoted text -

- Show quoted text -

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


"Andy B" <Andy wrote in message
...
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