CMV: In Excel, the formula for arithmetic mean should be "mean()" instead of "average()"
What does @ mean in an excel formula
Formula to calculate the mean batch of values in a column
Understanding quotation mark use in formulas
This is a longish way to answer your question.
The following is for entering text (also called "string") in a formula, where there is an = sign at the beginning. You can write a simple Hello in a cell and it will go right in, but if you put an = sign at the beginning, then you have to put it as a string, thus:
="Hello"
The opening and closing quotes here tell Excel that whatever in between them is the text that should appear. Now, if you want to put She said "hello" as that string, it gets a little complicated. If you put in
="She said "hello""
Excel will think that the beginning quote of hello is the ending quote of the string, and it will return an error because if can't figure out what the rest (the hello"" part) means. To get around this, you have to tell Excel to regard the quotes around hello as actual punctuation, and not as the markers for the string beginning or end, by doubling each quote, thus:
="She said ""hello"""
This way, Excel knows that the "" around the ""hello"" are simple punctuation notations, and not markers for the start or the end of the string. In this way, the text will appear in the cell as:
She said "hello"
I am going through this because the the INDIRECT function acts on strings, so anything in the parentheses in INDIRECT is a string and you have to be careful about the quotes. In the example you cited, the components in the string are the references D1 (which is the sheet name) and the range (A3:D6). The sheet and cell range are connected by the exclamation point. You connect these components with the ampersand (&)--that's the purpose of the ampersand. So the simplest listing for that string would be:
D1&"!"&"A3:D6"
In this example, the A3:D6 appears to be static, so we can just combine this with the ! sign:
D1&"!A3:D6"
However, just to be consistent with the example, let's keep using the D1&"!"&"A3:D6". Now, what's with the single quotes? Well, because the first part of the address is a sheet name, Excel adds single quotes to the sheet name if the sheet name is more than one word, to group them. See these examples:
=Sheet1!A1 =Global_Settings!A1:B10
but
='Sheet 1'!A1 ='Global Settings'!A1:B10
So when you are writing a string address that includes a sheet name, it's good practice to include the single quotes around the sheet name, even if the sheet name is only a one-word name. The single quotes will work for one-word or multi-word sheet names.
So back to the string in the INDIRECT function. You can see that now we have to:
-
Concatenate the single quote by telling Excel that it's a string, which means you use quotes around that;
-
Add the ampersand to connect to the sheet reference
-
Add the ampersand again to connect to the quotes around the single quote again (but here the exclamation point is lumped in with the ending single quote)
-
And then to connect with the rest of the string.
I've put in extra spaces to visually separate the components.
=INDIRECT( "'" & D1 & "'!" & "A3:D6" )
All this is so that the string is readable by Excel for whatever sheet name that might be part of that string. In turn, that means that the INDIRECT function can do its job.
Hope this helps.
More on reddit.comVideos
We all know there are multiple kinds of average: there's median, mode, geomean, arithmetic mean, etc. It would be less ambiguous to have the formula for arithmetic mean to be mean() instead of average(). It is also shorter to type, so would save time. I know a lot of other programming languages do it this way, so it would be less confusing overall.
Please note that I'm not advocating for removing the average() formula entirely. I'm arguing that this is how it should have been from the beginning. For the sake of backwards compatibility, we keep average() and have it do the same things as mean(). I think it's just insane that mean() isn't even a default formula in excel. It should be.
Also, before anyone says anything, I know that I can add my own formulas with defined names and lambda functions and stuff. I know. I'm just saying that mean() should be a default formula.