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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
EirmanChief Operations ManagerCommented:
I haven't been following your earlier questions.
Can you post an example or give more details
0
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.