Problems with IF function, unable to use column selection

%3CLINGO-SUB%20id%3D%22lingo-sub-3535626%22%20slang%3D%22en-US%22%3EProblems%20with%20IF%20function%2C%20unable%20to%20use%20column%20selection%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3535626%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EI%20have%20to%20complete%20an%20assignment%20using%20Excel%20for%20my%20online%20class%20and%20the%20class%20wants%20me%20to%20use%20a%20very%20specific%20formula%20inorder%20to%20fill%20the%20table.%3C%2FP%3E%3CP%3EThe%20textbook%20wants%20me%20to%20use%20this%20formula%3A%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIF(%5B%40%5BSpecialty%5D%3D%E2%80%9CLoans%E2%80%9D%2C%20%5B%40%5BAccount%20Values%5D%5D*.0025%2C0)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3Ethe%20assignment%20says%20to%20enter%20the%20%5Bthen%20double%20click%20the%20column%20from%20the%20drop%20down%20list%20to%20select%20the%20appropriate%20column%20for%20the%20formula.%20When%20I%20attempt%20to%20do%20this%20it%20doesn't%20put%20the%20column%20title%20Specialty%20or%20Account%20values%20into%20the%20formula%20its%20just%20inserts%20Column%203%20and%20Column%204%20respectively.%20When%20I%20hit%20enter%20it%20gives%20me%20a%20syntax%20error.%20When%20I%20directly%20copy%20paste%20the%20formula%20into%20the%20excel%20from%20my%20textbook%20it%20gives%20me%20a%20syntax%20error.%20I%20tried%20playing%20with%20the%20selection%20and%20instead%20of%20selecting%20the%20column%20I%20selected%20the%20cells%20and%20no%20matter%20what%20I%20do%20I%20only%20get%20Syntax%20errors%2C%20%23Name%3F%2C%20and%20%23Spill!%20errors.%20I%20can't%20figure%20out%20how%20to%20fix%20this.%20I%20tried%20looking%20online%20at%20other%20support%20forums%20and%20the%20people%20there%20were%20just%20saying%20to%20make%20a%20whole%20new%20workbook%2Ftable%20which%20is%20not%20an%20option%20as%20this%20excel%20file%20is%20provided%20by%20the%20textbook%20and%20I%20can't%20just%20make%20my%20own.%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3535626%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-3536504%22%20slang%3D%22en-US%22%3ERe%3A%20Problems%20with%20IF%20function%2C%20unable%20to%20use%20column%20selection%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3536504%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1431936%22%20target%3D%22_blank%22%3E%40RRouse34%3C%2FA%3E%26nbsp%3BI%20wouldn't%20be%20able%20to%20follow%20that%20instruction%20either%20as%20it%20is%20wrong.%20You%20are%20obviously%20learning%20about%20how%20to%20use%20structured%20table%20reference.%20What%20you%20do%20is%20the%20following%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFirst%20make%20sure%20that%20you%20have%20a%20structured%20table%20with%20a%20column%20called%20Specialty%20and%20another%20called%20Account%20Values.%3C%2FP%3E%3CP%3EIn%20the%20cell%20(usually%20on%20the%20first%20row%20below%20the%20headers)%20in%20another%20(empty)%20column%20in%20the%20table%2C%20start%20typing%20%3CSTRONG%3E%3DIF(%3C%2FSTRONG%3E%20and%20point%20at%20the%20cell%20on%20the%20same%20row%20in%20the%20%3CU%3ESpecialty%3C%2FU%3E%20column.%20That%20will%20automatically%20insert%20%3CSTRONG%3E%5B%40Specialty%5D%3C%2FSTRONG%3E.%3C%2FP%3E%3CP%3E%3CEM%3E(Note%20that%20there%20is%20one%20square%20bracket%20too%20many%20in%20the%20textbook%20formula).%26nbsp%3B%3C%2FEM%3EContinue%20typing%20the%20formula%20and%20when%20you%20come%20to%20entering%20the%20reference%20for%20the%20%3CU%3Eaccount%20value%3C%2FU%3E%2C%20just%20click%20on%20the%20correct%20cell%20and%26nbsp%3B%3CSTRONG%3E%5B%40%5BAccount%20Values%5D%5D%3C%2FSTRONG%3E%20will%20be%20added.%20Finish%20typing%20the%20formula%20and%20press%20enter.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20should%20end%20up%20with%20this%2C%20down%20the%20entire%20column%3A%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIF(%5B%40Specialty%5D%3D%E2%80%9CLoans%E2%80%9D%2C%20%5B%40%5BAccount%20Values%5D%5D*0.0025%2C0)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThe%20extra%20inner%20set%20of%20square%20brackets%20is%20only%20needed%20when%20the%20column%20name%20includes%20special%26nbsp%3B%3C%2FSPAN%3Echaracters%20(like%20a%20space).%20That's%20why%20the%20reference%20to%20Account%20Values%20looks%20different%20than%20the%20one%20for%20Specialty.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%26nbsp%3B%40%20sign%20indicates%20that%20you%20are%20looking%20at%20a%20value%20on%20the%20same%20row%20as%20where%20the%20formula%20is%20entered.%20If%20you%20address%20an%20entire%20column%20in%20a%20formula%20within%20a%20structured%20table%20you%20get%20the%20SPILL%20error%20as%20it%20is%20not%20allowed.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello,

I have to complete an assignment using Excel for my online class and the class wants me to use a very specific formula inorder to fill the table.

The textbook wants me to use this formula:

=IF([@[Specialty]=“Loans”, [@[Account Values]]*.0025,0)

the assignment says to enter the [then double click the column from the drop down list to select the appropriate column for the formula. When I attempt to do this it doesn't put the column title Specialty or Account values into the formula its just inserts Column 3 and Column 4 respectively. When I hit enter it gives me a syntax error. When I directly copy paste the formula into the excel from my textbook it gives me a syntax error. I tried playing with the selection and instead of selecting the column I selected the cells and no matter what I do I only get Syntax errors, #Name?, and #Spill! errors. I can't figure out how to fix this. I tried looking online at other support forums and the people there were just saying to make a whole new workbook/table which is not an option as this excel file is provided by the textbook and I can't just make my own. 

Please help.

1 Reply

@RRouse34 I wouldn't be able to follow that instruction either as it is wrong. You are obviously learning about how to use structured table reference. What you do is the following:

 

First make sure that you have a structured table with a column called Specialty and another called Account Values.

In the cell (usually on the first row below the headers) in another (empty) column in the table, start typing =IF( and point at the cell on the same row in the Specialty column. That will automatically insert [@Specialty].

(Note that there is one square bracket too many in the textbook formula). Continue typing the formula and when you come to entering the reference for the account value, just click on the correct cell and [@[Account Values]] will be added. Finish typing the formula and press enter. 

 

You should end up with this, down the entire column:

=IF([@Specialty]=“Loans”, [@[Account Values]]*0.0025,0)

 

The extra inner set of square brackets is only needed when the column name includes special characters (like a space). That's why the reference to Account Values looks different than the one for Specialty.

 

The @ sign indicates that you are looking at a value on the same row as where the formula is entered. If you address an entire column in a formula within a structured table you get the SPILL error as it is not allowed.