defined name - offset problem

[Helen]

Hi,

My spreadsheet has data from B3:K3. I've named that range as Labour_Sales
with =OFFSET(Metrics!$B$3,0,0,COUNTA(Metrics!$3:$3),1) in the Refers to box
so that every time i add a column the formula would update automatically. I
am using Excel 2007

In cell L3 i have a formula =AVERAGE (Labour_Sales). I got #DIV/0!. Why isnt
this working??

Pls help

 

[Bernard Liengme]

I think you will find that you have defined a range in a column not in a row
The syntax is =OFFSET(reference, rows, columns, height, width)
You want a height of 1 and a width of COUNT(......)
So change your definition to
=OFFSET(Metrics!$B$3, 0, 0, 1, COUNTA(Metrics!$3:$3))
best wishes

 

[Don Guillett]

You may want to re-visit your offset formula to count columns instead of
rows?

 

[Bernard Liengme]

Just noticed another problem:
You say you are putting this in cell L3. I hope this is not on the sheet
Metrics?
For then is in row 3 and will increment the COUNT
Maybe you need
=OFFSET(Metrics!$B$3,0,0, 1, COUNTA(Metrics!$B$3:$K$3) )
Wonder why you need a OFFSET to average?
This array formula average all non-blank cells
=AVERAGE(IF(Metrics!B3:K3<>"",Metrics!B3:K3))
best wishes

 

[Helen]

I tried yr = average(if()) line and i got #VALUE!

Maybe i am not approaching this right.

The word "Data" is in cell A3. 0.52% is in cell B3 etc (reading across then
down due to the way this paste) ending 0.22% in cell K3. I want to average
all the data from B3 to K3. When i insert a blank column in front of L I
would like the average to pick up that column so the new average should be
B3:L3. I thought using offset & defined name would accomplish what i want to
do but somehow it's not working for me. Maybe defined name is not good for me
bc i would need to come up with 52 names for my metric spreadsheet. Any
ideas?
Data 0.52% 0.52% 0.72% 0.31% 0.42% 0.43% 0.35% 0.41% 0.30% 0.22%

 

[Helen]

OMG i figured out the problem thanks to this website.

http://www.mvps.org/dmcritchie/excel/offset.htm

I rewrote the formula to =AVERAGE(B3:OFFSET(M3,0,-1). Everytime i enter a
column & put some data in the new column the formula pick up the new line.
Yay!!!!!