Forum Discussion
MicrosoftHiding constant values in formulas
Hello all,
I'm fairly new to Excel and was wondering if there is a way to hide a constant value in a formula or perhaps re-write the formula so the constant value does not display on the sheet as seen below?
2 Replies
There are multiple ways to accomplish that. And you are to be commended for asking the question. One of the ground rules of good formulas is to avoid "hard-coding" variables (even if "constant") into formulas; even constants change from time to time. I notice in your image that you're only asking about the second 8; as such, it's not clear if they're really the same--i.e., if one changes so does the other--or not. I'll just address your question, but the advice to avoid hard-coding of variables applies really to both.
The simplest way is to have a single cell reference, placing it off to the side (maybe even on another sheet) and make it absolute rather than relative (also important terms to learn).
=((D4-C4)*8)+$K$2
Another is via a "named range" -- this latter has the advantage of making the formula easier to read.
=((D8-C8)*8)+BaseHrs
See the attached spreadsheet. And feel free to come back with questions. (Note too, those are hyperlinks above in blue, links to a helpful website that explains both concepts)
Then there are ways, in cases where that variable changes based on multiple factors (length of service, age, whatever) and then a table with lookup functions can come into play. We'll leave that for a later time.
agristwoodmathetes this is all great information. Thank you for your assistance!
