SOLVED

Spill Error with IF function

%3CLINGO-SUB%20id%3D%22lingo-sub-1296035%22%20slang%3D%22en-US%22%3ESpill%20Error%20with%20IF%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1296035%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20working%20on%20a%20school%20excel%20project%20and%20I%20keep%20getting%20the%20spill%20error.%26nbsp%3B%20I%20have%20included%20a%20screenshot%20of%20my%20excel%20spreadsheet.%26nbsp%3B%20Would%20anyone%20be%20able%20to%20help%20me%20with%20this%3F%20%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20book%20is%20telling%20me%20to%20type%20%3Dif%20(%20d9%3D%22Warranty%22%2C0%2C%5BTime%20Billed%5D%20*%2075)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1296035%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1296384%22%20slang%3D%22en-US%22%3ERe%3A%20Spill%20Error%20with%20IF%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1296384%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F616062%22%20target%3D%22_blank%22%3E%40acoffee21%3C%2FA%3E%26nbsp%3B%20Tried%20it%20but%20no%20error.%20Could%20yo%20please%20share%20the%20file.%3F%20Formula%20is%20correct.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20the%20reference%20you%20can%20also%20refer%20here.%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fgyankosh.net%2Fmsexcel%2Ffunctions%2Flogical%2Fexcel-function-if%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fgyankosh.net%2Fmsexcel%2Ffunctions%2Flogical%2Fexcel-function-if%2F%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1297552%22%20slang%3D%22en-US%22%3ERe%3A%20Spill%20Error%20with%20IF%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1297552%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F616062%22%20target%3D%22_blank%22%3E%40acoffee21%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EObviously%2C%20you%20are%20learning%20about%20structured%20table%20references.%20Then%20this%20is%20the%20formula%20you%20need.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIF(%5B%40%5BService%20Type%5D%5D%3D%22Warranty%22%2C0%2C%5B%40%5BTime%20Billed%5D%5D*75)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EMay%20be%20a%20bit%20confusing%20in%20the%20beginning%20with%20the%20%22%40%22-signs%20and%20the%20square%20brackets%2C%20but%20if%20you%20build%20the%20formula%20by%20pointing%20at%20the%20cell%20within%20the%20table%2C%20it%20will%20automatically%20become%20like%20that.%20The%26nbsp%3B%22%40%22-sign%20tills%20it%20to%20only%20look%20at%20the%20cell%20on%20the%20current%20row.%26nbsp%3BOf%20course%2C%20you%20can%20leave%20the%20%22D9%22%20reference%20in%20place%2C%20but%20then%20you%20might%20as%20well%20use%20%22E9%22%20for%20the%20time%20billed%20part%20of%20the%20formula.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Visitor

I am working on a school excel project and I keep getting the spill error.  I have included a screenshot of my excel spreadsheet.  Would anyone be able to help me with this?  

My book is telling me to type =if ( d9="Warranty",0,[Time Billed] * 75)

2 Replies
Highlighted

@acoffee21  Tried it but no error. Could yo please share the file.? Formula is correct.

 

For the reference you can also refer here.

https://gyankosh.net/msexcel/functions/logical/excel-function-if/

 

Highlighted
Solution

@acoffee21 

Obviously, you are learning about structured table references. Then this is the formula you need.

=IF([@[Service Type]]="Warranty",0,[@[Time Billed]]*75)

May be a bit confusing in the beginning with the "@"-signs and the square brackets, but if you build the formula by pointing at the cell within the table, it will automatically become like that. The "@"-sign tills it to only look at the cell on the current row. Of course, you can leave the "D9" reference in place, but then you might as well use "E9" for the time billed part of the formula.