Oct 31 2020 12:24 PM
Hello all, I am trying to do my home work and even after following the instructions in the book for the practice problem I am having an error that I don't know how to fix. I was hoping that some one could look at it and let me know what I did wrong.... Thanks :)
Any help would be apricated. :)
LeAnna
Oct 31 2020 12:35 PM
You missed the @ sign. It refers to the current row.
=IF(OR([@Store]="Bonham",[@Store]="Graham"),[@[Current Salary]]*0.035,[@[Current Salary]]*0.025)
Oct 31 2020 02:06 PM
If you really want to use spilt dynamic arrays, the calculation has to be performed externally to the table because tables and multi-cell DA are not compatible. The formula
= IF(
(EmployeeTbl[Store]="Bonham")+(EmployeeTbl[Store]="Graham"),
EmployeeTbl[Current Salary] * 0.035,
EmployeeTbl[Current Salary] * 0.025 )
shows a number of changes. The references to the Table are now full column references and the Boolean OR function becomes a numeric + (plus) operation.
Oct 31 2020 02:50 PM
As a comment, this block, as entire related university course was designed for Excel 2016. If follow instructions like this
it works in Excel which doesn't support dynamic arrays (2016, 2019). More modern Excel on subscription model (365) requires more careful building of formulas, in this case as @Detlef Lewin suggested. At the same time it gives more possibilities, with next course on dynamic arrays you may use @Peter Bartholomew advice.
Oct 31 2020 04:37 PM
@Sergei Baklan Yes that is the pic from my book :)
I still don't understand what I did wrong, I copied the formula from the book exactly. It didn't have an @symbols like one person had suggested so I'm not sure that I needed them in this version.
Oct 31 2020 05:07 PM
You clearly do need them in this version. You are following instructions written for traditional Excel which changed your references to a column to be a reference to the single cell on the same row as the formula. To reference the entire column you used to need to commit the formula using Ctrl+Shift+Enter (an array formula).
Now it is the other way round. Excel 365 will default to the array formula and only return a single cell if you instruct it to do so using the '@' operator. This comes as a shock to many Excel users but it enables solutions to be build far more logically.