Help with multiple IF and OR statements

**Rooster35236** · 11-22-2010, 04:22 PM

I'm trying to put together a formula for all of my ebay final value fees (which are entirely too complicated,) I think I have the basics down but the math does not come out right. Somebody please help.

=IF(J13="YES",(IF(G13="Electronics",IF(L13<=50,L13*0.08,(OR(L13>=50.01,L13<=1000)*L13-50*0.05+(0.08*50))),IF(G13="Clothing",IF(L13<=50,L13*0.12,(OR(L13>=50.01,L13<=1000)*L13-50*0.09+(0.12*50))),IF(G13="Books",IF(L13<=50,L13*0.15,(OR(L13>=50.01,L13<=1000)*L13-50*0.05+(0.15*50))),IF(G13="Other",IF(L13<=50,L13*0.12,(OR(L13>=50.01,L13<=1000)*L13-50*0.06+(0.12*50)))))))),"NO")

G13 = is the category for which the item belongs and then has a corresponding formula depending on the price sold

J13 = asks me if the item was buy it now or not if yes then it just has to use the formulas that are listed

L13 = price sold

**Blake 7** · 11-22-2010, 05:14 PM

Roost - can you post a sample of your workbook? it would make it easier for people to follow. Also, describe your desired outcome. It maybe that your if formula is inefficient and not the right function.

**NBVC** · 11-22-2010, 05:19 PM

You would have to check the math, but perhaps?

=IF(J13="YES",IF(L13<50,L13*LOOKUP(L13,{"Books","Clothing","Electronics","Other"},{0.15,0.12,0.08,0.12}),IF(L13<=1000,L13-50*LOOKUP(L13,{"Books","Clothing","Electronics","Other"},{0.05,0.09,0.05,0.06})+50*LOOKUP(L13,{"Books","Clothing","Electronics","Other"},{0.15,0.12,0.08,0.12}),"What_if_over_1000")),"No")

replace this: "What_if_over_1000" with what you want to happen if L13 is over 1000....

Be careful of order operations in these statements: L13-50*0.05+(0.08*50)

the multiplications happen first, then the result of 50*0.05 is subtracted from L13 and the result of all that is added to 0.08*50

**Rooster35236** · 11-22-2010, 05:35 PM

Wow that's good I've been scratching my head over this one for the past 2 days thanks so much!

**noexcel=badwork** · 11-22-2010, 06:38 PM

you may use this formula also:

=IF(J13="yes",IF(AND(G13="electronics",L13<=50),L13*0.08,IF(AND(G13="electronics",L13>=50.01,L13<=1000),L13-50*0.05+(0.08*50),IF(AND(G13="clothing",L13<=50),L13*0.12,IF(AND(G13="clothing",L13>=50.01,L13<=1000),L13-50*0.09+(0.12*50),IF(AND(G13="books",L13<=50),L13*0.15,IF(AND(G13="books",L13>=50.01,L13<=1000),L13-50*0.05+(0.15*50),IF(AND(G13="other",L13<=50),L13*0.12,IF(AND(G13="other",L13>=50.01,L13<=1000),L13-50*0.06+(0.12*50))))))))),"no")