Need help with #SPILL! issue

%3CLINGO-SUB%20id%3D%22lingo-sub-1841383%22%20slang%3D%22en-US%22%3ENeed%20help%20with%20%23SPILL!%20issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1841383%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20all%2C%20I%20am%20trying%20to%20do%20my%20home%20work%20and%20even%20after%20following%20the%20instructions%20in%20the%20book%20for%20the%20practice%20problem%20I%20am%20having%20an%20error%20that%20I%20don't%20know%20how%20to%20fix.%20I%20was%20hoping%20that%20some%20one%20could%20look%20at%20it%20and%20let%20me%20know%20what%20I%20did%20wrong....%20Thanks%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20would%20be%20apricated.%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELeAnna%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1841383%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1841418%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20%23SPILL!%20issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1841418%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F853023%22%20target%3D%22_blank%22%3E%40nightstar73%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20missed%20the%26nbsp%3B%40%20sign.%20It%20refers%20to%20the%20current%20row.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DIF(OR(%5B%40Store%5D%3D%22Bonham%22%2C%5B%40Store%5D%3D%22Graham%22)%2C%5B%40%5BCurrent%20Salary%5D%5D*0.035%2C%5B%40%5BCurrent%20Salary%5D%5D*0.025)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1841481%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20%23SPILL!%20issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1841481%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F853023%22%20target%3D%22_blank%22%3E%40nightstar73%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20really%20want%20to%20use%20spilt%20dynamic%20arrays%2C%20the%20calculation%20has%20to%20be%20performed%20externally%20to%20the%20table%20because%20tables%20and%20multi-cell%20DA%20are%20not%20compatible.%20The%20formula%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D%20IF(%0A%20%20(EmployeeTbl%5BStore%5D%3D%22Bonham%22)%2B(EmployeeTbl%5BStore%5D%3D%22Graham%22)%2C%0A%20%20%20EmployeeTbl%5BCurrent%20Salary%5D%20*%200.035%2C%0A%20%20%20EmployeeTbl%5BCurrent%20Salary%5D%20*%200.025%20)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Eshows%20a%20number%20of%20changes.%26nbsp%3B%20The%20references%20to%20the%20Table%20are%20now%20full%20column%20references%20and%20the%20Boolean%20OR%20function%20becomes%20a%20numeric%20%2B%20(plus)%20operation.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1841502%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20%23SPILL!%20issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1841502%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F853023%22%20target%3D%22_blank%22%3E%40nightstar73%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20a%20comment%2C%20this%20block%2C%20as%20entire%20related%20university%20course%20was%20designed%20for%20Excel%202016.%20If%20follow%20instructions%20like%20this%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20697px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F230701i95B250EEADAFB1A5%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eit%20works%20in%20Excel%20which%20doesn't%20support%20dynamic%20arrays%20(2016%2C%202019).%20More%20modern%20Excel%20on%20subscription%20model%20(365)%20requires%20more%20careful%20building%20of%20formulas%2C%20in%20this%20case%20as%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3Bsuggested.%20At%20the%20same%20time%20it%20gives%20more%20possibilities%2C%20with%20next%20course%20on%20dynamic%20arrays%20you%20may%20use%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3Badvice.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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

5 Replies
Highlighted

@nightstar73 

You missed the @ sign. It refers to the current row.

=IF(OR([@Store]="Bonham",[@Store]="Graham"),[@[Current Salary]]*0.035,[@[Current Salary]]*0.025)

 

Highlighted

@nightstar73 

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.

Highlighted

@nightstar73 

As a comment, this block, as entire related university course was designed for Excel 2016. If follow instructions like this

image.png

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.

Highlighted

@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. 

Highlighted

@nightstar73 

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.