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

 

[RichardMGreen]

To do that, you'd need a helper column with numbers 0 to 30 in it.
You can then use the OFFSET command to move the columns across when you drag down the rows.

 

[tara_922]

Awesome! Thanks Richard!

Tara

 

[RichardMGreen]

I only know this because I've had to do it previously.
It took me about a day and a half (and about a gallon of coffee) to figure out how to do it.

 

[Sal Paradise]

You can do this (you need a bit more than the $ sign though).

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">55.75</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">58</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">50.25</td><td style="text-align: right;;">81</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">52.75</td><td style="text-align: right;;">89</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">49.75</td><td style="text-align: right;;">38</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">46.25</td><td style="text-align: right;;">15</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">45.25</td><td style="text-align: right;;">90</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">49.25</td><td style="text-align: right;;">58</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">60.5</td><td style="text-align: right;;">48</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">64.5</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">60.75</td><td style="text-align: right;;">76</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">54.5</td><td style="text-align: right;;">54</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">61.5</td><td style="text-align: right;;">64</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">38.75</td><td style="text-align: right;;">48</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">35</td><td style="text-align: right;;">92</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">46.75</td><td style="text-align: right;;">39</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">40.5</td><td style="text-align: right;;">39</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;">55.25</td><td style="text-align: right;;">76</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;">60.25</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;;">56.5</td><td style="text-align: right;;">24</td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: right;;">45.75</td><td style="text-align: right;;">86</td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: right;;">32.75</td><td style="text-align: right;;">51</td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: right;;">25.25</td><td style="text-align: right;;">60</td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="text-align: right;;">20.75</td><td style="text-align: right;;">44</td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="text-align: right;;">26.75</td><td style="text-align: right;;">71</td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="text-align: right;;">37</td><td style="text-align: right;;">8</td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style="text-align: right;;">34</td><td style="text-align: right;;">8</td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style="text-align: right;;">34.75</td><td style="text-align: right;;">14</td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style="text-align: right;;">35.66667</td><td style="text-align: right;;">53</td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style="text-align: right;;">29</td><td style="text-align: right;;">32</td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style="text-align: right;;">56</td><td style="text-align: right;;">49</td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style="text-align: right;;"></td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">33</td><td style="text-align: right;;"></td><td style="text-align: right;;">56</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">A1</th><td style="text-align:left">=AVERAGE(<font color="Blue">OFFSET(<font color="Red">$B$3,ROWS(<font color="Green">$1:1</font>)-1,0,4,1</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

 

[tara_922]

You guys are absolutely amazing geniuses! Thanks so much!

Tara

 

[tnerb]

Re: Absolute column relative row not working

Hi.

I'm having a similar problem except that I have an absolute column & relative row that is referenced from another worksheet:

=IF(Costings!$A12='DONT EDIT. Cost Data'!$A$2,Costings!$D12*(VLOOKUP(Costings!$B12,electricalcost,3,FALSE)+Costings!$C12),L4)

When I copy this to the cells to the right I need the row ref's to increase but the columns to remain absolute. The column ref's do remain absolute but for some reason so do the row ref's.

Can you help?

Thanks

 

[Sal Paradise]

Re: Absolute column relative row not working

Red References will not change row or column if moved right or down
Green References will not change column when moved left or right, but will change row if moved up or down.
Blue References will change row if moved up or down, and will change column if moved left or right

=IF(Costings!$A12='DONT EDIT. Cost Data'!$A$2,Costings!$D12*(VLOOKUP(Costings!$B12,electricalcost,3,FALSE)+Costings!$C12),L4)

I don't know what your workbook looks like, so I can't solve your problem, all I can do is point out what should be changing.

If you copy your columns right, then all the red and green should remain the same column and same row. If you copy things down, then all the blue and all the green will grow in row.

Copying things right will not increase the row number in excel through "normal" methods.