Sarbanes Oxley

[Gary_E]

Hey Guys :

Not happy but due to Sarbanes Oxley, all my excel files are being subjected to a ton of restrictions. One of the toughest ones is that I can’t use a ‘constant’ in a formula. For example, A1/1000 is no longer acceptable. The formula needs to be A1/B1 – Where B1 holds 1000. It sucks.

Anyone know of a study where changing a constant to reference cell increases memory usage & if so, % percentage increase

 

[Bob Rooney]

I do not know of any tests on the performance differences between constants vs. cell references holding constants, but I would guess it would be negligible.

In this case, you will have to bite the bullet and restructure your models to accommodate this requirement. It may be a load of work to change, but I would recommend you do it regardless of SOX restrictions. It is a far, far better way of working with Excel. The no constants in formulas rule is very sound advice, IMO.

If your example is common throughout your models, it appears that you are dividing by 1000 primarily to report the results in thousands -- for style reasons rather than substance reasons. If that is the case, you can eliminate the division and format the cell to display what you want (the value is preserved).

Example
A1 has 10,200
C1 has =A1/1000 ==> 10.2

Rather than placing 1000 in B1 and having =A1/B1 in C1, you can custom format C1 to something like #,##0.00,;[Red]-#,##0.00, (notice the trailing comma) or you can format A1 as such directly and dispense with the formula.

In any event, if there are common divisors, you can take advantage of Excel's Find/Replace functionality to do a lot of changes at once.

 

[Gary_E]

Thxs for the advice

Curently I'm exploring the use of hard coding values in range names & using them to get around using references....

 

[Bob Rooney]

Do you have any patterns to them? Give an example and I am sure a working solution can be offered.

 

[Von Pookie]

Duplicate. Please see:
http://www.mrexcel.com/board2/viewtopic.php?t=235217