Excel's golden rule

lezawang

Well-known Member
Joined
Mar 27, 2016
Messages
1,805
Office Version
  1. 2016
Platform
  1. Windows
Hi
I downloaded one of Mike Girvin sheet and he wrote the following:

"Excel;s Golden Rule: If a formula input can change, put it in a cell (with a label next to it) and refer to it in the formula with a cell reference"

I could not understand what he meant? Any help would be very much appreciated.
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
What it means is, if the cell referenced in a formula will never change you can hardcode the value instead of the reference !!
I think you may have that backwards. As I understand it, the "rule" is if you have a value in a formula that will or might change in the future, do not hard-code that value inside the formula... instead, put that value in a cell and replace the value in the cell with the address of the cell you put the value in. That way, when the value changes in the future, you don't have to look for the formula in order to change the value there, rather, you just have to change the value in the cell and the formula will automatically update for the change. The idea of the label is to tell yourself in the future what that number means (so you will know that it is the one that needs to change). A rough example would be a formula like this...

=8*24

where 24 is the number of employees (the formula gives the total man-hours available in a single workday. Since the number of employees may change in the future, the "rule" is to put the 24 in a cell and change the formula to reference the cell. So, let's say there is a sheet named Variables in the workbook where all the values that can vary will be placed. The suggestion is to put the 24 in a cell on the Variables sheet... let's say cell A9 (the assumption being cells A1:A8 already contain other values from formulas that were replaced with cell references according to the "rule"). So, you would put 24 in cell A9, put a description of the value(say, "Total number of employees in company") in cell B9 and change the formula to this...

=8*Variables!A9

Now, when the number of employees change in a year or two, you won't have to search the various formulas to find the number 24 that needs to be changed, rather, you simply go to the Variables sheet and search the descriptions until you find the one that says "Total number of employees in company" and change the 24 in the cell next to it.
 
Last edited:
Upvote 0
Yep, you're right Rick....cart before the horse:oops:
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,182
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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
Back
Top