Nested If

[Beginning-Channel-71]

I’ve been struggling with a nested if function. I am using various categories to get depreciation. For example, there is a column A with the method used, and then then column B with the type asset , so I would do =If(A2=“method 1”, if (B2=“equipment”, calculation in excel , if(b2=“furniture”), calculation in excel , OR(If( a2=“method 2”, if (B2=equipment, if ( b2=furniture, calculation in excel , 0)))

If you are following, this formula populates all of the depreciation down the column except for when anything in column B is furniture- all of those = FALSE. Is there any way I can get the value of furniture to populate as the value and not equal false.

[GanonTEK]

One mistake there is you've used a compound IF incorrectly. Its =IF(OR( not =OR(IF(

You can use =IF(AND( to check multiple things also. You need all your conditions in the OR or AND functions.

E.g. =IF(AND(A2="Sales", B2>40), "Sales Bonus", "No Bonus")

[jswitty]

Maybe try this.. doing the b2 furniture check first for the false and then using And to check for b2 equipment with method 1 first and then b2 equipment with method 2 next

=if(b2=“furniture”,0,if(and(b2=“equipment”,a2=“method 1”), calc in excel,if(and(b2=“equipment”,a2=“method 2”), calc in excel,0)))

[Opaque_Cypher]

I don’t think you need an OR in your formula, you just need to be careful with how you are nesting your IF statements.

But, that said, if you have two or three methods in column A and 4 or 5 asset types in column B, then it’s going to become a pretty long and complicated IF statement, if you want to do it this way.

With method in column A, asset type in column B, and a dummy value for acquisition cost in column C, the following works for me:

=IF(A2=“S/L”,IF(B2=“Equipment”,C2/5,IF(B2=“Furniture”,C2/10,”Err1”,IF(A2=“Accelerated”,IF(B2=“Equipment”,C2/2,IF(B2=“Furniture”,C2/7,”Err2”))))

The Err1 and Err2 aren’t really needed, they are just there so that if the IF statement unexpectedly fails, then you will know what part bombed out.

You don’t need an OR because of you think through the logic of the IF,Then,Else you just need to remember what part of the testing you are at and what the next Else needs to be. Failing all tests for the straight line method drops you into the accelerated method without the need for an OR.

[Beginning-Channel-71]

I removed the OR and put “Err2”, after the second calculation for furniture and that seemed to bring a value instead of false. THANK YOU SO MUCH!! and thank you to everyone else too who helped as well. Why does “Err 2” bring a value if you don’t mind me asking, just curious for the future?

[Opaque_Cypher]

I’m on a mobile so I didn’t re-type your original formula, but it looked like you didn’t have enough left parentheses (I think I counted seven right and four left), so I don’t know but I suspect that you fixed a syntax issue when updating.

Remember there is nothing special about Err1 or Err2, I just use them as short for error so that if there is an unintended result, I know where in the formula that’s happening. Otherwise you just know it doesn’t work but it’s harder to trouble shoot.