Forum Discussion
Error "SPILL"
michaellajakala It looks like you have entered a dynamic array formula in a structured table. Such formulae spill the result into multiple cells at once. If something is in it's way, you get the #SPILL error. Structured tables automatically copy a formula entered in the first cell of a column down the entire column, thereby blocking the spill range for the dynamic array. You need to change the formula, but exactly how is difficult to explain as you didn't include the formula you currently use.
- There is nothing that is in the way to cause “Spill” This is the exact questions I’m having trouble with.
1. Click IF in the Select a function box, and then click the Function Arguments dialog box opens.
2. In the Logical_test box, type OR([Store]=“Bonham”,[Store]=“Graham”) to enter the OR function with structured references.
3. In the Value_if_truealue_if_true box, type [Current Salary]*0.035 and then press the Tab key.
4. In the VValue_if_false box, type [Current Salary]*0.025.
After I press okay all the rows are saying spill.
What should I do to fix the Spill error ?if you have multiple instances of :
. In the Logical_test box, type OR([Store]=“Bonham”,[Store]=“Graham”) to enter
in the excel formatted table, you're going to get a spill error.
excel formatted tables do not support array formulas all you're going to get is a #Spill error
try converting the table into a range.
cheers
michaellajakala You still didn't give me the exact formula, so I'm going to try and reconstruct it from your latest response.
=IF(OR([Store]="Bonham",[Store]="Graham"),[Current Salary]*0.035,[Current Salary]*0.025)This will look at the entire column "Store" and spills the results as a dynamic range. But when you enter the formula in the table, it copies itself down the entire column. Thereby, blocking its own spill range.
Try it this way:
=IF(OR([@Store]="Bonham",[@Store]="Graham"),[@[Current Salary]]*0.035,[@[Current Salary]]*0.025)Note the ampersands in front of each of the column names. They will appear in the formula as you build it by pointing at the cells.
