fixing a row (x-axis) in excel slope calculation

[angel1980]

Dear All,

I am trying to calculate SLOPE of all rows using excel.

Each row is y-axis and x-axis is on the first row.

x1 0 4 8 12 16 20
a1 -0.2 -0.1 0 0.1 0.2 0.3
a2 -0.4 -0.3 0.1 0.4 0.5 0.2

The slope is a1 is = SLOPE(R2C2:R2C7, R1C2:R1C7)
The slope is a2 is = SLOPE(R3C2:R3C7, R1C2:R1C7) but it keeps getting updated to: SLOPE(R3C2:R3C7, R2C2:R2C7)

However, when I try to compute the slope going down each row, the x-axis keeps getting shifted and I want to keep it fixed. Can anyone help me with this?

Thanks.

[MrShorty]

Something in what you describe does not seem right. The R1C1 references you are describing are absolute references -- no matter where you copy them to, they should never change, and should always use the same data (R2C2:R2C7 for known_y's and R1C2:R1C7 for known x's). What you are describing should not happen a normal copy and paste.

I would expect that you should just need the right combination of absolute and relative references to get what you want. A formula like =SLOPE(RC[1]:RC[6],R1C2:R1C7). Note that the known y's reference is a relative reference, and the known x's is an absolute reference. As this formula is copied , the known x's should always be R1C2:R1C7, while the known y's will always be from the same row as the function, 1 through 6 columns to the right of the function.

Perhaps you are using a macro or something else to copy/paste these formulas? Or perhaps something else is running in the background that is auto updating your formulas?