We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Cell references

thirtywinter
thirtywinter asked
on
Medium Priority
257 Views
Last Modified: 2013-11-05
Gents and Ladies,

I'm a little bit embarrassed to ask such a seemingly easy question, but I have somewhat of an issue that is really getting under my skin here.

In one cell I have a straightforward and simple formula (For example: =(F2+G2+H2)/2))

My problem is that when i insert a a column the formula changes to =(G2+H2+I2)/2 regardless of the cell reference being absolute or not.  I would like the formula to always refer to the same cell no matter if I insert a column or not. (In other words, the formula to remain unchanged)

I'm absolutely stymied by this.

Please help.

:)

Thank you.
Comment
Watch Question

For that you have to use Offset formula for example if the above formula is in B2 then you can also use the following

=(OFFSET(B2,0,4,1,1)+OFFSET(B2,0,5,1,1)+OFFSET(B2,0,6,1,1))

Sid
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
You can also use the INDIRECT function of Excel

=SUM(INDIRECT("F2:H2"))/2

Author

Commented:
Worked like a champ! Thank you very much!

I was starting to wonder if I was loosing my mind or something.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.