Written by Jane Hames.
Last week, one of my Excel workshop participants said they were confused when looking at Excel formulas with quotes, exclamation marks, colons and commas. I thought that my response was worth sharing as I’m sure he’s not the only one to be confused by this.
The formula below, is calculating the average of the values in cells in a different WORKBOOK across two different WORKSHEETS.
=AVERAGE(‘[Sales Report.xlsx]1st qtr’!B4:D4,’[Sales Report.xlsx]2nd qtr’!B4:D4)
- The workbook name is within square brackets
[ ] - The workbook and the worksheet names have the single quotation marks
'around them. - The exclamation mark
!always follows the name of the worksheet. - The colon
:between the cell references means that B4, D4 and all cells in between are included. - The commas
,separate the two different ranges used.
