3 ms·
They should really add the concept of array. Now you have to type "B2:B11" to refer to a range of cells, but why not "B2", and let the ":B11" part be implicit f
by amelius 4y ago
They should really add the concept of array. Now you have to type "B2:B11" to refer to a range of cells, but why not "B2", and let the ":B11" part be implicit from the structure of the sheet?
- andrewmcwatters 4y agoGoogle Sheets has this, Excel doesn't?
- zamadatix 4y agoI 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 agoThis 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 agoIt 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.
- wolpoli 4y agoI might be missing something obvious, but wouldn't B2 be ambiguous, as it could refer to either B2:B11 or say B2:H2?
- amelius 4y agoThe obvious thing you are missing is that you'd have to define it in some other way, e.g. by selecting the cell and clicking "Define array" or something along these lines.
- Closi 4y agoYou either make a table and refer to a column, or define the array in the cell and then refer to it with a hash (i.e. B2# refers to the array of cells stored/referenced in B2).
- idiotsecant 4y agoI think excel has named ranges, which is a little bit like what you're describing I guess.
- heisenzombie 4y agoThere are array formulas: https://support.microsoft.com/en-us/office/guidelines-and-examples-of-array-formulas-7d94a64e-3ff3-4686-9372-ecfd5caa57c7 https://support.microsoft.com/en-us/office/guidelines-and-ex... There have actually been a couple of tries at them. The modern “dynamic” array formulas are a little less fiddly.
- Closi 4y agoYou can, but you need to make a table then refer to the table column.