#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
Highlighted
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

5 Replies
Highlighted

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

Highlighted
Highlighted

@jb0y1e7 

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

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

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.