New wonderfully complicated formula needed... :)

[grrrrr]

Hi guys.

I previously posted in here a couple of months ago and got a great response. This is along the same lines but a bit different and more complicated and any help would be fantastic!

This is the current formula in use:

=IF(ISNUMBER(H9*P$71),MAX(H9*P$71,VLOOKUP(--P$71,{0.08,30;0.1,30;0.125,40;0.15,50},2,0)),0)

This basically pays a minimum commission (based on the % sold of the target) unless the calculation with the figure in column H comes out to be higher, in which case that would be used instead.


On the new structure they are paid a minimum of 8% (0.08) or £30 for not meeting any criteria (as the original structure), but if they reach the target they move up to 10% (0.10) or £40 minimum.

Where it gets different (and complicated!):

They can also achieve the same as above via a different means (different target) which would need to link in. It doesn't matter by which method out of the two they meet in order to be paid at 10% or £40 minimum.

The third stage is 12.5% (0.125) or £50 minimum for meeting both of the above.

The fourth and final stage(!) is if they meet all three of the above and achieve a ratio of at least 1.3:1 on certain types of sales then it goes to 15% or £60 minimum.

I don't know if anybody will understand that - if not please say and I'll try to make it more clear.

Thanks again!!

 

[grrrrr]

Please say if that is too rambling/complicated to understand!

 

[grrrrr]

I'll try and explain a bit better:

There is a cell showing the profit of the deal, another showing the sales target and a final cell showing the profit target (call these A1, B1 and C1 respectively).

The total sales and total profit are B2 and C2.

The Commission payable can vary - call this cell D1.

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 all of the above are 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.


Phew!!

Thanks

 

[GlennUK]

Explain what you mean by this:

Stage 4
If all of the above are met

... all of the above cannot be met at the same time. Either of 1 or 2 or 3 can be met, but not more than one.

 

[grrrrr]

Sorry, that didn't quite come out right as you say.

It just meant if stage 3 is met (so equal/above target on sales and profit) as well as the ratio required.

 

[GlennUK]

Does this do what you want:

Excel Workbook
	A	B	C	D	E
1	434	24	36	43.4	TRUE
2		16	44		( cell above for "whether a ratio has been met ", as per user requirements, although no formula or logic actually given for this )
Sheet22