Try this:

Up to 500 items @ 0.25 increase per 100:

=SUMPRODUCT((A1>{0,100,200,300,400,500})*(A1-{0,100,200,300,400,500})*{2,0.2

5,0.25,0.25,0.25,0.25})

--

Regards,

RD

----------------------------------------------------------------------------

-------------------

Please keep all correspondence within the Group, so all may benefit !

----------------------------------------------------------------------------

-------------------

If you mean: an additional $0.25 for each 100 over 100,

I think you need a VBA macro with a loop.

I don't think it is what the OP wants, (or if it is he has a very

generous

employer), but just for the fun of it:

=MIN(A1,100)*2+(A1>100)*((INT(A1/100)*((A1>100)*0.125)+2)*ROUND(A1-100,-2)+M

OD(A1,100)*(INT(A1/100)*0.25+2))

increases by 0.25 for each 100 over 100.

or with explanations included:

=N("Calculate 1st 100")+MIN(A1,100)*2+N("Calculate all other

100's")+(A1>100)*((INT(A1/100)*((A1>100)*0.125)+2)*ROUND(A1-100,-2)+N("Calcu

late

remaining 10's & units items")+MOD(A1,100)*(INT(A1/100)*0.25+2))

--

Regards

Sandy

(e-mail address removed)

Replace@mailinator with @tiscali.co.uk

:

Not clear what you want. Sales of 101 units:

Either 100@ $2.00 +1 @ $2.25 =$202.25

Or 101 @ $2.25 = $227.25

Dan Lieberman wrote:

First option

[....]

100 units @ $2.00

101- 200 units @ $2.25

201 - 300 units @ $2.50

So what you mean to say is: $2.00 for the first 100,

$2.25 for the second 100, and $2.50 for the third 100.

What about the fourth 100, etc?

If you mean: $2.50 for any number over 200, you could

use the following formula:

2*min(a1,100) + 2.25*max(0,min(a1-100,100)) + 2.50*max(0,a1-200)

If you mean: an additional $0.25 for each 100 over 100,

I think you need a VBA macro with a loop.

Alternatively, if there is a reasonable limit (e.g,

"no one could sell more than 500"), you could extend

the formula above. For example:

2*min(a1,100) + 2.25*max(0,min(a1-100,100))

+ 2.50*max(0,min(a1-200,100)) + 2.75*max(0,min(a1-300,100))

+ 3.00*max(0,a1-400)

Note: This pays $3.00 for any number over 400.