Apr 25 2020 03:23 PM
I'm working on a project for my Data Analytics class for college. I have to insert a formula
under the Loan Commission
=if([Specialty]="Loans",[Account Values]*0.0025,0)
and another formula under the New Money Commission
=if([Specialty]="Loans",0,[Account Values]*0.0002)
I enter both of them in and the #Spill! the error came up twice. Before I insert the formulas I check and see if the range was empty of any data and it was but still the error came up. I've been trying to figure out how to get the error away so I can finish my assignment for the past couple of hours.
Please give me suggestions, solutions, anything to fix this.
Thanks for the help
Apr 25 2020 03:32 PM
@jb0y1e7 is it possible to send an excel file so we can trace the error together.
#SPILL errors are returned when a formula returns multiple results, and Excel cannot return the results to the grid.
Apr 25 2020 05:08 PM
You formula refers to a whole column instead of a single cell:
=if([@Specialty]="Loans",[@[Account Values]]*0.0025,0)
Apr 25 2020 07:39 PM
Hi @jb0y1e7
As @Detlef Lewin pointed out, you only need to refer to a cell and the formula is copied to other cells rather than referring to the whole column. Also, you do not need to use an array formula.
I have attached the updated file.
Cheers.
Mar 22 2021 09:08 AM
@wumoladYou have no idea how helpful this is, I have been trying to complete an assignment with this specific formula for days now, and I haven't been able to figure it out. Thank you so much for explaining this!
Apr 23 2021 03:30 PM
@Detlef Lewin I am having the same problem with the #spill error for the formula using 0.0002, could you please help me out here?
Apr 23 2021 03:47 PM