Hi. In our last tutorial on Named Ranges, we went over how to create a named range for a cell or a range of cells and then ways that you
could use this to save time by referencing the named range. But what if you need to create a named range that calculates a formula? In this
Excel tutorial we're going to go over how to make named ranges with formulas.
On my screen is a list of clients and the value of the amount they purchased. Most of them will also get a discount and all will pay a tax.
There are few ways we can use named ranges with formulas to calculate what they owe us. You can make a named range that contains a
formula that references another cell. So, let's create a named range that calculates the Discounted Dollars.
So, to do this you'll need access the Name Manager, which is under Formulas /
Name Manager. And, here, we'll click New and we'll call this "DiscDollars."
And, here, you can either just type your calculation in, copy and paste it if it was already in your spreadsheet, or
we can use this option to just select our cells. So this will be the Value Purchased x negative the Discount Dollars
and, notice that if you use this option, the cells referenced are, by default, absolute values.
So we'll need to get rid of the $.
And click OK when you're done.
And now, we can use our DiscDollar named range
anywhere we want to reuse this same calculation.
And, by the way, if you're finding this tutorial helpful, please don't forget the click Like and subscribe to this channel.
We can also make a named range that includes a function. So let's calculate the Discount %. And, remember that each client
has a different percent discount and some clients have no discount. So, I'm going to use a combination of
IFERROR and VLOOKUP to show the discount for each transaction.
And then, once I have my formula written out, I'm simply going to highlight what's in my Formula Bar,
copy it...click Escape to get out of that...and then, in the Name Manager,
click New, we'll call this "DiscPercent,"
and, in the Refers To field box, I'm going to delete what's there and paste my new formula.
Close out of that. And then, now, we can use the DiscPercent named range
in other cells to help us use the same formula
to get the discount percent for each client.
One thing to note here is that, even though the scope of the named ranges we've created has been set to Workbook and we've only been
entering the cell addresses in the Refers To field box, this is still defaulting to Sheet1. So, if you want to reuse these in a different sheet,
you'll need to make a copy of them and reference the new sheet name.
So, you can also create named ranges with calculations that reference other named ranges. So, for example,
let's name this Tax
and then create a named range that we can use to calculate the Invoice Total.
Click New and we'll call this
"InvTotal."
And we'll say equals the Value Purchased + the Discount Dollars, which we know is already negative, times
1 + Tax
and click OK. Close out of this. And, now, we can reference the InvTotal named range
to calculate the Invoice Total.
I hope you enjoyed this tutorial on how to make a named range with a formula in Microsoft
Excel. If you did, don't forget to Like the video and subscribe to this channel. Thanks!