Forum Discussion
excel in windows 10.
- Hi, What do you mean by 'order from 1 to 20'? What is 1, what is 30? Kind regards Hans
I see that absolute references static arrays comes more and more handy.
Using CHOOSE allows to combine multiple dynamic arrays.
=SORT(CHOOSE({3\1\2}; Table1[Text]; Table1[Value]; Table1[Rank]))
Also noted that RANK is around for backwards compatibility.
[x] Like Tables
I do though stress, during classes, that when you work with column formulas you enter it......and in one step gets the formula, a new column and the column filled with that formula.
When undoing though, it demands three Ctrl+Z. One for each step.
The risk is when making a less visible change and typing Ctrl+Z, it is very easy to not have undone the change as expected. In the rest of the envirnment it is normally one change and one undo.
The only hint is a tiny green corner telling that the cell deviates.I don't have any other suggestion for the interface - I'm confident that Microsoft have come up with the best solution among alternatives. Just notice that it is an area with a risk for unwanted results.
IMHO, that's always the risk to use the tool by the way it's not designed for. Structured table assumes same formula for entire column and keeps it as default formula for the column in background.
- Definately prefer structured tables 🙂
When it comes to calculated columns it may be a safer approach to simply delete the column and make a new one. Ctrl+Space and Ctrl+- (minus) and possibly Ctrl++ comes handy.
It can be something of a pitfall. As a 365 dynamic array user, I tend to expect consistency and follow Ray Panko (EuSpRIG papers) in treating any individually edited term as a Latent Error (i.e. one that may deliver the correct numbers now but with no assurance that it will continue to do so during the life of the spreadsheet). I would prefer that all edits should propagate and there should be no option to change a single term, but that would cause howls of protest.
It is not as if any other aspect of traditional spreadsheet development makes an attempt to be robust to change, so the Microsoft default is probably a good solution, though you are correct to warn against the risk.
Working my way, I would have
= IF([@precision]="high", PI(), 355/113)which, I agree, is somewhat cumbersome.
- Microsoft has surely thought about it, but it would be appreciated by some of us to have a setting for calculated columns like
[x] All edits propagate
