Problem with named ranges

[JenniferMurphy]

I swear I've done this before, but it's not working today and I cannot figure out why.

In the table below, I have assigned the name "Header" to row 6 and "Footer" to row 12. In C12, I calculate the average of the ratings in C7:C11. In C13, I try to do the same thing, but using the named ranges. Why isn't it working?
[TABLE="class: grid, width: 562"]
<tbody>[TR]
[TD][/TD]
[TD]C/R[/TD]
[TD]C[/TD]
[TD]D
[/TD]
[/TR]
[TR]
[TD]Header->[/TD]
[TD]6[/TD]
[TD]Rating[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]7[/TD]
[TD]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]8[/TD]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]9[/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]10[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]11[/TD]
[TD]1[/TD]
[TD]Formula[/TD]
[/TR]
[TR]
[TD]Footer->[/TD]
[TD]12[/TD]
[TD]3.00[/TD]
[TD]C12: =AVERAGE(C7:C11)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]13[/TD]
[TD]4.00[/TD]
[TD]C13: =AVERAGE(OFFSET(Header,1,0):OFFSET(Footer,-1,0))[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]14[/TD]
[TD]6[/TD]
[TD]C14: =ROW(Header)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]15[/TD]
[TD]12[/TD]
[TD]C15: =ROW(Footer)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]16[/TD]
[TD]5[/TD]
[TD]C16: =OFFSET(Header,1,0)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]17[/TD]
[TD]1[/TD]
[TD]C17: =OFFSET(Footer,-1,0)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]18[/TD]
[TD]7[/TD]
[TD]C18: =ROW(OFFSET(Header,1,0))[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]19[/TD]
[TD]1[/TD]
[TD]C19: =COLUMN(OFFSET(Header,1,0))[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]20[/TD]
[TD]11[/TD]
[TD]C20: =ROW(OFFSET(Footer,-1,0))[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]21[/TD]
[TD]1[/TD]
[TD]C21: =COLUMN(OFFSET(Footer,-1,0))[/TD]
[/TR]
</tbody>[/TABLE]

 

[oldbrewer]

is it because header is a group of cells ? Test it by making header = A6

 

[Dossfm0q]

it is Ok if range is not blank

Sub MakeRange()
Cells.Clear
    ThisWorkbook.Names.Add Name:="Header", RefersTo:="=Sheet1!$C$1:$C$6"
    ThisWorkbook.Names.Add Name:="Footer", RefersTo:="=Sheet1!$C$7:$C$12"
    T = Split(",5,4,3,2,1", ",")
    For R = LBound(T) + 6 To UBound(T) + 6
    Cells(R, 3) = T(R - 6)
    Next
    [C12] = "=AVERAGE(C7:C11)"
    [C13] = "=AVERAGE(OFFSET(Header,1,0):OFFSET(Footer,-1,0))"
    [C14] = "=ROW(Header)"
    [C15] = "=ROW(Footer)"
    [C16] = "=OFFSET(Header,1,0)" '????
    [C17] = "=OFFSET(Footer,-1,0)" ' ????
    [C18] = "=ROW(OFFSET(Header,1,0))"
    [C19] = "=COLUMN(OFFSET(Header,1,0))"
    [C20] = "=ROW(OFFSET(Footer,-1,0))"
    [C21] = "=COLUMN(OFFSET(Footer,-1,0))"


End Sub

 

[oldbrewer]

I regret I cannot help further as I do not understand [C12] = "=AVERAGE(C7:C11)"

 

[B___P]

Hi Jennifer Murphy,
are you sure you understood the meaning of namedranges?
If yes, isn't it easier to name range C7:C11 as myaverage and then use in C13 =AVERAGE(MYAVERAGE) ?

 

[Tetra201]

... I have assigned the name "Header" to row 6 and "Footer" to row 12. In C12, I calculate the average of the ratings in C7:C11. In C13, I try to do the same thing, but using the named ranges. Why isn't it working?..

Because =AVERAGE(OFFSET(Header,1,0):OFFSET(Footer,-1,0)) is equivalent to =AVERAGE(7:11)

If you want to get =AVERAGE(C7:C11), try =AVERAGE(OFFSET(Header,1,0):OFFSET(Footer,-1,0) C:C)

 

[JenniferMurphy]

Because =AVERAGE(OFFSET(Header,1,0):OFFSET(Footer,-1,0)) is equivalent to =AVERAGE(7:11)

That was an interesting puzzle. It took me awhile to figure out that average(7:11) is the average of all the numeric values in rows 7 thru 11, right?

In that case, what would the expression be for the average of all of the numeric values in columns 7 to 11?

If you want to get =AVERAGE(C7:C11), try =AVERAGE(OFFSET(Header,1,0):OFFSET(Footer,-1,0) C:C)

That works. Amazing. This is the Space operator, right? I keep forgetting that one. So it's the average of the intersection of rows 7-11 and column C, right?

In the table below, I applied your formula in C13 and it works perfectly. But I am puzzled as to why my formula fails. In C14 & C15, I put the two arguments and they return the correct values. In C16:C19, I calculate the row and column values for these two arguments. The rows are correct (7 & 11), but the columns are not (1). Why is that?

[TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]C/R[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD]Header->[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]Rating[/TD]
[TD="align: center"]Weight[/TD]
[TD="align: center"]Rating[/TD]
[TD="align: center"]Formula[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]0.00[/TD]
[TD="align: center"]0.00[/TD]
[TD]E7: =Rating*Weight[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]0.25[/TD]
[TD="align: center"]1.00[/TD]
[TD]E8: =Rating*Weight[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]0.50[/TD]
[TD="align: center"]1.50[/TD]
[TD]E9: =Rating*Weight[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]0.75[/TD]
[TD="align: center"]1.50[/TD]
[TD]E10: =Rating*Weight[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1.00[/TD]
[TD="align: center"]1.00[/TD]
[TD]E11: =Rating*Weight[/TD]
[/TR]
[TR]
[TD]Footer->[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]3.00[/TD]
[TD="align: center"]2.50[/TD]
[TD="align: center"]2.00[/TD]
[TD]C12: =AVERAGE(C7:C11)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]3.00[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD]C13: =AVERAGE(OFFSET(Header,1,0):OFFSET(Footer,-1,0) C:C)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD]C14: =OFFSET(Header,1,0)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD]C15: =OFFSET(Footer,-1,0)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"]16[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD]C16: =ROW(OFFSET(Header,1,0))[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"]17[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD]C17: =COLUMN(OFFSET(Header,1,0))[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD]C18: =ROW(OFFSET(Footer,-1,0))[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"]19[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD]C19: =COLUMN(OFFSET(Footer,-1,0))[/TD]
[/TR]
</tbody>[/TABLE]

I have assigned the name Rating to Column C and Weight to Column D. This makes the formulas in Column E work perfectly. Why does this work and the Average formula does not?

Thanks

 

[JenniferMurphy]

I regret I cannot help further as I do not understand [C12] = "=AVERAGE(C7:C11)"

The values in Column D are the formulas in Column C. I have a little UDF that will return the expression in any cell. It prefixes the result with the cell address. So, "[C12] =AVERAGE(C7:C11)" simply means that cell C12 contains the expression "=AVERAGE(C7:C11)".

 

[JenniferMurphy]

Hi Jennifer Murphy,
are you sure you understood the meaning of namedranges?

Yes, I think so.

If yes, isn't it easier to name range C7:C11 as myaverage and then use in C13 =AVERAGE(MYAVERAGE) ?

I don't know if it's easier or not, but it doesn't do what I want. If I name C7:C11, then I have to be careful when I add or delete rows or the range will not change accordingly. If I name the row above the range (Header) and the one below (Footer), then I can add or delete rows anywhere in between and the formula will continue to work correctly.

Do you understand the meaning of that?

 

[oldbrewer]

So, "[C12] =AVERAGE(C7:C11)" simply means that cell C12 contains the expression "=AVERAGE(C7:C11)".

as I said, I am on a learning curve here.

If you add 100 rows between rows 8 and 9 how does this work, please ?