4 ms·

Google Sheets has this, Excel doesn't?

• zamadatix 4y ago
  I hadn't seen this before but when I went to try I couldn't get it to work in Google sheets, am I using the wrong syntax? Typing "=SUM(A1)" just gave me the sum of A1 not A1 through the rest of the data in the column while similarly "=SUM(A1:)" resulted in an error. "=SUM(A1:A)" gives me the whole column as expected, not stopping when the data breaks after A11.
  • Closi 4y ago
    This is the correct behaviour, =SUM(A1) should give you A1 in both Excel and Google Sheets. You are passing in a single cell into SUM, so only asking it to add that cell up (not the cells below). You must refer to a range of cells for it to work - in Excel this would be for instance =SUM(A:A). In Google Sheets: generally you write a formula and hit Ctrl+Shift+Enter which will turn it into an array formula. In Excel there are many more options in how to handle this, one of which is to define a table in Excel (Insert -> Table) and then refer to the table and column (=TableName[Column]).
• Closi 4y ago
  It does - and you can do it multiple ways. The obvious way to do it is as follows: * Setup an excel sheet with "Name" and "Age" in A1 and B1, then put some sample data in. * Highlight the data and go insert -> table and hit 'ok' * Where it says Table Name: put in "Users" If we want to refer to the array of names in that table, you would now say =Users[Name] in a formula. As another example, if we wanted to find the average age, we could simply say =Average(Users[Age]) passing in the ages as an array. If we made another two columns - MathsScore and EnglishScore, we can either automatically create a column adding these up ([@MathsScore]+[@EnglishScore]), or alternatively these ranges can then be used in spill formulas.