Whats wrong with my code

Hey - completely novice excel user trying to get my head round a line of algorithm...

Im trying to tier an IF statement... basically over 100% you get 10% of a value, between 110-120% you get 20% of a value, and 120% you get 30% of a value.

the 666 is a standard commission payment made after getting over 100% which is why its in every part.

My code is like this...

IF(F2<100, 0, IF(F2>=100 AND <110, (((E2-D2)*0.1)+666)), IF(F2>=110 AND <120, (((E2-D2)*0.2)+666)), (((E2-D2)*0.3)+666))

Excell telling me its wrong - but I cant see why!!

help please?

I think its something to do with the brackets, But im sure I bracketed it all together...

this isnt working either.

=IF(F2<100, 0, IF(F2>=100 AND <110, (((E2-D2)*0.1)+666), IF(F2>=110 AND <120, (((E2-D2)*0.2)+666), (((E2-D2)*0.3)+666))))

thanks martin -

I dont understand how I would write that?

Originally Posted by OP

what's wrong with my code

>>it doesn't work.

glad I could help

I love easy questions

...

More seriously, though, you're using AND wrong.

Please Login or Register  to view this content.

should be

Please Login or Register  to view this content.

Anyway, aside from that,

I'm not sure you've set up your value handing correctly, which is a problem with the way you've designed it, not with the syntax.

ok - this?

=IF(F2<100, 0, IF(AND(F2>=100, <110), (((E2-D2)*0.1)+666)), IF(AND(F2>=110, <120),(((E2-D2)*0.2)+666),(((E2-D2)*0.3)+666)))

=IF(F2<100, 0, IF(AND(F2>=100, f2<110), (((E2-D2)*0.1)+666)), IF(AND(F2>=110, f2<120), (((E2-D2)*0.2)+666)), (((E2-D2)*0.3)+666))

I think this might be legit - but excel is telling me I have too many arguments...?

god im useless at this.

One too many right paren after both the first "+666" bits; it was terminating the relevant IF loop early so the first IF loop was thinking you were giving it like five terms, like so: IF(1,2,3,4,5) which made excel freak out.

Try this:

Please Login or Register  to view this content.

But yeah martin's formulations are more elegant and easier to read.