Stuck with a Formula

[eschacha]

Hi All

I'm pulling my hair out trying to figure out a formula.

I have a series of Codes in Column A : (IB, IS, IG, IW, IT, IBRO, ISRO & IGRO) which refer to products.

In Column L, I want to calculate a formula which changes based on the above Codes as follows;

IB, IS, IG, IW & IT all use the following formula
=T9-M9-N9

Whilst IBRO, ISRO & IGRO all need to use the following formula
=(J9-V9)*62.5%

When I first was unable to work out how to make my overall formula work I made a new workaround column, W ; which calculated part of the formula allowing me to simplify what I had to
=W9*62.5%

However I still can't get this to work.

Here's my working formula….

=IF((A9="IB, IS, IG, IW, IT"),T9-M9-N9,IF((A9="IBRO, ISRO, IGRO")W9*62.5%))

My question is

A - Can I combine my codes into single IF statements like above or should I have a separate IF statement for each one
B - I'm assuming the reason my formula doesn't work is because of the two sub formula's - Is what I'm trying to do even possible or do I need to cross reference with more columns (I already have several extra columns as work arounds).

As you can probably tell I'm not an advanced excel user (very much self taught) - I hope you don't mind me posting this in this forum. I've attached a blank template of what I'm trying to do if that helps clarify the problem.

Many thanks

Ewan

[amotto11]

Your if statement needs an or to work like this. so it would be

Please Login or Register  to view this content.

[AlKey]

Try this one

=IF(OR(A9={"IB","IS","IG","IW","IT"}),T9-M9-N9,IF(OR(A9={"IBRO","ISRO","IGRO"}),W9*62.5%,""))

[eschacha]

Thanks Amotto

Very much appreciate your help.

That sort of makes sense to a layman.

I can understand breaking the codes into separate comma's (I just thought I might be able to combine them - obviously not).

However i'm getting warnings using your suggestion. Excel isn't liking it and is suggesting IF(Logical_test[value_if_true],[value_if_false]) which looks like what we have to me.

Regards

Ewan

[amotto11]

Missed one comma, the last one. Also it is best practice to end your final if statement.

Please Login or Register  to view this content.

[eschacha]

Thanks to Amotto11 and AIKey - both your solutions work a treat.

Can't begin to describe how much time I've spent on this.

Very much appreciated.

Ewan