Nov 06 2021 08:06 PM
As you can see in column M, all of my values is inserted "Spill". I used the insert function and added my functions and once I clicked OK, It showed "Spill" in every row. How should I fix this problem??
Nov 06 2021 11:00 PM
@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.
Nov 06 2021 11:20 PM
Nov 07 2021 12:12 AM
@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.
Nov 07 2021 03:43 AM
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