Dynamic Formula - Ignore cell Formula is in

I should have tested the formula fully. When I added the formula =average(data) I get an error because the cell that contains the formula is on the same line in the named range. How can I ignore the cell that my formula is in?
LVL 19
Edward PamiasTeam Lead RRS DeskAsked:
Who is Participating?
 
Saurabh Singh TeotiaCommented:
epamias

If you are applying formula in the same row from where the dynamic range is created then it will give you circular error since that is part of calculation of range only which you are further using it again...

If you want to apply in the same row then you need to use it a different technique for it.. Enclosed is the file for your reference where i have applied this dynamic formula on range highlighted by yellow color.. This will give you average from B3.. till one last column on the left hand side where you are applying this...

Saurabh...
dyanmic-formula.xlsx
0
 
EirmanChief Operations ManagerCommented:
You are probably getting a circular reference error.

The simplest and most obvious solution, is to add your formula outside the named range.

You could redefine the range to exclude the cell in question.
0
 
Edward PamiasTeam Lead RRS DeskAuthor Commented:
I would love to do what you said but I can't do it. Any other suggestions? BTW, as for this suggestions "You could redefine the range to exclude the cell in question. " that wont work because that column moves every time I add a column.
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
EirmanChief Operations ManagerCommented:
I haven't been following your earlier questions.
Can you post an example or give more details
0
 
Edward PamiasTeam Lead RRS DeskAuthor Commented:
If I could give you a 1000 points I would. This was perfect. Thanks for all the help in the last couple of days.
0
 
Saurabh Singh TeotiaCommented:
epamias,

Thanks for appreciation and always happy to help .. :-)

Saurabh...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.