relative column absolute row not working

[tara_922]

I found this on the internet...

3. Relative Column/Absolute Row
e.g. A$1
means When you use auto-fill across row, it will remain row 1 but when you auto-fill across column, the reference will change to B$1, C$1, D$1 and so on.

I've tried that because my formula is
=AVERAGE(B$11:B$14)

but when I drag down it stays at =AVERAGE(B$11:B$14)

is there an error in what I am doing?

Please help,
Thanks.
Tara

 

[Sal Paradise]

If you want the row to change (not the column) then you use:
=AVERAGE($B11:$B14)

The $ sign will fix either the row ($A1) the column (A$1) or both ($A$1).

Give it a try on a new sheet and see how it works if you're still confused.

 

[tara_922]

Hi Sal!

Thanks for your reply. I'm trying to get it to fix the column with B$11 but it's not working for some reason. When I drag the formula it is keeping everything the same? I don't get it. totally confused..

 

[Matt Rogers]

Hi.

You have 3 options:

Excel Workbook
	A
1	=AVERAGE($B11:$B14)
2	=AVERAGE(B$11:B$14)
3	=AVERAGE($B$11:$B$14)
Sheet

 

[Sal Paradise]

Sorry, my post was bad. I did them backwards.

Put the $ in front of the letter (column name) to fix columns.

e.g. $A1

Put the $ in front of the number (row number) to fix rows.

e.g. A$1

Put $ in front of both to fix both.

e.g. $A$1

 

[tara_922]

So why isn't it working for me? is there a setting in excel somewhere that I need to change?

Thanks!
Tara

 

[Sal Paradise]

Okay, maybe I'm not being clear.

If you drag this formula down, it will never change. The row numbers are "fixed" so they will not change no matter how far down you drag them:
=AVERAGE(B$11:B$14)

If you want the row number to change, you have to take off the $ signs from the rows before you copy it down:
=AVERAGE(B11:B14)

If you have that formula in A1, and you copy it to A2, it will change to:
=AVERAGE(B12:B15)

If you move it to the right (B2) it will change to:
=AVERAGE(C12:C15)

To stop the up-down movement for the rows, change it to:
=AVERAGE(B$11:B$14)

To stop the left-right movement for the columns, change it to:
=AVERAGE($B11:$B14)

 

[tara_922]

"To stop the up-down movement for the rows, change it to:
=AVERAGE(B$11:B$14)"

This is what I'm doing. Shouldn't it change to =AVERAGE(C$11:C$14) when I drag it down the column?

Tara

 

[Sal Paradise]

No, it will never change the column when you drag it down.

It will change the row.

Going from row 1 to row 2 will increase all the numbers by one.

Going from column B to column C will increase all the letters by one.

You cannot use the $ to make the column change as you move the row down. It doesn't work that way.

Perhaps you'd be better off explaining what you're trying to do since none of us know and our suggestions aren't working.

 

[tara_922]

You are making perfect sense. I'm trying to do the impossible. Drag a formula down the column changing the cell letter reference by adding one but not changing the cell number reference. So going from

=AVERAGE(B$3:B$6) dragging to achieve
=AVERAGE(C$3:C$6) in the next cell.

So my starting cell would be D38. By the time I got to D69 I would have a whole month of data from B$3:B$6 up to AF$3:AF$6.

I know this is confusing. I think your last post explained it very well. I thought you could use absolute references dragging down columns.

Since I can't I'll be interested in knowing if I can chart data that is in row order instead of column order.

Thanks so much for your help! I totally appreciate it!

Tara