Using abosulte references in Sum function

[FryGirl]

Not sure I understand how Excel knows how to do this; however, one thing I know is it works, but thought I would ask.

In colunmn A I have some numbers with a running percentage in column B with the following formulas.

B12=SUM(A12:A$12)/SUM($A$12:$A$21)

B13=SUM(A$12:A13)/SUM($A$12:$A$21)

When I drag B12 down to B13 and so on the absolute references adjust automatically. Any ideas as to why? Again, the function works fine, just pondering why the references adjust?

 

[jasonb75]

The absolute references don't adjust, the relative refernces do. It's more common to have the absolute refernce before the relative, not after like in your first formula, i.e.

B12=SUM(A$12:A12)/SUM($A$12:$A$21)

B13=SUM(A$12:A13)/SUM($A$12:$A$21)


A row or column prefixed with $ is absolute so will remain unchanged anywhere you drag it, a row or column without the $ prefix is relative so will increase by 1 each cell you drag down or right and decrease when you drag up or left.

 

[FryGirl]

Thanks Jason,

I understand the concept of absolutes and relative, but in this case, the formula starts off with:

=SUM(A12:A$12)/ in B12 and when drug down to B13 it changes to =SUM(A$12:A13)/

If the $ in A$12 will lock the row, then how come when drug down it changes to A13 and the absolute reference is now automattically shifted to the front of the formula. If there was no $ on the first A12, how come it appears there now?

 

[MrKowz]

It rearranges the range so that it puts the earliest range first. The reason being is that Excel processes ranges from top left to bottom right, not the other way around.

For example, type into a cell:

=SUM(A100:A10). When you press enter, it will show =SUM(A10:A100)

 

[FryGirl]

Thanks Kieth

Makes sense and I thought it was Excel being smarter than I and once again, Excel is

Thanks Jason

 

[jasonb75]

This can happen if you drag up and then back down again

B12 =SUM(A$12:A12)

drag that up to B11 and you get B12=SUM(A11:A$12) which is correct, earliest range first.

Now drag B11 down to B12 and instead of the original =SUM(A$12:A12) you now get =SUM(A12:A$12)

Because the relative row hasn't exceeded the absolute, the ranges have not been inverted.

Trying to figure out "how it works" this could easily add i touch of confusion to the recipe.

 

[FryGirl]

Got it thanks