Complicated formula help...

[grrrrr]

This is a re-post as I typed it quickly and it was tough to understand (even by me!), so apologies.

I need help creating a formula to account for all of the following:

Cell details:

Profit of the deal A1
Sales target B1
Profit target C1

Total sales B2
Total profit C2

Commission payable D1

Ratio E1

Formula requirements:

Stage 1
If B2 and C2 are less than B1 and C1 then 8% of A1 or (whichever is highest) £30 commission will be paid.

Stage 2
If B2 or C2 are higher than B1 or C1 then 10% of A1 or (whichever is highest) £40 commission will be paid.

Stage 3
If B2 and C2 are higher than B1 and C1 then 12.5% of A1 or (whichever is highest) £50 commission will be paid.

Stage 4
If stage 3 is met then another cell showing whether a ratio has been met will have to be linked (say E1). If this is also met then 15% of A1 or (whichever is highest) £60 commission will be paid.


Any help would be hugely appreciated

 

[Ralajer]

I increased the row numbers by one so I could use column headers. Try this.

=IF(AND(B2<B3,C2<C3),MAX(30,0.08*A2),IF((--(B2>=B3)+--(C2>=C3))=1,MAX(40,0.1*A2),IF(AND(B2>=B3,C2>=C3),IF(E2>=1,MAX(60,0.15*A2),MAX(50,0.125*A2)),-1)))

It seems to be getting the expected values.

 

[Joseph.Marro]

Grrrrr,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Based on your requirements the following formula should work. However, you did not explain how the ratio was calculated so you will have to enter the desired ratio value where "some number" appears in the formula. I avoided using IF statements so the formula could be updated with additional criteria without needing to change the structure.
<o></o>
<o></o>
<?XML:NAMESPACE PREFIX = B1,C2<B2)),MAX(A1*0.08,30),MAX(A1*0.1,40),MAX(A1*0.125,50),MAX(A1*0.15,60))<o /><B1,C2<B2)),MAX(A1*0.08,30),MAX(A1*0.1,40),MAX(A1*0.125,50),MAX(A1*0.15,60))<o>
=CHOOSE(MAX(--AND(C1>=B1,C2>=B2,E1>="Some Number")*4,--AND(C1>=B1,C2>=B2)*3,--OR(C1>B1,C2>B2)*2,--AND(C1<B1,C2<B2)),MAX(A1*0.08,30),MAX(A1*0.1,40),MAX(A1*0.125,50),MAX(A1*0.15,60))

Thank you,<o></o>
<o></o>
Joseph Marro<o></o>
<o></o>
</B1,C2<B2)),MAX(A1*0.08,30),MAX(A1*0.1,40),MAX(A1*0.125,50),MAX(A1*0.15,60))<o>

 

[Joseph.Marro]

grrrrr,

For some reason the formula wont post correctly.

Thank you,

Joseph Marro

 

[Ralajer]

If you choose to go with my solution I assumed if the ratio in E2 (or E1) was greater than 1 than apply stage four.

Joseph thanks for reminding me about the CHOOSE function. Try wrapping the formula in CODE tags.

 

[Joseph.Marro]

grrrrr,

one last try... ill PM you if it doesn't work.

Sent PM... Site is broken. Code Tags are stripping the text

Joseph Marro

 

[grrrrr]

Thank you so much for the replies - I'll have a look shortly and see how we go!

 

[grrrrr]

Thanks again Ralajer and Joseph - I never would have been able to figure that out by myself although I think i'm starting to get to grips with it all now (a bit at least!)

 

[barry houdini]

For some reason the formula wont post correctly.

Hello Joseph,

sometimes < and > signs in formulas are interpreted as HTML tags and the formula is corrupted. Try putting a space either side of and > or < signs

 

[Joseph.Marro]

Barry,

Thanks for the info. I was a bit confused why it works in PM's but not on the board.

Thank you,

Joseph Marro