Relative vs. Absolute Values in Formulas

[Guest]

I understand the concept of absolute and relative
formulas. But what I can't seem to make happen is this
scenario:

A B C D E F
1 10 20 30 40 =d1 1/10/04
2 50 60 70 80 =if(f2=f1,e1+d2,d2) 1/10/04
3 90 100 110 120 =if(f3=f2,e2+d3,d3) 1/10/04
4 130 140 150 160 =if(f4=f3,e3+d4,d4) 1/10/04


What the formulas in column E say are to cumulate the
totals for all dates that are the same. As soon as I
change the date in column F, it starts over. These are
weekly production "buckets." The problem arises when I
want to move the data around. Let's say I want to move
A22 and put it above what is now row 3 (by cutting and
inserting cut cells). When I do that, the formulas in
column E also change, throwing everything off. I have
tried with and without dollar signs, I've tried using the
R1C1 method, but no matter what I do, Excel tries to make
the formulas match where I've moved the data. I
thought "absolute" meant that it always refers to the
same cell whether you move it or not but apparently I'm
missing something. I want the formulas in column E to
always remain frozen to whatever is in the referenced
cells. Can I do this? Thanks for any help!

Bill


__________________
Bill in ABQ

 

[Guest]

Bill

You may have to change your approach. Try putting the following formula into F1 and copy down. It will provide a progressive total to match the dates, and if the dates in column F change, then it will show the progressive for the dates provided.

Tony

=SUMPRODUCT(--($F$1:F1=F1),--($D$11))

----- (e-mail address removed) wrote: -----

I understand the concept of absolute and relative
formulas. But what I can't seem to make happen is this
scenario:

A B C D E F
1 10 20 30 40 =d1 1/10/04
2 50 60 70 80 =if(f2=f1,e1+d2,d2) 1/10/04
3 90 100 110 120 =if(f3=f2,e2+d3,d3) 1/10/04
4 130 140 150 160 =if(f4=f3,e3+d4,d4) 1/10/04


What the formulas in column E say are to cumulate the
totals for all dates that are the same. As soon as I
change the date in column F, it starts over. These are
weekly production "buckets." The problem arises when I
want to move the data around. Let's say I want to move
A22 and put it above what is now row 3 (by cutting and
inserting cut cells). When I do that, the formulas in
column E also change, throwing everything off. I have
tried with and without dollar signs, I've tried using the
R1C1 method, but no matter what I do, Excel tries to make
the formulas match where I've moved the data. I
thought "absolute" meant that it always refers to the
same cell whether you move it or not but apparently I'm
missing something. I want the formulas in column E to
always remain frozen to whatever is in the referenced
cells. Can I do this? Thanks for any help!

Bill


__________________
Bill in ABQ