In that case, I would use the table technique that I outline at the site
I cited, but make it two dimensional, e.g.,:

A B C D E
1 1 2 3 4 ...
2 0 x1% x2% x3% x4%
3 100 y1% y2% y3% y4%
4 1000 z1% z2% z3% z4%

where row 1 is the number of units sold in each bracket, and y and z
are incremental percentages, as described.

Then the calculation would be, assuming J1 is the dollar value of the
item and J2 is the number sold:

=SUMPRODUCT(--(J1$A$2:$A$4),(J1-$A$2:$A$4),
OFFSET($B$2,0,MATCH(J2,$B$1:$E$1,TRUE)-1, 3, 1))

ensuring that the number of rows in OFFSET() equals the number of rows
in the lookup range

In article ,
corrado444
wrote:

Thank you for your replay.
The formula you pointed me to will work for my initial set up, and I
swear I would have never found it by myself.

The formula I am looking for is one where the commission is
incrementally adjusted according to the sale price and quantity sold.
Conceptually is very similar to this one, but it doesn't relay on
blocks of values, but each value affects the commission incrementally.

In essence, if I sell 7 items under $100 I should get a x commission,
if I sell 8 the commission percentage should go up in increments for
each product sold under $100. Maybe the formula you pointed me to does
that, I'll plug in some real numbers to try, but it looks like it still
needs me to classify sales with a price range and a quantity range to
arrive at a a given percentage.

The goal would be to have each single result affect the commission
without having any ranges at all, either in price nor quantity.

Maybe there is another way to arrive at this, and my logic is faulty.

I am selling expensive restaurant supply on eBay for a dealer. Most of
them will be in the $1000 to $15000 range. In addition I'll be selling
less expensive items to attract more clientele.

The amount of work to list an item is the same whether I sell 1 or a
100 items and whether they cost $1 or $1,000, the only extra work will
be in dealing with closing the sale and the shipping, but the listing
work represents about 70% of the total effort.

Ideally, I would like my commission to go down as sales increase in a
smooth way, without abrupt jumps, say form $199 to $200. In the latter
example, if I sold 10 items at $199, my commission would be X, if I
sold 10 $201 items I would actually make considerably less in
commission if my threshold was $199.

By the same token, if my commission for under $199 is x up to 9 items
sold, and x*0.7 for 10 or more, I would be better off not selling that
last 10th item because I would make less total commission.

The reverse is true for my client.

I hope I am doing a decent job explaining this. It's really confusing.

Thank you again for your help.