Sarbanes Oxley

Status
Not open for further replies.

Gary_E

Board Regular
Joined
Feb 18, 2002
Messages
108
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
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Bob Rooney

Board Regular
Joined
Jul 25, 2006
Messages
169
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

Board Regular
Joined
Feb 18, 2002
Messages
108
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

Board Regular
Joined
Jul 25, 2006
Messages
169
Do you have any patterns to them? Give an example and I am sure a working solution can be offered.
 
Status
Not open for further replies.

Watch MrExcel Video

Forum statistics

Threads
1,133,453
Messages
5,658,866
Members
418,473
Latest member
shaztastic73

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