SOLVED

IF Function #SPILL! help

%3CLINGO-SUB%20id%3D%22lingo-sub-1269286%22%20slang%3D%22en-US%22%3EIF%20Function%20%23SPILL!%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1269286%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20is%20for%20an%20Excel%20class.%20I%20am%20supposed%20to%20copy%20the%20formula%20exactly%20as%20the%20books%20says%20(where%20I've%20highlighted%20in%20green).%20The%20%22formula%20result%22%20is%20correct%20in%20the%20Function%20Arguments%20dialog%20box%20but%20when%20I%20click%20%22OK%2C%22%20I%20get%20the%20%23SPILL!%20error.%20Can%20anyone%20help%20me%20with%20this%20please%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20book%20is%20is%20based%20on%20Excel%202013%20and%20it%20is%20the%20required%20text%20for%20this%20class%20but%20maybe%20the%20newest%20version%20of%20Excel%20is%20preventing%20this%20formula%20from%20working%20properly%20because%20I%20am%20also%20getting%20an%20error%20message%20when%20typing%20the%20next%20step%20on%20the%20page.%20(%3CSPAN%3E%22Click%20cell%20K6.%20Type%20the%20formula%20%3D%5BAmount%5D%E2%80%93%5BDown_Pay%5D%20and%20press%20Enter.%22)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EAny%20help%20is%20appreciated.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E-B%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1269286%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-1269315%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Function%20%23SPILL!%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1269315%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F603395%22%20target%3D%22_blank%22%3E%40brandydowns%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYes%2C%20the%20formula%20has%20some%20assumptions%20which%20better%20to%20avoid%20even%20for%20Excel%202013.%20For%20the%20modern%20Excel%20that%20causes%20an%20error.%20More%20correct%20formula%20will%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIF(%5B%40%5BPaid_Type%5D%5D%3D%22Paid%20in%20Full%22%2C%20%5B%40Amount%5D%2C%5B%40Amount%5D*%24D%243)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EIn%20brief%2C%20%5BAmount%5D%20returns%20entire%20column%2C%26nbsp%3B%5B%40Amount%5D%20returns%20the%20value%20from%20that%20column%20for%20the%20current%20row.%202013%20returns%20the%20latest%20as%20well%20doing%20some%20silent%20work%2C%20but%20better%20to%20avoid%20such%20assumptions.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1269335%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Function%20%23SPILL!%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1269335%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BThank%20you%20so%20much!%20This%20worked%20perfectly!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1269346%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Function%20%23SPILL!%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1269346%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F603395%22%20target%3D%22_blank%22%3E%40brandydowns%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%2C%20glad%20to%20help%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

This is for an Excel class. I am supposed to copy the formula exactly as the books says (where I've highlighted in green). The "formula result" is correct in the Function Arguments dialog box but when I click "OK," I get the #SPILL! error. Can anyone help me with this please?

 

My book is is based on Excel 2013 and it is the required text for this class but maybe the newest version of Excel is preventing this formula from working properly because I am also getting an error message when typing the next step on the page. ("Click cell K6. Type the formula =[Amount]–[Down_Pay] and press Enter.")

 

Any help is appreciated.

-B

3 Replies
Highlighted
Solution

@brandydowns 

Yes, the formula has some assumptions which better to avoid even for Excel 2013. For the modern Excel that causes an error. More correct formula will be

=IF([@[Paid_Type]]="Paid in Full", [@Amount],[@Amount]*$D$3)

In brief, [Amount] returns entire column, [@Amount] returns the value from that column for the current row. 2013 returns the latest as well doing some silent work, but better to avoid such assumptions.

Highlighted

@Sergei Baklan Thank you so much! This worked perfectly!

Highlighted

@brandydowns , you are welcome, glad to help