Complex Excel Forumula

double_dutchy

DullGreyGuy said:

Layer	Rate	Commission	Overrider	Profit share
1	60%	30%	5%	10%
2	20%	30%	15%	0%
3	50%	20%	0%	10%

What would be the formula to work out the Gross to Supplier for any given sale and layer? Have been trying to use a nested Product/If statement testing the Sales ID is the same and the Layer number is less than the current layer but seem to have to many cycles to go through and my head hurts.

I'm not sure I understand the whole requirement but just on that last part it's often possible to use SUMPRODUCT function as a "pseudo" productifs function, e.g.

=SUMPRODUCT((A2:A10="x")*(B2:B10<10),C2:C10, D2:D10, E2:E10)

If only one row satisfies the criteria for columns A and B then you'll get the product of the other 3 columns for the matching row, otherwise if there are multiple matching rows you'll get sum of each of the products.

Sounds like your requirement is a wee bit more complex than that but you may be able to adapt to your needs

Heedtheadvice

Maybe sub prime but at least a solution for you.