Offset Function Help

Willmonbo

Board Regular
Joined
May 16, 2010
Messages
141
Here is the scenario that I need help with:

In Column "I" the values entered are always a calculation based on the values entered in column "E" the same row, so I figured that to make it easier I simply can create a name and use the following formula in the name definition:

=Offset(Cell("Address"),,-4,)*.3

but it doesn't work and I can't figure out why, would appreciate someone taking a look at it.

I tried to enter the formula directly into a cell and it doesn't work either, so I am aware that problem is not with it being a defined name but with the formula itself, nevertheless can't figure out what the problem is
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
The problem you have, specifically, is that CELL function returns a text string not a cell reference - you could wrap the CELL function in INDIRECT but I'm not sure why you can't just use a formula like this

=E2*0.3
 

Willmonbo

Board Regular
Joined
May 16, 2010
Messages
141
Thanks Barry,

it's not always the same calculation and the and it's not a simple *.3, I was just using it to illustrate the scenario.

thanks again I always like your creativity when mentioned either by Mr. Excel in his Podcasts or by ExcellIsFun in his YouTube clips.
 

Willmonbo

Board Regular
Joined
May 16, 2010
Messages
141
Barry,

Sorry to bother again with it, the "Indirect" makes it work but it still seems to be quite a volatile formula, I could be working somewhere else on the worksheet and suddenly Column "I" comes up with the "value" error, and then I click into one cell in column "I" with the "value" error and just hit the enter again and the entire column recalculates and is good, what could be the problem?
 
Last edited:

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825

ADVERTISEMENT

Both INDIRECT and OFFSET are volatile functions, best avoided if you can - I'm still not sure why you can't just reference the cell directly, even if you have different calculations.....
 

Willmonbo

Board Regular
Joined
May 16, 2010
Messages
141
it's not that I can't just reference the other cell, it's rather a convenience being that it's always one of four possible calculations and they all are at least 6 figures beyond the decimal position.

but since you say that these functions are inherently volatile I may just abandon it completely or perhaps if you have a suggestion of a different way to do it, i'd greatly appreciate it.

thanks much for your time and help.
 

Willmonbo

Board Regular
Joined
May 16, 2010
Messages
141
Barry,

I was thinking of an idea how to resolve the volatility problem with the formula I am using, and that is to have a macro constantly run in the background that whenever this formula is entered the macro should go into the cell and evaluate it (as if I would do F9 after entering the formula), I just don't know how to write VBA code, can you help me with that?
 

Forum statistics

Threads
1,141,017
Messages
5,703,752
Members
421,313
Latest member
Mooncake1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top