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?

	C/R	C	D
Header->	6	Rating
	7	5
	8	4
	9	3
	10	2
	11	1	Formula
Footer->	12	3.00	C12: =AVERAGE(C7:C11)
	13	4.00	C13: =AVERAGE(OFFSET(Header,1,0):OFFSET(Footer,-1,0))
	14	6	C14: =ROW(Header)
	15	12	C15: =ROW(Footer)
	16	5	C16: =OFFSET(Header,1,0)
	17	1	C17: =OFFSET(Footer,-1,0)
	18	7	C18: =ROW(OFFSET(Header,1,0))
	19	1	C19: =COLUMN(OFFSET(Header,1,0))
	20	11	C20: =ROW(OFFSET(Footer,-1,0))
	21	1	C21: =COLUMN(OFFSET(Footer,-1,0))

<tbody>
</tbody>

 

[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?

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

<tbody>
</tbody>

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 ?