#Spill! EXCEL

%3CLINGO-SUB%20id%3D%22lingo-sub-1338051%22%20slang%3D%22en-US%22%3E%23Spill!%20EXCEL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1338051%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20working%20on%20a%20project%20for%20my%20Data%20Analytics%20class%20for%20college.%20I%20have%20to%20insert%20a%20formula%26nbsp%3B%3C%2FP%3E%3CP%3Eunder%20the%20Loan%20Commission%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3Dif(%5BSpecialty%5D%3D%22Loans%22%2C%5BAccount%20Values%5D*0.0025%2C0)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eand%20another%20formula%26nbsp%3Bunder%20the%20New%20Money%20Commission%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3Dif(%5BSpecialty%5D%3D%22Loans%22%2C0%2C%5BAccount%20Values%5D*0.0002)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20enter%20both%20of%20them%20in%20and%20the%20%23Spill!%20the%20error%20came%20up%20twice.%20Before%20I%20insert%20the%20formulas%20I%20check%20and%20see%20if%20the%20range%20was%20empty%20of%20any%20data%20and%20it%20was%20but%20still%20the%20error%20came%20up.%20I've%20been%20trying%20to%20figure%20out%20how%20to%20get%20the%20error%20away%20so%20I%20can%20finish%20my%20assignment%20for%20the%20past%20couple%20of%20hours.%3C%2FP%3E%3CP%3EPlease%20give%20me%20suggestions%2C%20solutions%2C%20anything%20to%20fix%20this.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20the%20help%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1338051%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1338064%22%20slang%3D%22en-US%22%3ERe%3A%20%23Spill!%20EXCEL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1338064%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F637027%22%20target%3D%22_blank%22%3E%40jb0y1e7%3C%2FA%3E%26nbsp%3Bis%20it%20possible%20to%20send%20an%20excel%20file%20so%20we%20can%20trace%20the%20error%20together.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22x-hidden-focus%22%3E%23SPILL%20errors%20are%20returned%20when%20a%20formula%20returns%20multiple%20results%2C%20and%20Excel%20cannot%20return%20the%20results%20to%20the%20grid.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1338091%22%20slang%3D%22en-US%22%3ERe%3A%20%23Spill!%20EXCEL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1338091%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F622486%22%20target%3D%22_blank%22%3E%40wumolad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1338117%22%20slang%3D%22en-US%22%3ERe%3A%20%23Spill!%20EXCEL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1338117%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F637027%22%20target%3D%22_blank%22%3E%40jb0y1e7%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20formula%20refers%20to%20a%20whole%20column%20instead%20of%20a%20single%20cell%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3Dif(%5B%40Specialty%5D%3D%22Loans%22%2C%5B%40%5BAccount%20Values%5D%5D*0.0025%2C0)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1338120%22%20slang%3D%22en-US%22%3ERe%3A%20%23Spill!%20EXCEL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1338120%22%20slang%3D%22en-US%22%3EThanks%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1338177%22%20slang%3D%22en-US%22%3ERe%3A%20%23Spill!%20EXCEL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1338177%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F637027%22%20target%3D%22_blank%22%3E%40jb0y1e7%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%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%3Bpointed%20out%2C%20you%20only%20need%20to%20refer%20to%20a%20cell%20and%20the%20formula%20is%20copied%20to%20other%20cells%20rather%20than%20referring%20to%20the%20whole%20column.%20Also%2C%20you%20do%20not%20need%20to%20use%20an%20array%20formula.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20attached%20the%20updated%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2227894%22%20slang%3D%22en-US%22%3ERe%3A%20%23Spill!%20EXCEL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2227894%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F622486%22%20target%3D%22_blank%22%3E%40wumolad%3C%2FA%3EYou%20have%20no%20idea%20how%20helpful%20this%20is%2C%20I%20have%20been%20trying%20to%20complete%20an%20assignment%20with%20this%20specific%20formula%20for%20days%20now%2C%20and%20I%20haven't%20been%20able%20to%20figure%20it%20out.%20Thank%20you%20%3CEM%3Eso%3C%2FEM%3E%20much%20for%20explaining%20this!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2283213%22%20slang%3D%22en-US%22%3ERe%3A%20%23Spill!%20EXCEL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2283213%22%20slang%3D%22en-US%22%3E%3CP%3E%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%3BI%20am%20having%20the%20same%20problem%20with%20the%20%23spill%20error%20for%20the%20formula%20using%200.0002%2C%20could%20you%20please%20help%20me%20out%20here%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2283232%22%20slang%3D%22en-US%22%3ERe%3A%20%23Spill!%20EXCEL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2283232%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F637027%22%20target%3D%22_blank%22%3E%40jb0y1e7%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20formulas%20are%20valid%20provided%20they%20are%20not%20entered%20within%20a%20table.%3C%2FP%3E%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%20334px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F275214iB67DA9D90A34572B%2Fimage-dimensions%2F334x328%3Fv%3Dv2%22%20width%3D%22334%22%20height%3D%22328%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2283234%22%20slang%3D%22en-US%22%3ERe%3A%20%23Spill!%20EXCEL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2283234%22%20slang%3D%22en-US%22%3EThank%20you%20so%20much!%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
New Contributor

 

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

9 Replies

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

@jb0y1e7 

You formula refers to a whole column instead of a single cell:

=if([@Specialty]="Loans",[@[Account Values]]*0.0025,0)
Thanks

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.

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

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

@jb0y1e7 

The formulas are valid provided they are not entered within a table.

image.png

Thank you so much!