Forum Discussion
Spill error with IF function
When i added the following data, =IF([Specialty]="Loans",[Account Values]*0.0025,0) I keep getting a Spill error and I am unsure why. I was following my professors steps and for her it works fine.
2 Replies
A point of clarification - that type of formula used to work inside a structured table. But, when MS pushed the update for office 365 to implement dynamic array as the default formula evaluation method, it no longer works and you have to enter the formula as Riny indicated.
I think that some teachers and textbooks are not up to date on the latest Excel version, so it is possible it may work for the instructor, but not the students (depending on what version each is using).Edit: Though I think there should be an extra set of brackets around Account Values.
=IF([@Specialty]="Loans",[@[Account Values]]*0.0025,0)
Johann_six You can't use dynamic array formulae inside a structured table.
Try it this way:
=IF([@Specialty]="Loans",[@Account Values]*0.0025,0)
It will calculate the Commission row-by-row in stead of trying to spill an array.
.png){kind=link}