Calculating a YTD figures from changing monthly figure.

[Guest]

I have a column of figures in Row A (Monthly figs). I want to be able to
change these monthly figures and for it to be added to a running total in
Column B (YTD). However each time I put the formula in =(a1+b1) and =(a2+b2)
etc., the figures change all down column B whereas I only want the figs in
the corresponding cell in Column B to change. How do I stop this happening?

 

[Guest]

I have a column of figures in Row A (Monthly figs). I want to be able to
change these monthly figures and for it to be added to a running total in
Column B (YTD). However each time I put the formula in =(a1+b1) and =(a2+b2)
etc., the figures change all down column B whereas I only want the figs in
the corresponding cell in Column B to change. How do I stop this happening?

Just some thoughts to achieve what I think you're trying to do here ..

Assuming the 12 monthly figures are input in A2:A13,
we could pull up a "cumulative YTD" figure in B2:B13

Put in B2: =A2
Put in B3: =SUM(B2,A3)
Copy B3 down to B13

To copy the formula in B3 down, point at the fill handle
(that's the solid black square at the bottom right hand corner of B3)
then drag down to B13.

When the monthly figures within A2:A13 are changed,
col B will compute accordingly.

---

 

[Guest]

Apologies - It is the cumulative effect in Column B than I am trying to
avoid. Imagine the figure in A2 is April's sales figure and B2 is the YTD
figure Jan-Apr. When I produce the sales figure for May I want to be able to
change the figure in A2 to my new figure and for this to be added to B2. I do
not want the figures in the rest of Column B to change.

 

[Guest]

Apologies - It is the cumulative effect in Column B than I am trying to
avoid. Imagine the figure in A2 is April's sales figure and B2 is the YTD
figure Jan-Apr. When I produce the sales figure for May I want to be able to
change the figure in A2 to my new figure and for this to be added to B2. I do
not want the figures in the rest of Column B to change.

well ... if I'm reading / guessing you right, it's not possible via formulas
then again, why not go with the approach suggested earlier,
where successive monthly figures are entered progressively down in A2:A13
instead of repeatedly overwriting a single source cell (A2?) with new
figures ??

Perhaps you could paste the actual formulas you have in B2, B3 & B4 (say)
so that we could have a better feel of what's happening over there <g>


---

 

[Guest]

Mth Apr YTD
Co A 2 53
Co B 16 32
Co C 46 103
Co D 10 89
Using these figures I was putting the formula =(B2+C2) where the figure 53
is. I solved the Circular reference I created and this worked fine. However
things go wrong when I put =(B3+C3) where 32 is located. Everytime I update
B2, where 2 is located it automatically adds another16 to C3!! Each row is a
distinct set of figures although I need to create graphs, total and calculate
percentages - hence in this format.

 

[Guest]

Here's an example set up to make it dynamic ..
(wo getting into circular ref problems <g>)

A sample construct available at:
http://www.savefile.com/files/8312983
Monthly Figures n Dynamic YTD.xls

Source input table for the year is in E15
E11 houses first of month dates, formatted as: mmm-yy (Jan-06, Feb-06 ..)
Monthly figures data is input within E25

In B1 is a data validation droplist,
created via clicking Data > Validation
Allow: List
Source: =$E$1:$P$1

B1 allows the selection of the current month

In C1: YTD (a label)

Put in B2:
=IF($B$1="","",HLOOKUP($B$1,$E$1:$P$5,ROW(),0))

Put in C2:
=IF(OR($B$1="",B2=0),"",SUM(OFFSET(E2,,,,MATCH($B$1,$E$1:$P$1,0))))

Select B2:C2, copy down to C5

B2:B5 will return the figures for the month selected in B1 from the source
input table in E15. C2:C5 will return the corresponding YTD figures. The
sample chart plotted on $A$1:$C$5 will also update accordingly.